共用方式為

設定 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 設為特定的值有時候可提高效能。

本主題內容

開始之前

限制事項

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

建議

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

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

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

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

    注意

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

    警告

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

  • 當所有的工作者執行緒都在進行長時間執行的查詢時，SQL Server 可能會反應遲緩，直到工作者執行緒完成並恢復為可用狀態為止。 雖然這不是缺陷，但有時可能不受歡迎。 若處理序反應遲緩，而且無法處理新查詢，請使用專用管理員連接 (DAC) 來連接 SQL Server，然後清除處理序。 若要避免這個問題，請增加 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

設定最大背景工作線程選項

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

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

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

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

使用 TRANSACT-SQL

設定最大背景工作線程選項

  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)。

待處理：設定最大背景工作線程選項之後

變更會立即生效，而不需要重新啟動 資料庫引擎。

另請參閱

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