設定 max degree of parallelism 伺服器組態選項Configure the max degree of parallelism Server Configuration Option

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主題描述如何使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL,在 SQL Server 2017SQL Server 2017 中設定 [平行處理原則的最大程度 (MAXDOP)] 伺服器組態選項。This topic describes how to configure the max degree of parallelism (MAXDOP) server configuration option in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. SQL ServerSQL Server 執行個體在具有多個微處理器或 CPU 的電腦上執行時,會偵測平行處理原則的程度,也就是說,針對每一個平行計畫的執行,執行單一陳述式所要採用的處理器個數。When an instance of SQL ServerSQL Server runs on a computer that has more than one microprocessor or CPU, it detects the degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. 您可以使用 max degree of parallelism 選項來限制要用於平行計畫執行的處理器數目。You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. SQL ServerSQL Server 會針對查詢、索引資料定義語言 (DDL) 作業、平行插入、線上改變資料行、平行收集統計資料,以及靜態和索引鍵集驅動資料指標擴展,考慮平行執行計畫。considers parallel execution plans for queries, index data definition language (DDL) operations, parallel inserts, online alter column, parallel stats collection, and static and keyset-driven cursor population.

開始之前Before You Begin

限制事項Limitations and Restrictions

  • 如果 affinity mask 選項不是設成預設值,它可能會限制對稱式多處理 (SMP) 系統上 SQL ServerSQL Server 可用的處理器個數。If the affinity mask option is not set to the default, it may restrict the number of processors available to SQL ServerSQL Server on symmetric multiprocessing (SMP) systems.

  • 平行處理原則最大程度 (MAXDOP) 限制的設定會根據工作The max degree of parallelism (MAXDOP) limit is set per task. 它不是根據要求或查詢限制。It is not a per request or per query limit. 這表示平行查詢執行期間,單一要求可能會產生指派至排程器的多個工作。This means that during a parallel query execution, a single request can spawn multiple tasks which are assigned to a scheduler. 如需詳細資訊,請參閱執行緒與工作架構指南For more information, see the Thread and Task Architecture Guide.

建議Recommendations

  • 此選項是進階選項,只有具經驗的資料庫管理員或通過認證的 SQL ServerSQL Server 專業人員才可變更。This option is an advanced option and should be changed only by an experienced database administrator or certified SQL ServerSQL Server professional.

  • 若要讓伺服器判斷平行處理原則的最大程度,請將此選項設定為 0 (預設值)。To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. 將平行處理原則的最大程度設定為 0 就會允許 SQL ServerSQL Server 使用所有可用的處理器 (最多 64 個處理器)。Setting maximum degree of parallelism to 0 allows SQL ServerSQL Server to use all the available processors up to 64 processors. 若要抑制平行計畫的產生,請將 max degree of parallelism 設成 1。To suppress parallel plan generation, set max degree of parallelism to 1. 將此值設成 1 到 32,767 的數字會指定單一查詢執行可用的最大處理器核心數目。Set the value to a number from 1 to 32,767 to specify the maximum number of processor cores that can be used by a single query execution. 如果指定的數值大於可用的處理器數目,就會使用可用處理器的實際數目。If a value greater than the number of available processors is specified, the actual number of available processors is used. 如果電腦只有一個處理器,則會忽略 max degree of parallelism 值。If the computer has only one processor, the max degree of parallelism value is ignored.

  • 您可以在查詢陳述適中指定 MAXDOP 查詢提示,來覆寫查詢中的 max degree of parallelism 值。You can override the max degree of parallelism value in queries by specifying the MAXDOP query hint in the query statement. 如需詳細資訊,請參閱查詢提示 (Transact-SQL)For more information, see Query Hints (Transact-SQL).

  • 建立或重建索引的索引作業,或者卸除叢集索引的索引作業,都需要大量資源。Index operations that create or rebuild an index, or that drop a clustered index, can be resource intensive. 您可以在索引陳述式中指定 MAXDOP 索引選項,覆寫索引作業中的 max degree of parallelism 值。You can override the max degree of parallelism value for index operations by specifying the MAXDOP index option in the index statement. MAXDOP 值會在執行時套用至陳述式,且不會儲存在索引中繼資料內。The MAXDOP value is applied to the statement at execution time and is not stored in the index metadata. 如需詳細資訊,請參閱 設定平行索引作業For more information, see Configure Parallel Index Operations.

  • 除了查詢作業和索引作業外,此選項也會控制 DBCC CHECKTABLE、DBCC CHECKDB 和 DBCC CHECKFILEGROUP 的平行處理原則。In addition to queries and index operations, this option also controls the parallelism of DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP. 您可以使用追蹤旗標 2528 來停用這些陳述式的平行執行計畫。You can disable parallel execution plans for these statements by using trace flag 2528. 如需詳細資訊,請參閱追蹤旗標 (Transact-SQL)For more information, see Trace Flags (Transact-SQL).

提示

若要在查詢層級完成此操作,請使用 MAXDOP 查詢提示To accomplish this at the query level, use the MAXDOP query hint.
若要在資料庫層級完成此操作,請使用 MAXDOP 資料庫範圍設定To accomplish this at the database level, use the MAXDOP database scoped configuration.
若要在工作負載層級完成此操作,請使用 MAX_DOP Resource Governor 作負載群組設定選項To accomplish this at the workload level, use the MAX_DOP Resource Governor workload group configuration option.

指導方針Guidelines

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,若 Database EngineDatabase Engine 在服務啟動期間偵測到啟動時每個 NUMA 節點或通訊端有超過八個實體核心,則會根據預設自動建立軟體式 NUMA 節點。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), during service startup if the Database EngineDatabase Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. Database EngineDatabase Engine 會將來自相同實體核心的邏輯處理器放入不同軟體式 NUMA 節點。The Database EngineDatabase Engine places logical processors from the same physical core into different soft-NUMA nodes. 下表中建議事項目標是使所有平行查詢的背景工作執行緒保持在相同軟體式 NUMA 節點內。The recommendations in the table below are aimed at keeping all the worker threads of a parallel query within the same soft-NUMA node. 這會改善查詢效能及工作負載 NUMA 節點中的背景工作執行緒分佈。This will improve the performance of the queries and distribution of worker threads across the NUMA nodes for the workload. 如需詳細資訊,請參閱軟體式 NUMAFor more information, see Soft-NUMA.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,請在您設定最大平行處理程度伺服器設定值時,使用下列方針:Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), use the following guidelines when you configure the max degree of parallelism server configuration value:

具有單一 NUMA 節點的伺服器Server with single NUMA node 小於或等於 8 個邏輯處理器Less than or equal to 8 logical processors MAXDOP 保持在或低於 # 個邏輯處理器數目Keep MAXDOP at or below # of logical processors
具有單一 NUMA 節點的伺服器Server with single NUMA node 多於 8 個邏輯處理器Greater than 8 logical processors MAXDOP 保持在 8Keep MAXDOP at 8
具有多個 NUMA 節點的伺服器Server with multiple NUMA nodes 每個 NUMA 節點小於或等於 16 個邏輯處理器Less than or equal to 16 logical processors per NUMA node 每個 NUMA 節點的 MAXDOP 保持在或低於 # 個邏輯處理器數目Keep MAXDOP at or below # of logical processors per NUMA node
具有多個 NUMA 節點的伺服器Server with multiple NUMA nodes 每個 NUMA 節點多於 16 個邏輯處理器Greater than 16 logical processors per NUMA node 將 MAXDOP 保持在最大 (MAX) 值為每個 NUMA 節點 16 個邏輯伺服器數量的一半Keep MAXDOP at half the number of logical processors per NUMA node with a MAX value of 16

注意

上表中的 NUMA 節點指的是由 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本自動建立的軟體式 NUMA 節點,若已停用軟體式 NUMA,則為硬體式 NUMA 節點。NUMA node in the above table refers to soft-NUMA nodes automatically created by SQL Server 2016 (13.x)SQL Server 2016 (13.x) and higher versions, or hardware-based NUMA nodes if soft-NUMA has been disabled.
請在您為 Resource Governor 工作負載群組設定平行處理最大程度的選項時,使用這些相同的方針。Use these same guidelines when you set the max degree of parallelism option for Resource Governor workload groups. 如需詳細資訊,請參閱 CREATE WORKLOAD GROUP (Transact-SQL)For more information, see CREATE WORKLOAD GROUP (Transact-SQL).

SQL Server 2008SQL Server 2008SQL Server 2014 (12.x)SQL Server 2014 (12.x),請在您設定最大平行處理程度伺服器設定值時,使用下列方針:From SQL Server 2008SQL Server 2008 through SQL Server 2014 (12.x)SQL Server 2014 (12.x), use the following guidelines when you configure the max degree of parallelism server configuration value:

具有單一 NUMA 節點的伺服器Server with single NUMA node 小於或等於 8 個邏輯處理器Less than or equal to 8 logical processors MAXDOP 保持在或低於 # 個邏輯處理器數目Keep MAXDOP at or below # of logical processors
具有單一 NUMA 節點的伺服器Server with single NUMA node 多於 8 個邏輯處理器Greater than 8 logical processors MAXDOP 保持在 8Keep MAXDOP at 8
具有多個 NUMA 節點的伺服器Server with multiple NUMA nodes 每個 NUMA 節點小於或等於 8 個邏輯處理器Less than or equal to 8 logical processors per NUMA node 每個 NUMA 節點的 MAXDOP 保持在或低於 # 個邏輯處理器數目Keep MAXDOP at or below # of logical processors per NUMA node
具有多個 NUMA 節點的伺服器Server with multiple NUMA nodes 每個 NUMA 節點多於 8 個邏輯處理器Greater than 8 logical processors per NUMA node MAXDOP 保持在 8Keep MAXDOP at 8

安全性Security

權限Permissions

不含參數或只含第一個參數之 sp_configure 上的執行權限預設會授與所有使用者。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 以同時設定兩個參數的 sp_configure 來變更組態選項或執行 RECONFIGURE 陳述式時,使用者必須取得 ALTER SETTINGS 伺服器層級權限。To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. 系統管理員 (sysadmin)serveradmin 固定伺服器角色會隱含 ALTER SETTINGS 權限。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

設定 max degree of parallelism 選項To configure the max degree of parallelism option

  1. [物件總管] 中,以滑鼠右鍵按一下伺服器,然後選取 [屬性]In Object Explorer, right-click a server and select Properties.

  2. 按一下 [進階] 節點。Click the Advanced node.

  3. [平行處理原則的最大程度] 方塊中,選取用於執行平行計畫的最大處理器數目。In the Max Degree of Parallelism box, select the maximum number of processors to use in parallel plan execution.

使用 Transact-SQLUsing Transact-SQL

設定 max degree of parallelism 選項To configure the max degree of parallelism option

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 此範例示範如何使用 sp_configuremax degree of parallelism 選項設定為 8This example shows how to use sp_configure to configure the max degree of parallelism option to 8.

USE AdventureWorks2012 ;  
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)伺服器組態選項。For more information, see Server Configuration Options (SQL Server).

後續操作:在您設定完最大平行處理程度的選項後Follow Up: After you configure the max degree of parallelism option

設定會立即生效,不需要重新啟動伺服器。The setting takes effect immediately without restarting the server.

另請參閱See Also

ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
SQL Server 中 [平行處理原則最大程度] 設定選項的建議和指導方針 Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server
affinity mask 伺服器組態選項 affinity mask Server Configuration Option
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
伺服器組態選項 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
查詢處理架構指南 Query Processing Architecture Guide
執行緒和工作架構指南 Thread and Task Architecture Guide
設定平行索引作業 Configure Parallel Index Operations
查詢提示 (Transact-SQL) Query Hints (Transact-SQL)
設定索引選項Set Index Options