sqlserver 并行基础、用法与常见问题_并行的开销阈值-优快云博客

本文链接：https://blog.youkuaiyun.com/Hehuyi_In/article/details/89470005

一、并行查询的优缺点

1. 优点

改善大查询的响应时间，在资源足够时，这种改善几乎是线性的
对终端用户完全透明
适用于数据仓库工作负载（少量大查询）

2. 缺点

不适合OLTP工作负载（大量小查询）
增加查询执行的开销
可能会降低整体服务器吞吐量

二、 SQL Server如何进行并行查询

SQL Server通过将输入数据水平分区为大致相等大小的集合，为每个CPU分配一组，然后在每个集合上执行相同的操作（例如聚合、连接等）来并行化查询。

即主要有三步：

将输入数据进行水平分区
将分区分到每个CPU
同时在每个分区执行相同操作

例如，假设我们使用两个CPU来执行在整数列上分组的哈希聚合。

我们创建两个线程（每个CPU一个），将所有奇数（组1）发送至线程1、偶数（组2）发送至线程2，每个线程执行相同的散列聚合运算，合并运算后就会得到正确的结果。这种并行查询执行方法既简单又可扩展。

三、谁决定SQL Server是否使用并行查询

1. 相关参数

SQL Server中有并行相关的设置主要有两个：最大并行度及并行阈值

最大并行度 max degree of parallelism：按任务设置最大并行度 (MAXDOP) 限制，它不是按请求限制或查询限制。这意味着在并行查询执行期间，一个请求可生成多个任务（不超过 MAXDOP 限制），并且每个任务将使用一个worker 和一个scheduler。

默认值为0，即不限制；设置为1表示始终使用串行执行计划
如果计算机只有一个处理器，将忽略 max degree of parallelism 值
可以通过在查询、索引语句等中指定 MAXDOP hint来覆盖 max degree of parallelism 值
并行查询使用的线程数可能超过DOP（类似oracle）。如果在运行并行查询时检查sys.sysprocesses，可能会看到比DOP更多的线程。DOP确定每个运算符的线程数，而不是每个查询计划的线程总数。查询使用的所有线程都被分配给同一组scheduler，并且查询仅使用DOP CPU，而不管线程的总数。详细可参考线程和任务体系结构指南中的“计划并行任务”部分，以及 SqlServer 任务调度概念与过程简介_Hehuyi_In的博客-优快云博客_sqlserver 调度

并行阈值 cost threshold for parallelism：仅当运行同一查询的串行计划的预估成本高于 cost threshold for parallelism 中设置值时，SQL Server才使用并行计划。

cost是一个抽象单位，指的是在特定硬件配置中运行串行计划估计需要的开销，而不是时间单位
该参数可设置为 0 到 32767 之间的任何值，默认值为 5
在某些情况下，即使查询的计划预估成本小于当前 cost threshold for parallelism 的值，也有可能选择并行计划。这可能是由于参数嗅探问题导致的，即之前生成执行计划所用的参数计划预估成本高于 cost threshold for parallelism 的值

2. 参数设置

图形界面

选中服务器实例 -> 右键属性->高级，设置保存即可，不需重启

命令行

sp_configure 'show advanced options', 1;
go
reconfigure with override;
go
sp_configure 'max degree of parallelism', 3;
go
reconfigure with override;
go

3. 最大并行度建议值

从 SQL Server 2008 到 SQL Server 2014 (12.x)，请使用以下准则配置“最大并行度”

服务器配置	处理器数目	指南
具有单个 NUMA 节点的服务器	小于或等于 8 个逻辑处理器	将 MAXDOP 保持为小于或等于逻辑处理器的数量
具有单个 NUMA 节点的服务器	大于 8 个逻辑处理器	将 MAXDOP 保持为 8 个
具有多个 NUMA 节点的服务器	每个NUMA 节点拥有小于或等于 8 个逻辑处理器	将 MAXDOP 保持为小于或等于每个 NUMA 节点的逻辑处理器的数量
具有多个 NUMA 节点的服务器	每个 NUMA 节点大于 8 个逻辑处理器	将 MAXDOP 保持为 8 个

从 SQL Server 2016 (13.x) 开始，请使用以下准则配置“最大并行度”

服务器配置	处理器数目	指南
具有单个 NUMA 节点的服务器	小于或等于 8 个逻辑处理器	将 MAXDOP 保持为小于或等于逻辑处理器的数量
具有单个 NUMA 节点的服务器	大于 8 个逻辑处理器	将 MAXDOP 保持为 8 个
具有多个 NUMA 节点的服务器	每个 NUMA 节点拥有小于或等于 16 个逻辑处理器	将 MAXDOP 保持为小于或等于每个 NUMA 节点的逻辑处理器的数量
具有多个 NUMA 节点的服务器	每个 NUMA 节点大于 16 个逻辑处理器	将 MAXDOP 保持为每个 NUMA 节点逻辑处理器数量的一半，最大值为 16

四、如何强制使用并行

由上面我们知道，sqlserver是否启用并行取决于系统配置、参数设置以及sql成本，那是否可以强制sql使用并行呢？

注意以下只是介绍方法，强制使用并行未必能提升性能。

1. 2016 SP1 CU2之前

Trace Flag 8649 在SQL Server中可以在指定查询启用并行，注意这个标记无官方文档记录，勿轻易使用。

使用时只需加上查询提示：Option(querytraceon 8649)即可

SELECT PP.[ProductID]
      ,[Name]
      ,[ProductNumber]
      ,PTH.ActualCost 
      ,PTH.TransactionType      
  FROM [MSSQLTipsDemo].[Production].[Product] PP
  JOIN [MSSQLTipsDemo].[Production].TransactionHistory PTH
  ON PP.ProductID =PTH.ProductID 
  WHERE PP.SellEndDate <GETDATE()-2 AND MakeFlag =1 and Weight >148
  OPTION(QUERYTRACEON 8649);

2. 2016 SP1 CU2及以后

2016 SP1开始，引入了查询提示OPTION(USE HINT('hint名'))以替代OPTION(QUERYTRACEON)。OPTION(USE HINT('hint名'))不需要sysadmin权限来执行，并且使用时可直接填入hint名，无需记住跟踪标志号。

SQL Server 2016 SP1的CU2引入了一个新hint，用于强制特定查询使用并行。

可以重写上一个查询，使用新的ENABLE_PARALLEL_PLAN_PREFERENCE查询提示

SELECT PP.[ProductID]
      ,[Name]
      ,[ProductNumber]
      ,PTH.ActualCost
      ,PTH.TransactionType     
  FROM [MSSQLTipsDemo].[Production].[Product] PP
  JOIN [MSSQLTipsDemo].[Production].TransactionHistory PTH
  ON PP.ProductID =PTH.ProductID
  WHERE PP.SellEndDate <GETDATE()-2 AND MakeFlag =1 and Weight >148
  OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

执行计划如下

如果遇到如下错误，说明版本不够

3. 指定查询的最大并行度

使用查询提示OPTION(MAXDOP 1)即可

SELECT * FROM Pubs.dbo.Authors 
OPTION(MAXDOP 1);

4. 配置并行索引操作

并行索引执行和 MAXDOP 索引选项适用于下列 Transact-SQL 语句：

CREATE INDEX
ALTER INDEX REBUILD
DROP INDEX（只适用于聚集索引）
ALTER TABLE ADD (索引) CONSTRAINT
ALTER TABLE DROP (聚集索引) CONSTRAINT
不能在 ALTER INDEX REORGANIZE 语句中指定 MAXDOP 索引选项

例如

CREATE INDEX IX_ProductVendor_NewVendorID ON Purchasing.ProductVendor (BusinessEntityID) 
WITH (MAXDOP=8);

ALTER INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor 
REBUILD WITH (MAXDOP=8);

配置并行索引操作 - SQL Server | Microsoft Learn

五、 Parallelism Operator 并行操作符（别名exchange）

优化器在线程的边界处放置并行操作符，以便行在线程间移动，它是将并行计划的执行上下文连接在一起的粘合剂。

1. 组成部分

并行操作符实际上是两个操作符：生产者和消费者

生产者：连接输入端的线程
消费者：连接输出端的线程

请注意，虽然大多数操作符之间的数据流是基于拉取的，但生产者和消费者之间的数据流大部分是基于推送的。也就是说，生产者用行填充数据包并将其“推送”给消费者。此模型允许生产者和消费者线程独立执行。

我们将生产者放在查询子树的根，生产者从其子树中读取输入行，将行组装成数据包，并将数据包路由到适当的消费者。消费者从其生产者接收数据包，并将行返回到其父操作符。

例如，以下并行度为2的例子，由两个生产者和两个消费者组成：

2. 分类

可以通过三种不同的方式对并行操作符进行分类。

(1) 根据生产者、消费者线程数量分类

根据生产者、消费者线程的数量（多对一、多对多、一对多）对并行操作符进行分类，主要可分为：

聚合流
重分区流
分布流

聚合流之后的运算符（上图左边）串行运行，而它之前的运算符（上图右边）并行运行。任何并行计划中的根操作符始终是聚合，因为任何查询计划的结果必须最终聚集到单个线程以返回给客户端。

分布流则与聚合流交换相反：其之后并行运行，而之前串行运行。

(2) 根据如何将行从生产者路由到消费者分类

在生产者与消费者的模型中，数据通过一定规则由生产者流动到消费者那里。可以根据将行从生产者路由到消费者的方法对并行操作符进行分类，此属性称为并行操作符的分区类型。分区类型仅对前面提到的重分区流或分发流并行操作符有意义。

SQL Server支持5种分区类型

类型	描述
广播	每行均被发送给所有消费者。
哈希	最常见，通过对当前行的一或多个列值进行哈希来选择消费者。
轮循	每个新行以固定顺序轮流发送给消费者。
请求	将行发送给第一个请求的消费者，是基于拉取的数据流。仅用于分区表。
范围	通过对每行的一列执行范围函数选择消费者，特定输入列所属的范围决定了哪个消费者获得该行。仅在index build和stats queries时使用

在图形执行计划中可以看到使用的分区类型以及该过程中使用的列，例如：

(3) 根据并行操作后数据是否有序分类

根据并行操作后数据是否有序，并行操作还可以分为Merge和Non-Merge。当然，有序的成本明显高于无序。

需要排序输入的运算符包括Stream Aggregate、Segment和Merge Join。

下图显示了一个merge的Repartition Streams操作符：

merge并行操作符的执行计划可以看到有一个Order By属性，如图：

六、并行执行计划

并行执行计划最显著的特点在于运算符图标上有一个并行度符号

--串行执行
select COUNT(*) from dbo.bigTransactionHistory option(maxdop 1);
--并行执行
select COUNT(*) from dbo.bigTransactionHistory option(maxdop 2);

观察上面两条简单语句的执行计划可以发现，在预估cost中，实际上并行只是CPU预估加倍，IO预估不变。

每个并行执行计划都有一个根操作符，即图形执行计划中最左边的Gather stream运算符。所有的并行执行计划也都会有一个固定的串行区域，即根操作符及其左边所有部分。它们由主线程Thread Zero控制，同时其执行上下文也是context zero。

而并行区域，顾名思义就是根操作符所有靠右的部分。并行部分可能有多个分支，每个分支都可以同时执行（各分支有自己的tasks），分支自身可以是并行也可以是串行，但分支不会使用主线程Thread Zero。

关于分支可以执行如下语句查看相关执行计划属性（2012及之后版本）。

select a.productid,count_big(*) as rows
from dbo.bigproduct as a inner join dbo.bigtransactionhistory as b on a.productid=b.productid
where a.ProductID between  1000 and 5000
group by a.productid
order by a.productid;

如上图，最大并行度设置为4，有3个branches，则这里使用的线程数就是4*3=12，再加上一个主线程 thread zero，这个并行查询所使用的线程总数为13个。

而并行和串行的区别联系可以理解成下图，主线程在串行中实际就是它的执行线程

七、 SQL Server中无法并行的操作

SQL Server并不是所有操作都可以并行的，有些操作符会导致整个执行计划无法并行，而有些会使得某些分支无法并行（串行部分可能会成为性能瓶颈）。

1. 会使整个执行计划只能串行的操作

所有用户自定义函数（UDF）
CLR UFDs with data access（没用过，参考SQL Server CLR 集成简介 - ADO.NET | Microsoft Learn）
各类内建函数，如OBJECT_ID()、ERROR_NUMBER()、@@TRANCOUNT...
动态游标

2. 相应部分执行计划只能串行的操作

并行计划中的串行部分可能导致性能瓶颈，有时可能比完全使用串行还慢

System table scans
Backward scans（即扫描方向与聚集索引创建排序相反，常见于order by desc）
Global scalar aggregate
Sequence functions
递归查询
Multi-consumer spool
TOP关键字
表值函数（TVF）

八、最常见的并行等待事件——CXPACKET

CXPACKET是SQL Server中最常见的等待之一，通常对这个等待的解释是并行中某些线程工作快、某些线程工作慢而产生的差异。这个说法是对的，但不够专业，实际上根据生产者消费者模型来分析是：

CXPACKET Waits = Class eXchange PACKET

针对生产者：所有Buffer packets都已被填满，无法继续生产（生产快于消费）
针对消费者：所有Buffer packets都是空的，没有数据可以消费（消费快于生产）

Cxpacket常见产生原因

九、并行的问题

以下截图来自oracle，但sqlserver的问题其实类似

1. 自动并行的问题

关于第三点，有一个著名的线程饥饿问题(worker thread starvation)：

前面说过，线程的是按照分支(branches)及并行度授予的。如果并行度高，复杂的查询下分支又很多，很可能针对某个查询分配过多线程。如果这类查询还高并发，这时出现线程饥饿的几率就大大增加了。

举个简单的实例，这个查询最大并行度为16，有5个分支，所申请的线程就是5*16=80个，再加主线程一共81个！

2. 自适应并行的问题

3. 并行死锁

并行死锁在并行执行中也会偶尔出现，官方给出的解释是SQL Server的”BUG”，只需将查询的MAXDOP调整为1，死锁就会自动消失（废话那都变成串行了）。并行死锁原理图如下：

这里用一个例子说明下并行死锁的原因，以便更好地利用并行。

生成测试数据

接下来执行如下语句，取30000以下最大偶数，此时将并行数maxdop随意调整为奇数，3、5、7执行都可以迅速返回结果。

但将并行数调整为偶数时，执行时间居然长达数秒，打开profiler跟踪dead lock chain发现并行数为偶数时出现了死锁。

真是蹊跷，到底发生了什么？

我们具体分析下并行死锁的相应执行计划。

访问基表数据时用的是聚集索引扫描，但扫描方式是backward，而SQL server中只有forward scan可以并行扫描，backward只能串行扫描
因此在向各个threads分发数据时（distribute streams）采用roundrobin轮询分发数据，这势必造成奇偶数据按threads分开流向下一个过滤操作符
在Filter时将奇数的数据过滤，而相应的threads也就没有了数据
在最后exchange汇总数据时(gather streams)有的threads没有数据，因而造成死锁。

反观并行采用奇数并行数，这时当分发数据时就不会造成某个thread所持有的数据只是奇数或是偶数，也就不会造成后来的情形，死锁也就不会出现。