如何在SQL Server 2016中使用并行插入以提高查询性能

介绍 (Introduction )

In the first part of this article, we will discuss about parallelism in the SQL Server Engine. Parallel processing is, simply put, dividing a big task into multiple processors. This model is meant to reduce processing time.

在本文的第一部分，我们将讨论SQL Server Engine中的并行性。 简单地说，并行处理就是将一个大任务划分为多个处理器。 该模型旨在减少处理时间。

• SQL Server can execute queries in parallel
  SQL Server可以并行执行查询
• SQL Server creates a path for every query. This path is execution plan
  SQL Server为每个查询创建一个路径。 此路径是执行计划
• The SQL Server query optimizer creates execution plans
  SQL Server查询优化器创建执行计划
• SQL Server query optimizer decides the most efficient way for create execution plan
  SQL Server查询优化器决定创建执行计划的最有效方法

Execution plans are the equivalent to highways and traffic signs of T-SQL queries. They tell us how a query is executed.

执行计划等效于T-SQL查询的高速公路和交通标志。 他们告诉我们如何执行查询。

In the SQL Server Engine, there is a parameter to set up a limit aka governor for CPU usage. This setting name is MAXDOP (maximum degree of parallelism). We can set this parameter in T-SQL or SQL Server Management Studio under the properties of the server. “0” means SQL Server can use all processors if they are necessary

在SQL Server引擎中，有一个参数来设置CPU使用率的限制（也称为限制器）。 该设置名称为MAXDOP（最大并行度）。 我们可以在T-SQL或SQL Server Management Studio的服务器属性下设置此参数。 “ 0”表示SQL Server可以在必要时使用所有处理器

We can change this option using the following T-SQL script

我们可以使用以下T-SQL脚本更改此选项

 
EXEC sp_configure 'max degree of parallelism', 4;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  
EXEC sp_configure 'max degree of parallelism'
 

Parallel execution plans, MAXDOP and ENABLE_PARALLEL_PLAN_PREFERENCE

并行执行计划，MAXDOP和ENABLE_PARALLEL_PLAN_PREFERENCE

The Query optimizer analyzes possible execution plans and then chooses the optimal execution plan. This selection is based on the value of query estimated cost. In some cases, the SQL Server query optimizer chooses a parallel execution plan, primarily because the SQL Server query optimizer decides a parallel execution plan cost is more optimum than a serial execution plan.

查询优化器分析可能的执行计划，然后选择最佳执行计划。 该选择基于查询估计成本的值。 在某些情况下，SQL Server查询优化器选择并行执行计划，这主要是因为SQL Server查询优化器确定并行执行计划的成本比串行执行计划更为理想。

Now we will look at some examples of parallel execution plans and properties. This query will generate a parallel execution plan.

现在，我们将看一些并行执行计划和属性的示例。 该查询将生成并行执行计划。

 
SELECT SOD.[SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
  FROM [Sales].[SalesOrderDetail] SOD
  INNER JOIN SALES.[SalesOrderHeader] SOH ON SOD.SalesOrderID = SOH.SalesOrderID  
  ORDER BY SOD.ModifiedDate DESC , SOH.rowguid DESC
 

In the previous example, we can see parallel operators and as has been highlighted, Degree of Parallelism show us how many threads are used in this query.

在前面的示例中，我们可以看到并行运算符，并且突出显示， 并行度显示了此查询中使用了多少个线程。

In this step, we will look at the MAXDOP query hint. We can dictate to the SQL Server query optimizer how many threads will run in parallel. This hint specifies the number of threads for the que