【SQLServer】max worker threads參數說明
本文介紹如何使用SQL Server Management Studio或Transact-SQL在SQL Server中配置最大工作線程服務器配置選項。
max worker threads選項配置SQL Server可用於處理查詢請求、登錄、註銷和類似應用程序請求的工作線程數。
SQL Server使用操作系統的本地線程服務來確保以下條件:
·一個或多個線程同時支持SQL Server支持的每個網絡。
·一個線程處理數據庫檢查點。
·一個線程池處理所有用戶。
max worker threads的默認值為0。這使SQL Server能夠在啟動時自動配置工作線程數。默認設置適合大多數系統。但是,根據你的系統配置,將最大工作線程數設置為特定值有時會提高性能。
查詢請求的實際數量可能會超過max worker threads中設置的值,在這種情況下,SQL Server將工作線程池化,以便下一個可用的工作線程可以處理請求。工作線程僅分配給活動請求,並在請求得到服務後釋放。即使發出請求的用戶會話/連接保持打開,也會發生這種情況。
max worker threads配置選項不會限制引擎內可能產生的所有線程。LazyWriter、Checkpoint、Log Writer、Service Broker、Lock Manager或其他任務所需的系統線程在此限制之外生成。可用性組使用max worker threads限制內的一些工作線程,但如果超過配置的線程數,也使用系統線程,以下查詢將提供有關係統任務產生的額外線程信息。
SELECT s.session_id, r.command, r.status, r.wait_type, r.scheduler_id, w.worker_address, w.is_preemptive, w.state, t.task_state, t.session_id, t.exec_context_id, t.request_id FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id INNER JOIN sys.dm_os_tasks AS t ON r.task_address = t.task_address INNER JOIN sys.dm_os_workers AS w ON t.worker_address = w.worker_address WHERE s.is_user_process = 0;
以下是設置建議:
此選項是一個高級選項,只能由經驗豐富的數據庫管理員或經過認證的SQL Server專業人員進行更改。如果你懷疑存在性能問題,有可能不是工作線程的可用性原因。原因更可能與佔用工作線程而不釋放它們的活動有關。比如長時間運行的查詢或瓶頸(I/O、阻塞、閂鎖等待、網絡等待)。最好在更改最大工作線程設置之前找到性能問題的根本原因。
當大量客戶端連接到服務器時,線程池有助於優化性能。通常,會為每個查詢請求創建一個單獨的操作系統線程。但是,對於數百個與服務器的連接,每個查詢請求使用一個線程會消耗大量系統資源。max worker threads選項使SQL Server能夠創建一個工作線程池來服務更多的查詢請求,從而提高性能。
下表顯示了根據CPU、計算機體系結構和SQL Server版本的各種組合自動配置的最大工作線程數(當值設置為0時),使用公式:默認最大工作線程數+((邏輯CPU-4)*每個CPU的worker線程數)
Number of logical CPUs | 32-bit computer (up to SQL Server 2014 (12.x)) | 64-bit computer (up to SQL Server 2016 (13.x) SP1) | 64-bit computer (starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)) |
---|---|---|---|
<= 4 | 256 | 512 | 512 |
8 | 288 | 576 | 576 |
16 | 352 | 704 | 704 |
32 | 480 | 960 | 960 |
64 | 736 | 1472 | 1472 |
128 | 1248 | 2496 | 4480 |
256 | 2272 | 4544 | 8576 |
截至SQL Server 2016 (13.x) SP1,每個CPU的worker線程數取決於cpu的架構(32位還是64位):
Number of logical CPUs | 32-bit computer Note 1 | 64-bit computer |
---|---|---|
<= 4 | 256 | 512 |
> 4 | 256 + ((logical CPU’s – 4) * 8) | 512 Note 2 + ((logical CPU’s – 4) * 16) |
從SQL Server 2016 (13.x) SP2和SQL Server 2017 (14.x)起,每個CPU的worker線程數取決於cpu的架構(32位還是64位)和處理器的個數:
Number of logical CPUs | 32-bit computer Note 1 | 64-bit computer |
---|---|---|
<= 4 | 256 | 512 |
> 4 and <= 64 | 256 + ((logical CPU’s – 4) * 8) | 512 Note 2 + ((logical CPU’s – 4) * 16) |
> 64 | 256 + ((logical CPU’s – 4) * 32) | 512 Note 2 + ((logical CPU’s – 4) * 32) |
當所有工作線程都在長時間運行的查詢中處於活動狀態時,SQL Server可能會顯示為無響應,直到工作線程完成並變得可用。雖然這不是缺陷,但有時可能是不可取的。如果某個進程似乎沒有響應並且無法處理新查詢,則使用專用管理員連接(DAC)連接到SQL Server,並終止該進程。為防止這種情況,請增加最大工作線程數。