sql并行度_SQL Server最大并行度的重要性

sql并行度

In this article, we will discuss how the Max Degree of Parallelism works in SQL Server and how does it improve the query performance. SQL Server Degree of Parallelism is the processor conveyance parameter for a SQL Server operation, and it chooses the maximum number of execution distribution with the parallel use of different logical CPUs for the SQL Server request. Microsoft SQL Server allows setting this Degree of Parallelism parameter value at the SQL Server instance level or Query level. If you do not specify the SQL Server Degree of Parallelism value at the SQL Server instance, then each request or operation has to rely on SQL Server default value and random CPU allocations.

在本文中，我们将讨论最大并行度在SQL Server中的工作方式以及它如何提高查询性能。 SQL Server并行度是SQL Server操作的处理器传输参数，它选择并行执行SQL Server请求的不同逻辑CPU来选择最大执行分配数。 Microsoft SQL Server允许在SQL Server实例级别或查询级别设置此并行度参数值。 如果未在SQL Server实例中指定SQL Server并行度值，则每个请求或操作都必须依赖SQL Server默认值和随机CPU分配。

SQL Server Degree of Parallelism is especially helpful to the Database Warehouse query engine, Telecommunication database, and partitioned table-based database. The Degree of Parallelism parameter should be configured with small computation and data usage analysis. The default value is 0 at the SQL Server instance level, which relied on the database engine to use several logical CPUs as required by the SQL Server Query.

SQL Server并行度对数据库仓库查询引擎，电信数据库和基于分区表的数据库特别有用。 并行度参数应通过较小的计算和数据使用分析进行配置。 在SQL Server实例级别，默认值为0，这取决于数据库引擎根据SQL Server查询的要求使用多个逻辑CPU。

使用SSMS配置SQL Server最大并行度 (Configuring SQL Server Max Degree of Parallelism using SSMS)

SQL Server Management Studio (SSMS) >> Right-click on the SQL Server Instance > properties >> Advanced >> Max Degree of Parallelism

SQL Server Management Studio（SSMS）>>右键单击SQL Server实例>属性>>高级>>最大并行度

Here, we can see that the default configuration of the SQL Server Degree of Parallelism value is 0.

在这里，我们可以看到“ SQL Server并行度”值的默认配置为0。

The user can change the Max Degree of Parallelism by SSMS in the SQL Server Advanced property.

用户可以在SQL Server Advanced属性中通过SSMS更改“最大并行度”。

最大并行度在SQL Server中如何工作？ (How does Max Degree of Parallelism work in SQL Server?)

When SQL Server gets any request from the client application end, it compiles and prepares the quickest execution plan with the minimal cost of server assets. If query cost is high, then the reason should be either database engine is not able to make a proper execution plan due to worse query logic or complex query structure. With choosing the quickest execution plan, the SQL Server request will utilize the required logical CPUs to finish the operation or query execution.

当SQL Server从客户端应用程序端收到任何请求时，它将编译并准备以最少的服务器资产成本进行最快的执行计划。 如果查询成本很高，则原因可能是由于查询逻辑较差或查询结构复杂，数据库引擎无法制定适当的执行计划。 通过选择最快的执行计划，SQL Server请求将利用所需的逻辑CPU来完成操作或查询执行。

For instance, we have two situations with a single thread and multi-thread distribution with defining the SQL Server Degree of Parallelism parameter. In the first case, the parameter value is set it to 1, and SQL Server request execution utilizes a single logical CPU and completing operation or returning a query result set.

例如，在定义SQL Server并行度参数的情况下，有两种情况，即单线程和多线程分布。 在第一种情况下，将参数值设置为1，并且SQL Server请求执行利用单个逻辑CPU并完成操作或返回查询结果集。

In the second case, the Max Degree of Parallelism parameter value is set to higher than 1/0. 0 stands for SQL Server choose the max number of CPU usage for the query execution as required by the execution plan. Greater than one value will allow using maximum CPUs as defined with a parameter to execute the request as needed. For example, the SQL Server Max Degree of Parallelism parameter is five, and query execution required three only then the query will be distributed in 3 execution only.

在第二种情况下，“最大并行度”参数值设置为大于1/0。 0代表SQL Server根据执行计划的要求选择查询执行的最大CPU使用量。 大于一个值将允许使用通过参数定义的最大CPU来根据需要执行请求。 例如，SQL Server的“最大并行度”参数为5，而查询执行只需要3个，那么查询将仅分配3个执行。

For the SQL Server request execution, a processor will circulate the execution with multiple logical CPUs as defined in the parameter and as CPU costs required by the query. Each dispersed execution will have an equivalent period to finish the task. If any threads completed earlier than the expected time, then that threads will hold up till the last one to complete the undertaking.

对于SQL Server请求执行，处理器将使用参数中定义的多个逻辑CPU以及查询所需的CPU成本来循环执行。 每个分散的执行将有一个相等的时间来完成任务。 如果有任何线程早于预期时间完成，则该线程将一直持续到最后一个线程才能完成任务。

将逻辑CPU分配到SQL Server实例 (Distribution of Logical CPUs to the SQL Server Instance)

Logical CPUs can be relegated to the SQL Server instance to access those only by the SQL Server Processor property. Logical CPUs will be listed in the server property as are configured or associated with the machine or virtual machine. A rundown of CPUs will be bifurcated with the numa node (Processor Socket).

可以将逻辑CPU委托给SQL Server实例，以仅通过SQL Server Processor属性访问它们。 逻辑CPU将在服务器属性中列出，并与计算机或虚拟机配置或关联。 CPU的消耗将与numa节点（处理器插槽）分叉。

Logical CPUs can be allotted to two different entities, process affinity mask, and I/O affinity mask. Use can choose the distribution of logical processor units based on the querying size, database size, nature of data, and I/O operations.

逻辑CPU可以分配给两个不同的实体，进程亲和力掩码和I / O亲和力掩码。 用户可以根据查询大小，数据库大小，数据性质和I / O操作来选择逻辑处理器单元的分布。

By default, these options will be set to automatic with selected checkboxes to both properties. Automatically set processor affinity mask for all processors and Automatically set an I/O affinity mask for all processors. The automatic option will use any free CPUs to play out the operation as required. A best practice is to set individual logical CPUs to both containers because if you assign the same logical resource to both substances, then perhaps one of it doesn’t get an opportunity to get to it.

默认情况下，这些选项将设置为自动，并且选中两个属性的复选框。 为所有处理器自动设置处理器亲和力掩码，并为所有处理器 自动设置I / O亲和力掩码 。 自动选项将使用任何可用的CPU来按要求播放操作。 最佳实践是为这两个容器设置单独的逻辑CPU，因为如果您为这两种物质分配了相同的逻辑资源，那么也许其中之一就没有机会使用它。

On the off chance that database and application are in the same server or machine, at that point, keep some room in SQL Server CPU allocation for permitting the remainder of CPUs to access by application and operating system. Max Degree of Parallelism parameter uses maximum CPUs that are assigned to SQL Server by this screen to perform parallel execution.

此时，如果数据库和应用程序位于同一台服务器或计算机中，则应在SQL Server CPU分配中留出一定的空间，以允许其余的CPU被应用程序和操作系统访问。 “最大并行度”参数使用此屏幕分配给SQL Server的最大CPU来执行并行执行。

SQL Server并行度如何帮助提高查询性能？ (How does SQL Server Degree of Parallelism help in better Query Performance?)

Day by day size of the database is expanding and turn out to be very basic to oversee common data source with the data warehouse. Data warehouse holds typical data information for the end-user response with the enormous measured in gigabytes or terabytes of data. For client responses, complex queries run in the data warehouse, and ideally, it requires more processing time to finish the execution. The Query execution process should be distributed in multiple threads with allocating several logical CPUs to the query execution with the help of the SQL Server Degree of Parallelism to reduce the query executing time.

数据库的大小每天都在扩大，事实证明，监督数据仓库中的通用数据源非常基础。 数据仓库保存着针对最终用户响应的典型数据信息，其中包含以GB或TB级数据为单位的巨大数据量。 对于客户响应，复杂查询在数据仓库中运行，理想情况下，需要更多处理时间才能完成执行。 查询执行过程应分布在多个线程中，并借助SQL Server并行度为查询执行分配多个逻辑CPU，以减少查询执行时间。

The default an incentive for the parameter of SQL Server Degree of Parallelism is 0 (Zero), which means SQL Server query can utilize all CPUs for the parallel execution. On the off chance that this Parallelism isn’t zero, at that point that the single query can use the number of CPUs. Both situations are having various requirements and use cases. If we use Parallelism with zero, then long execution queries will utilize the most extreme CPUs, and the rest of the SQL Server request will be in a queue with CXPACKET wait type and suspended status and those requests will wait till get the free CPU resources. In the subsequent case, if a Parallelism parameter value is smaller (1,2 or 3), then any longer query execution will take additional time than expected time to finish it since it needs more resources.

SQL Server并行度参数的默认激励为0（零），这意味着SQL Server查询可以利用所有CPU进行并行执行。 并行度不为零的可能性很小，这时单个查询可以使用CPU的数量。 两种情况都有不同的要求和用例。 如果我们将Parallelism设为零，那么长执行查询将使用最极限的CPU，而其余SQL Server请求将处于具有CXPACKET等待类型和暂停状态的队列中，并且这些请求将等待直到获得可用的CPU资源。 在随后的情况下，如果Parallelism参数值较小（1、2或3），则更长的查询执行时间将比预期的时间花费更多的时间来完成它，因为它需要更多的资源。

使用MAXDOP选项SQL语句并行 (Parallelism at SQL Statement using MAXDOP option)

The MAXDOP option is utilized to powers the maximum level of parallelism value at the individual T-SQL statement. MAXDOP value cannot be the same as the SQL Server Degree of Parallelism parameter value. Actually, it is more helpful when additional CPU resources are required to execute the specific SQL statement.

MAXDOP选项用于在单个T-SQL语句中为最大并行度值供电。 MAXDOP值不能与“ SQL Server并行度”参数值相同。 实际上，当需要额外的CPU资源来执行特定SQL语句时，它会更有帮助。

Users can decide the Parallelism parameter value based on a load of SQL Server request, several logical CPUs, Storage Type (I/O Performance), Query Type (Report, Single execution, Remote Procedure, etc…), Partition and many more.

用户可以根据SQL Server请求的负载，几个逻辑CPU，存储类型（I / O性能），查询类型（报告，单执行，远程过程等），分区等来决定并行参数值。

If no longer execution type of report queries in the database, then go with the 1,2,3, As the availability of the CPU resource and define the Parallelism with the SQL statement using the MAXDOP option for the report queries. MAXDOP can be set to limit the query to execute on a number of logical CPUs. MAXDOP option can be added at the end of the SQL Query statement. For example,

如果数据库中不再有报告查询的执行类型，则使用1,2,3，作为CPU资源的可用性，并使用SQLD语句为报告查询使用MAXDOP选项定义并行性。 可以将MAXD​​OP设置为限制查询在多个逻辑CPU上执行。 可以在SQL Query语句的末尾添加MAXDOP选项。 例如，

SELECT * FROM Production.Product WHERE ProductID = 319 OPTION(MAXDOP 4)

Here, the MAXDOP value is four for the above T-SQL statement and Query can use a maximum of four logical CPUs to complete the execution.

在这里，上述T-SQL语句的MAXDOP值为4，而Query最多可以使用四个逻辑CPU来完成执行。

For example, the current configuration of the SQL Server Degree of Parallelism can be checked by the T-SQL query as well with the help of SYS.SYSCONFIGURES table.

例如，SQL Server并行度的当前配置可以通过T-SQL查询以及借助SYS.SYSCONFIGURES表来检查。

SELECT * 
FROM SYS.SYSCONFIGURES 
WHERE comment LIKE '%degree%'

Here, we have an example with the execution plan of the T-SQL statement with several JOINS. The first part of the query statement in the execution plan represents the Degree of Parallelism value, as shown in the below image.

在这里，我们有一个带有多个JOINS的T-SQL语句的执行计划示例。 执行计划中查询语句的第一部分表示“并行度”值，如下图所示。

Here, Parallelism value is one that means a single CPU is used to complete the execution of this statement. Now, what happens if it is more than 1? Execution will be distributed in the number of parallel threads as configured the Parallelism and required CPUs by the query. In the below image, we can see the Parallelism task in the execution plan while the query used 40 CPUs. Gather Steams in the execution plan collects task information of each thread or worker.

在这里，并行度值是一个值，表示一个CPU用于完成该语句的执行。 现在，如果大于1会怎样？ 执行将按照查询配置的并行线程数和所需的CPU数量分布在并行线程中。 在下图中，我们可以看到执行计划中的Parallelism任务，而查询使用了40个CPU。 执行计划中的Gather Steam收集每个线程或工作程序的任务信息。

结论 (Conclusion)

Database sizes are rapidly expanding, and interest for SQL Server queries are growing with the greater unpredictability in the industry. More CPUs on a server is a more thing can do without a moment’s delay with thread distribution in SQL Server. SQL Server Degree of Parallelism helps to improve performance on most undertakings with parallel threading internally. Acknowledged that each operation can’t be made parallel, but Microsoft SQL Server covers most of it.

数据库规模正在Swift扩大，并且随着行业中更大的不可预测性，对SQL Server查询的兴趣也在增长。 服务器上更多的CPU可以做更多的事情，而不会因SQL Server中的线程分发而造成片刻的延迟。 SQL Server并行度可通过内部并行线程帮助提高大多数任务的性能。 确认不能使每个操作并行执行，但是Microsoft SQL Server涵盖了大部分操作。

You can refer to the article, Different Ways to set the Max Degree of Parallelism in SQL Server to get more knowledge about the Parallelism.

您可以参考文章“ 在SQL Server中设置最大并行度的不同方法”以获取有关并行性的更多知识。

翻译自: https://www.sqlshack.com/importance-of-sql-server-max-degree-of-parallelism/

sql并行度