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

  • 發行項

適用於：SQL Server

本文說明如何使用 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 會將背景工作執行緒納入集區中，以便下一個可用的背景工作執行緒能處理要求。 背景工作執行緒只會指派給作用中的要求，並會在要求獲得服務之後釋放。 即使提出要求的使用者工作階段或連線保持開啟，也會發生此情況。

  • 最大 背景工作線程 伺服器組態選項不會限制引擎內可能繁衍的所有線程。 LazyWriter、檢查點、記錄檔寫入器、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
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 建立工作者執行緒集區，以服務更多的查詢要求數量，進而改善效能。

  • 下表顯示根據 SQL Server 的不同邏輯 CPU、電腦架構和版本組合所自動設定的最大背景工作執行緒數目 (值設為 0 時)，使用的公式為：預設最大背景工作角色 + ((邏輯 CPU數目 - 4) * 每個 CPU 的背景工作角色)。

    邏輯 CPU 數目 32 位元電腦 (上至 SQL Server 2014 (12.x)) 64 位元電腦 (上至 SQL Server 2016 (13.x) SP1) 64 位元電腦 (從 SQL Server 2016 (13.x) SP2 和 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) (含 Service Pack 1)，每個 CPU 的背景工作角色僅取決於架構 (32 位元或 64 位元)：

    邏輯 CPU 數目 32 位元電腦 1 64 位元電腦
    <= 4 256 512
    > 4 256 + ((邏輯 CPU 數目 - 4) * 8) 512 2 + ((邏輯 CPU 數目 - 4) * 16)

    從 SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x) 開始，每個 CPU 的背景工作角色取決於架構和處理器數目 (介於 4 到 64，或大於 64)：

    邏輯 CPU 數目 32 位元電腦 1 64 位元電腦
    <= 4 256 512
    > 4 和 <= 64 256 + ((邏輯 CPU 數目 - 4) * 8) 512 2 + ((邏輯 CPU 數目 - 4) * 16)
    > 64 256 + ((邏輯 CPU 數目 - 4) * 32) 512 2 + ((邏輯 CPU 數目 - 4) * 32)

    1 從 SQL Server 2016 (13.x) 開始，SQL Server 便無法再安裝於 32 位元作業系統上。 列出 32 位元電腦值以協助客戶執行 SQL Server 2014 (12.x) 或更早版本。 建議在 32 位元電腦上執行的 SQL Server 執行個體的背景工作執行緒最大數目設為 1,024。

    2 從 SQL Server 2017 (14.x) 開始，針對記憶體小於 2 GB 的機器，預設最大背景工作角色值會除以 2。

    提示

    如需有關使用超過 64 個邏輯 CPU 的詳細資訊，請參閱在超過 64 個 CPU 的電腦上執行 SQL Server 的最佳做法。

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

權限

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

使用 SQL Server Management Studio (SSMS)

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

  2. 選取 [處理器] 節點。

  3. 在 [最大背景工作執行緒] 方塊中，鍵入或選取 128 到 65,535 之間的任一數值。

提示

Max worker threads 選項可用來設定 SQL Server 處理序可使用的工作者執行緒數目。 max worker threads 的預設值對大部份系統而言都是最合適的。
但依系統組態而定，將 max worker threads 設為較小的值有時候可提高效能。 如需詳細資訊，請參閱本文的建議一節。

使用 Transact-SQL

  1. 連線至資料庫引擎。

  2. 在標準列上，選取 [新增查詢]。

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

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXEC sp_configure 'max worker threads', 900;
GO

RECONFIGURE;
GO

無須重新啟動資料庫引擎，變更會在執行 RECONFIGURE 後立即生效。

相關內容