設定 max degree of parallelism (伺服器組態選項)

適用於:SQL Server

本文章描述如何使用 SQL Server Management Studio、Azure Data Studio 或 Transact-SQL,在 SQL Server 中設定平行處理原則 max degree of parallelism (MAXDOP) 伺服器組態選項。 當 SQL Server 的執行個體在具有多個微處理器或 CPU 的電腦上執行時,資料庫引擎會偵測是否可以使用平行處理原則。 平行處理原則程度會針對每個平行計劃執行,設定執行單一陳述式所運用的處理器數目。 您可以使用 max degree of parallelism 選項來限制執行平行計畫所用的處理器數目。 如需有關 max degree of parallelism (MAXDOP) 所設限制的詳細資料,請參閱本頁面中的考量一節。 SQL Server 會針對查詢、索引資料定義語言 (DDL) 作業、平行插入、線上改變資料行、平行統計資料收集,以及靜態和索引鍵集驅動資料指標填入,考量進行平行執行計劃。

注意

SQL Server 2019 (15.x) 推出根據安全程序期間可用的處理器數目,設定 MAXDOP 伺服器組態選項的自動建議。 安裝程式使用者介面可讓您接受建議的設定,或輸入您自己的值。 如需詳細資訊,請參閱資料庫引擎定 - MaxDOP 頁面

在 Azure SQL Database 和 Azure SQL 受控執行個體中,每個新的單一資料庫、彈性集區資料庫和受控執行個體的預設 MAXDOP 設定為 8。 在 Azure SQL Database 中,MAXDOP 資料庫範圍的組態會設定為 8。 在 Azure SQL 受控執行個體 中,max degree of parallelism (MAXDOP) 伺服器組態選項會設定為 8。

如需 Azure SQL Database 中 MAXDOP 的詳細資訊,請參閱在 Azure SQL Database 中設定平行處理原則的最大程度 (MAXDOP)

開始之前

考量

  • 此選項是進階選項,只有具經驗的資料庫管理員或通過認證的 SQL Server 專業人員才可變更。

  • 如果親和性遮罩選項不是設成預設值,它可能會限制對稱式多處理 (SMP) 系統上 SQL Server 可用的處理器個數。

  • 將平行處理原則的最大程度 (MAXDOP) 設定為 0 就會允許 SQL Server 使用所有可用處理器 (最多 64 個處理器)。 不過,在大多數情況下,並不建議使用此值。 如需平行處理原則最大程度的建議值詳細資訊,請參閱本頁面中的建議一節。

  • 若要隱藏平行計劃的產生,請將 max degree of parallelism 設定為 1。 將此值設成為 1 到 32,767 的數字,以指定執行單一查詢時可用的最大處理器核心數目。 如果指定的數值大於可用的處理器數目,就會使用可用處理器的實際數目。 如果電腦只有一個處理器,則會忽略 max degree of parallelism 值。

  • 平行處理原則最大程度的限制會根據工作而設定。 它不是根據要求或查詢限制。 這表示在平行查詢執行期間,單一要求可繁衍多個工作 (最多為 MAXDOP 限制),且每個工作都會使用一個背景工作和一個排程器。 如需詳細資訊,請參閱執行緒和工作架構指南中的<排程平行工作>一節。

  • 您可覆寫平行處理原則最大程度的伺服器組態值:

  • 建立或重建索引的索引作業,或者卸除叢集索引的索引作業,都需要大量資源。 您可以在索引陳述式中指定 MAXDOP 索引選項,覆寫索引作業中的 max degree of parallelism 值。 MAXDOP 值會在執行時套用至陳述式,且不會儲存在索引中繼資料內。 如需詳細資訊,請參閱 設定平行索引作業

  • 除了查詢作業和索引作業外,此選項也會控制 DBCC CHECKTABLE、DBCC CHECKDB 和 DBCC CHECKFILEGROUP 的平行處理原則。 您可以使用追蹤旗標 2528 來停用這些陳述式的平行執行計畫。 如需詳細資訊,請參閱追蹤旗標 (Transact-SQL)

  • SQL Server 2022 (16.x) 加入了「平行處理原則程度 (DOP) 回饋」這項新功能,可根據耗用時間及等候狀況來找出重複查詢平行處理原則的效率低落情形,進而改善查詢效能。 DOP 意見反應是智慧查詢處理系列功能的一部分,可解決重複查詢平行處理原則的欠佳使用狀況。 如需 DOP 意見反應的相關資訊,請參閱平行處理原則程度 (DOP) 意見反應 (機器翻譯)。

建議

從 SQL Server 2016 (13.x) 開始,若資料庫引擎在服務啟動期間偵測到啟動時每個 NUMA 節點或通訊端有超過八個實體核心,則會根據預設自動建立軟體式 NUMA 節點。 資料庫引擎會將來自相同實體核心的邏輯處理器放入不同軟體式 NUMA 節點。 下表中建議事項目標是使所有平行查詢的背景工作執行緒保持在相同軟體式 NUMA 節點內。 這會改善查詢效能及工作負載 NUMA 節點中的背景工作執行緒分佈。 如需詳細資訊,請參閱軟體式 NUMA

從 SQL Server 2016 (13.x) 開始,請在您設定 max degree of parallelism 伺服器組態值時,使用下列方針:

伺服器組態 處理器數目 指引
具有單一 NUMA 節點的伺服器 小於或等於八個邏輯處理器 MAXDOP 保持在或低於 # 個邏輯處理器數目
具有單一 NUMA 節點的伺服器 多於八個邏輯處理器 MAXDOP 保持在 8
具有多個 NUMA 節點的伺服器 每個 NUMA 節點小於或等於 16 個邏輯處理器 每個 NUMA 節點的 MAXDOP 保持在或低於 # 個邏輯處理器數目
具有多個 NUMA 節點的伺服器 每個 NUMA 節點多於 16 個邏輯處理器 將 MAXDOP 保持在最大 (MAX) 值為每個 NUMA 節點 16 個邏輯伺服器數量的一半

注意

上表中的 NUMA 節點指的是由 SQL Server 2016 (13.x) 及更高版本自動建立的軟體式 NUMA 節點,若已停用軟體式 NUMA,則為硬體式 NUMA 節點。 在您為 Resource Governor 工作負載群組設定平行處理最大程度的選項時,使用這些相同的方針。 如需詳細資訊,請參閱 CREATE WORKLOAD GROUP (Transact-SQL)

從 SQL Server 2008 (10.0.x) 到 SQL Server 2014 (12.x),當您設定 max degree of parallelism 伺服器組態值時,請使用下列指導方針:

伺服器組態 處理器數目 指引
具有單一 NUMA 節點的伺服器 小於或等於八個邏輯處理器 MAXDOP 保持在或低於 # 個邏輯處理器數目
具有單一 NUMA 節點的伺服器 多於八個邏輯處理器 MAXDOP 保持在 8
具有多個 NUMA 節點的伺服器 每個 NUMA 節點小於或等於八個邏輯處理器 每個 NUMA 節點的 MAXDOP 保持在或低於 # 個邏輯處理器數目
具有多個 NUMA 節點的伺服器 每個 NUMA 節點多於八個邏輯處理器 MAXDOP 保持在 8

安全性

權限

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

使用 SQL Server Management Studio 或 Azure Data Studio

在 Azure Data Studio 中,安裝 Database Admin Tool Extensions for Windows 延伸模組,或使用下列 T-SQL 方法。

設定 max degree of parallelism 選項

這些選項會變更執行個體的 MAXDOP。

  1. 在 [物件總管] 中,以滑鼠右鍵按一下所需的執行個體名稱,然後選取 [屬性]。

  2. 選取 [進階] 節點。

  3. [平行處理原則的最大程度] 方塊中,選取用於執行平行計畫的最大處理器數目。

使用 Transact-SQL

使用 T-SQL 設定 max degree of parallelism 選項

  1. 使用 SQL Server Management Studio 或 Azure Data Studio 連線到資料庫引擎。

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

  3. 複製下列範例並將其貼到查詢視窗中,然後選取 [執行]。 此範例示範如何使用 sp_configuremax degree of parallelism 選項設定為 16

USE AdventureWorks2022;  
GO   
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  
EXEC sp_configure 'max degree of parallelism', 16;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  

如需詳細資訊,請參閱伺服器組態選項 (SQL Server)

待處理:設定 max degree of parallelism 選項之後

設定會立即生效,不需要重新啟動伺服器。