設定 max worker threads 伺服器組態選項

  • 發行項

本主題描述如何使用 SQL Server Management Studio 或 Transact-SQL，在 SQL Server 中設定最大背景工作執行緒伺服器組態選項。 [最大背景工作執行緒] 選項會設定可供SQL Server進程使用的背景工作執行緒數目。 SQL Server使用作業系統的原生執行緒服務，讓一或多個執行緒支援SQL Server同時支援的每個網路、另一個執行緒資料庫檢查點，以及執行緒集區會處理所有使用者。 max worker threads 的預設值是 0。 這會讓 SQL Server 在啟動時自動設定工作者執行緒的數目。 此預設值對大多數系統都是最佳的。 但依系統組態而定，將 max worker threads 設為特定的值有時候可提高效能。

本主題內容

開始之前

限制事項

  • 當實際的查詢要求數目小於 max worker threads的設定值時，就會由一個執行緒處理每一個查詢要求。 不過，如果實際的查詢要求數目超過最大背景工作執行緒中設定的數量，SQL Server集區背景工作執行緒，讓下一個可用的背景工作執行緒可以處理要求。

建議

  • 此選項是進階選項，只能由有經驗的資料庫管理員或經認證的SQL Server技術人員變更。

  • 當大量用戶端連接到伺服器時，執行緒集區有助於最佳化效能。 通常，會針對每一個查詢要求建立個別的作業系統執行緒。 然而，在數以百計的伺服器連接之下，若每個查詢要求都使用一個執行緒，反而會耗用大量的系統資源。 max worker threads 選項可讓 SQL Server 建立工作者執行緒集區，以服務更多的查詢要求數量，進而改善效能。

  • 下表顯示 CPU 和 SQL Server版本各種組合的自動設定最大背景工作執行緒數目。

    CPU 數 32 位元電腦 64 位元電腦
    <= 4 個處理器 256 512
    8 個處理器 288 576
    16 個處理器 352 704
    32 個處理器 480 960
    64 個處理器 736 1472
    128 個處理器 4224 4480
    256 個處理器 8320 8576

    注意

    如需有關使用超過 64 個 CPU 的建議事項，請參閱 在超過 64 個 CPU 之電腦上執行 SQL Server 的最佳作法。

    警告

    我們建議 1024 做為在 32 位電腦上執行之 SQL Server 實例的背景工作執行緒數目上限。

  • 當所有的工作者執行緒都在進行長時間執行的查詢時，SQL Server 可能會反應遲緩，直到工作者執行緒完成並恢復為可用狀態為止。 雖然這不算是瑕疵，但有時卻讓人困擾。 若處理序反應遲緩，而且無法處理新查詢，請使用專用管理員連接 (DAC) 來連接 SQL Server，然後清除處理序。 若要避免這個問題，請增加 max worker threads 的最大數目。

max worker threads 伺服器組態選項不會將所有系統工作 (例如可用性群組、Service Broker、鎖定管理員與其他工作) 需要的執行緒納入考量。 如果超過設定的執行緒數目，下列查詢會提供已產生其他執行緒之系統工作的相關資訊。

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  
INNERJOIN 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;

安全性

權限

不含參數或只含第一個參數之 sp_configure 上的執行權限預設會授與所有使用者。 以同時設定兩個參數的 sp_configure 來變更組態選項或執行 RECONFIGURE 陳述式時，使用者必須取得 ALTER SETTINGS 伺服器層級權限。 系統管理員 (sysadmin) 及 serveradmin 固定伺服器角色會隱含 ALTER SETTINGS 權限。

使用 SQL Server Management Studio

設定 max worker threads 選項

  1. 在物件總管中，請以滑鼠右鍵按一下伺服器，然後選取 [屬性]。

  2. 按一下 [處理器] 節點。

  3. 在 [ 最大背景工作執行緒 ] 方塊中，輸入或選取 128 到 32767 的值。

    Max worker threads 選項可用來設定 SQL Server 處理序可使用的工作者執行緒數目。 max worker threads 的預設值對大部份系統而言都是最合適的。 但依系統組態而定，將 max worker threads 設為較小的值有時候可提高效能。

使用 TRANSACT-SQL

設定 max worker threads 選項

  1. 連線至資料庫引擎。

  2. 在標準列中，按一下 [新增查詢] 。

  3. 複製下列範例並將其貼到查詢視窗中，然後按一下 [執行] 。 此範例示範如何使用 sp_configure 將 max worker threads 選項設定為 900。

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'max worker threads', 900 ;  
GO  
RECONFIGURE;  
GO

如需詳細資訊，請參閱伺服器設定選項 (SQL Server)。

待處理：設定 max worker threads 選項之後

變更會立即生效，而不需要重新開機 Database Engine。

另請參閱

RECONFIGURE (Transact-SQL)
伺服器組態選項 (SQL Server)
sp_configure (Transact-SQL)
資料庫管理員的診斷連接