配置 max degree of parallelism 服务器配置选项Configure the max degree of parallelism Server Configuration Option

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主题说明如何使用 SQL Server 2017SQL Server 2017 中配置 SQL Server Management StudioSQL Server Management Studio max degree of parallelism (MAXDOP) Transact-SQLTransact-SQL服务器配置选项。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 best 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 insert, 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.

建议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. 若将 maximum degree of parallelism 设置为 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).

准则Guidelines

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,在服务启动期间,如果 数据库引擎Database Engine 在启动时检测到每个 NUMA 节点或插槽内的物理内核数目超过 8 个,在默认情况下就会自动创建 soft-NUMA 节点。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), during service startup if the 数据库引擎Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. 数据库引擎Database Engine 将相同物理内核中的逻辑处理器放入不同的 soft-NUMA 节点中。The 数据库引擎Database Engine places logical processors from the same physical core into different soft-NUMA nodes. 下表中的建议旨在将并行查询的所有工作线程保持在相同 soft-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. 有关详细信息,请参阅 Soft-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 保持为 8 个Keep MAXDOP at 8
具有多个 NUMA 节点的服务器Server with multiple NUMA nodes 每个 NUMA 节点拥有小于或等于 16 个逻辑处理器Less than or equal to 16 logical processors per NUMA node 将 MAXDOP 保持为小于或等于每个 NUMA 节点的逻辑处理器的数量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 保持为每个 NUMA 节点逻辑处理器数量的一半,最大值为 16Keep 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) 及更高版本自动创建的 soft-NUMA 节点,或基于硬件的 NUMA 节点(如果 soft-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 保持为 8 个Keep MAXDOP at 8
具有多个 NUMA 节点的服务器Server with multiple NUMA nodes 每个NUMA 节点拥有小于或等于 8 个逻辑处理器Less than or equal to 8 logical processors per NUMA node 将 MAXDOP 保持为小于或等于每个 NUMA 节点的逻辑处理器的数量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 保持为 8 个Keep MAXDOP at 8

SecuritySecurity

权限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. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。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 EngineConnect to the 数据库引擎Database 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

affinity mask 服务器配置选项 affinity mask Server Configuration Option
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
服务器配置选项 (SQL Server) sp_configure (Transact-SQL) Server Configuration Options (SQL Server) sp_configure (Transact-SQL)
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
DBCC CHECKTABLE (Transact-SQL) DBCC CHECKTABLE (Transact-SQL)
DBCC CHECKDB (Transact-SQL) DBCC CHECKDB (Transact-SQL)
DBCC CHECKFILEGROUP (Transact-SQL) DBCC CHECKFILEGROUP (Transact-SQL)
配置并行索引操作 Configure Parallel Index Operations
查询提示 (Transact-SQL) 设置索引选项Query Hints (Transact-SQL) Set Index Options
Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server(适用于 SQL Server 中的“max degree of parallelism”配置选项的建议和指南)Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server