在SQL Server中设置最大并行度的不同方法

In this article, we will go through the different ways to set the Max Degree of Parallelism option. Before doing that, it is helpful to understand the concept of parallelism, how it works and what the MSXDOP is.

在本文中，我们将通过不同的方式来设置“最大并行度”选项。 在此之前，了解并行性的概念，并行化的原理以及MSXDOP是什么是有帮助的。

The main idea of the Parallelism is splitting the main task into smaller tasks, running simultaneously, with each task accomplishes part of the job, then assigning these tasks to a number of processors and finally combining the partial results into one final result. You can imagine the parallelism as running multiple serial plan tasks at the same time in a parallel plan, each task runs on a separate processing unit using its own server connection. In this way, a parallel will run much faster than the serial plan.

并行的主要思想是将主要任务分解为多个较小的任务，同时运行，每个任务完成一部分工作，然后将这些任务分配给多个处理器，最后将部分结果组合为一个最终结果。 您可以想象并行性是在并行计划中同时运行多个串行计划任务，每个任务使用其自己的服务器连接在单独的处理单元上运行。 这样，并行运行将比串行计划快得多。

There are few technical terms that we should understand in order to be familiar with the parallelism. The first term is the Scheduler, which is the physical or logical processor that is responsible for scheduling execution of the SQL Server threads, taking into consideration that only one thread can be in the running state at any given time. The second term is the Worker, which represents the thread that is bounded to a specific schedule to accomplish a certain job. The fourth term is the Task, which represents a piece of work scheduled by SQL Server and assigned to a specific SQL Server worker. The Execution Context is where each task run inside, with each task runs inside only one execution context. The Parallel Page Supplier is a SQL Server Storage Engine component that is responsible for distributing the rows to be processed within the involved workers upon the workers’ demands and requests. The last component is the Exchange, that is responsible for connecting the different execution contexts of the parallel plan together.

为了熟悉并行性，我们应该理解一些技术术语。 第一项是Scheduler ，它是负责调度SQL Server线程执行的物理或逻辑处理器，要考虑到在任何给定时间只能有一个线程处于运行状态。 第二个术语是Worker ，它表示绑定到特定计划以完成特定工作的线程。 第四个术语是Task ，它代表SQL Server计划并分配给特定SQL Server worker的工作。 执行上下文是每个任务在其中运行的位置，每个任务仅在一个执行上下文中运行。 并行页面供应商是一个SQL Server存储引擎组件，负责根据工作人员的需求和要求在相关工作人员中分配要处理的行。 最后一个组件是Exchange ，它负责将并行计划的不同执行上下文连接在一起。

Before executing a query, the SQL Server Database Engine checks if the parallel execution is required depending on many factors, such as, if the SQL Server instance is running on a server with more than one CPU, if the required number of threads is available and can be satisfied, the workload type, if the number of rows to be processed is sufficient to use parallel plan and if the query cost exceeds the Cost Threshold for Parallelism value. If it is required, the SQL Server Database Engine detects the number of threads and distribute the execution of the tasks among these threads. The number of workers that the SQL Server Database Engine assigns for each parallel plan execution to perform a specific task is called the Degree of Parallelism (DOP). Which also determines the number of processors that will be used to execute a parallel plan.

在执行查询之前，SQL Server数据库引擎会根据许多因素检查是否需要并行执行，例如，SQL Server实例是否在具有多个CPU的服务器上运行，是否需要所需的线程数，以及如果要处理的行数足以使用并行计划，并且查询成本超过并行成本阈值 ，则可以满足工作负载类型。 如果需要，SQL Server数据库引擎将检测线程数并在这些线程之间分配任务的执行。 SQL Server数据库引擎为每次并行计划执行分配以执行特定任务的工作人员数量称为并行度 （DOP）。 这也决定了将用于执行并行计划的处理器数量。

The number of processors that are used in the parallel plan execution can be limited using the Max Degree of parallelism option. Setting the Max Degree of Parallelism option to 0 will allow the SQL Server Engine to use all available CPUs on the current server up to 64 processors in the parallel plan execution. Only a Max Degree of Parallelism with a value equal to 1, serial plan execution will be used. You can override the Max Degree of Parallelism default value to specify the number of processors to be used in the parallel plan execution by setting it to any value between 2 and 32,767. If the value of the Max Degree of Parallelism is greater than the actual number of processors in the server, the actual number of processors that will be used in the parallel plan execution is all the available processors. If the SQL Server instance is hosted on a single processor server, the Max Degree of Parallelism value will be ignored.

可以使用“ 最大并行度”选项限制并行计划执行中使用的处理器数量。 将“最大并行度”选项设置为0将允许SQL Server Engine在并行计划执行中使用当前服务器上的所有可用CPU最多64个处理器。 仅使用最大并行度（其值等于1）执行串行计划。 您可以覆盖“最大并行度”默认值，以将并行计划执行设置为2到32,767之间的任何值来指定要在并行计划执行中使用的处理器数量。 如果“最大并行度”的值大于服务器中的实际处理器数量，则将在并行计划执行中使用的实际处理器数量是所有可用处理器。 如果SQL Server实例托管在单处理器服务器上，则“最大并行度”值将被忽略。

There are many ways to set the Max Degree of Parallelism value. The first method is setting the value at the server level using SQL Server Management Studio. From the advanced page of the Server Properties, scroll down to the Parallelism section and override the default value of the Max Degree of Parallelism to the value that fits your workload as in the below:

有许多方法可以设置“最大并行度”值。 第一种方法是使用SQL Server Management Studio在服务器级别设置值。 在“服务器属性”的高级页面上，向下滚动到“并行度”部分，并将“最大并行度”的默认值覆盖为适合您的工作负载的值，如下所示：

The Max Degree of Parallelism value can also be overridden at the server level using the SP_Configure T-SQL command, after enabling the Show Advanced Option configuration. The below script is used to change the default Max Degree of Parallelism value and set it to 4:

启用“显示高级选项”配置后，也可以使用SP_Configure T-SQL命令在服务器级别覆盖“最大并行度”值。 以下脚本用于更改默认的“最大并行度”值并将其设置为4：

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

The third method that can be used to override the Max Degree of Parallelism value is using the OPTION (MAXDOP num) query hint, which will override the server Max Degree of Parallelism for that query with the value specified (num) in the query hint. The below query will not use parallel plan execution although the Max Degree of Parallelism value equal to 0 at the server level due to overriding the value to 1 at the query level suing OPTION (MAXDOP 1) hint:

可以用来覆盖“最大并行度”值的第三个方法是使用OPTION（MAXDOP num）查询提示，它将用查询提示中指定的值（“ num”）覆盖该查询的服务器“最大并行度”。 尽管在服务器级别的最大并行度值等于0，但由于在查询级别使用OPTION（MAXDOP 1）提示将该值覆盖为1，因此以下查询将不使用并行计划执行：

 
USE SQLShackDemo 
GO
SELECT TOP 10 * FROM Employees 
OPTION (MAXDOP 1)
 

Right-click on the SELECT operator from the execution plan of the previous query and choose Properties, you will find that the Degree of Parallelism value is 0, which is the server’s level Max Degree of Parallelism value, but the parallel plan execution is not used due to MaxDopSetToOne reason:

右键单击上一个查询的执行计划中的SELECT运算符，然后选择“属性”，您会发现“并行度”值为0，这是服务器级别的“最大并行度”值，但是未使用并行计划执行由于MaxDopSetToOne原因：

The Max Degree of Parallelism can be overridden also at the database level in SQL Server 2014 SP2 and SQL Server 2016 versions from the newly defined Database Scoped Configurations. From the Advanced Options of the Database Properties window, the Max Degree of Parallelism value can be configured for a specific database by replacing the Max DOP value with the one that fits this database workload as in the below image:

在新定义的数据库范围配置中，也可以在SQL Server 2014 SP2和SQL Server 2016版本的数据库级别覆盖最大并行度。 在“数据库属性”窗口的“高级选项”中，可以通过将“最大DOP”值替换为适合该数据库工作负载的值来为特定数据库配置“最大并行度”值，如下图所示：

Enabling the trace flag 8649 using the QUERYTRACEON query hint can be used to force the query to use the parallel plan execution if you find that the parallel plan execution is better for your query than the serial plan execution as in the following example:

如果发现并行计划执行比串行计划执行更好，那么使用QUERYTRACEON查询提示启用跟踪标志8649可以强制查询使用并行计划执行，如以下示例所示：

 
USE SQLShackDemo 
GO
SELECT  ROW_NUMBER() over(partition by EmpID order by EmpID Desc) as RowID
FROM Employees EMP
JOIN Employee_Department EMPD
ON EMP.EmpDEPID  =EMPD.DepID 
WHERE EmpAddress not like '%AMM%' and EmpBirthDay >'1990-05-01' and EmpID>250
OPTION (QUERYTRACEON 8649) 
 

Another way that can be used to control the Max Degree of Parallelism is configuring a workload group while using the SQL Server Resource Governor Enterprise Edition feature by using the WITH MAX_DOP=value hint in the CREATE WORKLOAD GROUP group_name T-SQL statement as in the below statement:

可以用来控制最大并行度的另一种方法是在使用SQL Server资源调控器企业版功能时配置工作负载组，方法是使用CREATE WORKLOAD GROUP group_name T-SQL语句中的WITH MAX_DOP = value提示，如下所示声明：

 
CREATE WORKLOAD GROUP MaxDOPTest
WITH (MAX_DOP = 4)
USING [DEFAULT];    
 

结论 (Conclusion)

Parallelism is a fantastic SQL Server option that is used to take benefits of having more than one processor on the hosting server and split the current task into smaller tasks and distribute it among the available processors in order to complete it faster. Max Degree of Parallelism can be used to set the maximum number of processors that can be used to run the query in the parallel plan execution. There are many ways that can be used to set that value at the server, database, and query level.

并行是一种出色SQL Server选项，用于利用以下优势：在主机服务器上拥有多个处理器，并将当前任务拆分为较小的任务，然后将其分配给可用处理器以更快地完成任务。 最大并行度可用于设置并行计划执行中可用于运行查询的最大处理器数量。 有许多方法可用于在服务器，数据库和查询级别设置该值。

翻译自: https://www.sqlshack.com/different-ways-set-max-degree-parallelism-sql-server/