第12/24周 并行执行计划

 

在这一期的性能调优培训里,我想详细讲解下SQL
Server里的并行执行计划(Parallel
Execution Plans)
。执行一个有并行执行计划的查询,意味着SQL
Server从执行计划里进行必须的运算符需要使用多线程。首先我会介绍下并行执行计划里最常用的运算符,然后我们来分析下SQL
Server是如何决定是否要使用并行执行计划。

   
许多有经验的数据库开发或者DBA都曾经头痛于并行查询计划,尤其在较老版本的数据库中(如sqlserver2000、oracle
7、mysql等)。但是随着硬件的提升,尤其是多核处理器的提升,并行处理成为了一个提高大数据处理的高效方案尤其针对OLAP的数据处理起到了很好的作用。

并行运算符(Parallel Operators)

对于并行执行计划最常见的误解就是多线程只用于整个执行计划。这是错误的,因为SQL
Server可以分配多个工作线程给所有可并行(parallelism-aware)运算符。这就意味者一个大型的并行计划可以消耗大量的线程。SQL
Server在并行计划里区分2类运算符:可并行(parallelism-aware)运算符,和称为交换运算符(Exchange
Operators)

  • 很多传统的运算符可以使用多线程来进行它们的工作,它们是可并行(parallelism-aware)运算符:索引扫描(Index
    Scan),索引查找(Index Seek),嵌套循环(Nested
    Loop),哈希连接(Hash Join),排序(Sort)等。
  • 交换运算符(Exchange
    Operators)
    是在并行执行计划里,用来分发和合并多线程之间的行。

SQL
Server使用下列3个交换运算符:

  • Distribute Streams:
    在并行计划中,从一个单线程里区域转变为多线程区域(接收记录的单个输入流,并生成多个输出流。)。
  • Repartition Streams:
    在线程间重新分配行(例如,当前一个运算符是并行哈希连接)(处理多个流并生成多个记录流。)。
  • 威尼斯官方网站登录,Gather
    Streams:在并行计划中,从多线程区域转变为单线程区域(处理几个输入流并通过组合这几个输入流生成单个记录输出流。)。

当你阅读并行执行计划时,你总会看到2种运算符的结合。每个执行计划必须生成一个单线程的结果,因此在并行执行计划的结尾,你总能看到Gather
Streams
运算符。

威尼斯官方网站登录 1

查询优化器是否生成一个并行执行计划也取决于你使用的查询结构是否会阻止并行计划,例如:

  • T-SQL
    语句和SQL公共语言运行库的自定义函数(SQLCLR UDFs);
  • 内建函数,像OBJECT_ID(),
    ERROR_NUMBER(), @@TRANCOUNT等;

在并行计划里,也有很多查询结构会强制串行区域(这部分操作会使用并行计划):

  • 系统表扫描(System Table
    Scans)
  • 序列函数(Sequence
    Functions)
  • 向后扫描(Backward
    Scans)
  • 递归查询(Recursive
    Queries)
  • 表值函数(TVFs)
  • TOP

在你的并行计划里,串行区域越少,你的查询越快。当你下次写你的查询时,好好考虑这点。

   
充分高效地利用并行查询需要对调度、查询优化和引擎工作等有一个比较好的了解,但是针对一般场景的应用我们只需要如何常规使用即可,这里也就不深入描述了,感兴趣可以一起讨论。

什么时候使用并行(When to go parallel?)

每个执行计划都会被SQL
Server分配一个成本因素(Cost
Factor)
。成本因素就是用来告诉SQL
Server这个执行计划有多贵的简单数字。这个数字越大,执行计划运行的关联成本就越高。

SQL Server有一个称为并行开销阀值(cost threshold for
parallelism)
的配置选项,用来定义成本因素,在哪个点查询优化会考虑使用并行计划。默认这个配置选项是5,也就说查询高于这个成本值,只要并行计划可行,就会使用并行计划。

当并行计划被查询优化器编译好后,最大并行度(Max Degree of
parallelism
(MAXDOP))选项定义对执行计划中的每个并行运算符可用线程数。我刚才提过,并行执行计划里的每个运算符都可以用多线程运行,而不是整个执行计划。当然在并行执行计划里,线程可以被前一个运算符共享和重用。可以通过服务器属性->高级->并行配置这些属性:

威尼斯官方网站登录 2

默认最大并行度(MAXDOP)选项值是0,因此SQL
Server会默认将并行查询穿过所有分配给SQL
Server的CPU内核。当你使用NUMA(Non Uniform Memory
Access)系统时,这个会导致性能问题。最佳实践是限制MAXDOP选项值,在NUMA节点里的核心数(包括超线程的核心数)范围内。这样就可以保证SQL
Server的并行计划呆在NUMA节点内。

    那么这里我就简单介绍下SQLServer中并行的应用?

小结

这一期的性能调优培训里我讲解了SQL
Server里的并行执行计划,你学到了在并行执行计划里涉及到的各种运算符,还有在SQL
Server里你如何配置并行度。如果你想对并行计划有更深入的了解,可以看下…………(此次内容待完善)。

看完这篇文章,性能调优培训的第3个月就已经结束了,在下一个月我会讲解下SQL
Server里的统计信息。请继续关注!

什么是并行?

我们从小就听说过“人多力量大”、“人多好办事”等,其思想核心就是把一个任务分给许多人,这样每个人只需要做很少的事情就能完成整个任务。更重要的是,如果额外的人专门负责分配工作,那么任务的完成时间就可以大幅减少了。

围观PPT:

0812_12_并行执行计划.rar

数糖豆

   
设想你正面对一个装满各式各样糖豆的罐子,并且要求书有多少个。假设你能平均每秒数出五个,需要大于十分钟才能数完这个盒子里的3027个糖豆。

   
如果你有四个朋友帮助你去做这个任务。你就有了多种策略来安排这个数糖豆任务,那让我们模仿SQLServer
将会采取的策略来完成这个任务。你和4个朋友围坐在一个桌子四周,糖果盒在中心,用勺子从盒子中拿出糖豆分给大家去计数。每个朋友还有一个笔和纸去记录数完的糖豆的而数量。

   
一旦一个人输完了并且盒子空了,他们就把自己的纸给你。当你收集完每个人的计数,然后把所有的数字加在一起就是糖豆的数量。这个任务也就完成了。大概1-2分钟,完成的效率提高了四倍多。当然四个人累加也是十分钟左右甚至还要多(因为多出来了分配和累加的过程)。这个任务很好的展示了并行的优点,也没有其他额外的工作需要处理。

使用SQLServer 完成“数糖豆”

    当然SQLServer
不会去数罐子里的糖豆,那我就让它去计算表里的行数。如果表很小那么执行计划如图1:

威尼斯官方网站登录 3

图1  串行执行计划:

这个查询计划使用了单一进程,就好像自己一个人数糖豆一样。计划本身很简单:流聚合操作符负责统计接收来自索引扫描操作符的行数,然后统计出总行数。相似的情况下,如果盒子里面糖豆非常少,虽然分配糖豆的时间会减少很多,但是统计步骤就显得效率不是那么高了,因为相对于大数量的糖豆这部分的所占时间就高很多了。所以当表足够大,SQLServer
优化器可以选择增加更多的线程,执行计划如图2:

威尼斯官方网站登录 4

图2 并行计数计划

 

右侧三个操作符中的黄色箭头图标表示引入了多线程。每个线程被分配了一部分工作,然后完成分分部工作被聚集在一起成为最终结果。如同前面人工数糖豆的例子一样,并行计划有很大可能提高完成速度,因为多线程在计数上更优。

并行如何工作?

 

设想一下,如果SQLServer没有内置对于并行的支持。或许我们只能手动去平均划分并行查询来实现性能优化,然后分别运行分配的流,独立地访问服务器。

威尼斯官方网站登录 5

图3 手动分配并行

每次查询都必须手写分隔表行数的独立查询,确保全表数据都被查询到。幸运的是SQLServer
能在一个处理单元内完成每一个分隔的独立线程,然后接收三个部分结果集只需要三分之一的时间左右。自然地我们还需要额外的时间来合并三个结果集。

并行执行多个串行计划

回想一下图2中显示的并行查询计划,然后假设SQLServer
分配了三个额外的线程在运行时去查询。概括的讲,重新生成并行计划来展示SQLServer
运行三个独立串行的计划流(这个表示是我自己起的不是很精确。)

威尼斯官方网站登录 6

图4: 多串行计划

 

每个线程被分配三个branch 中的一个,最后汇聚到Gather Streams(流聚合)
操作符。注意这个图中只有流聚合操作符带有黄色并行箭头;所以这个操作符是这个计划中仅有的与多线程交互的操作符。这种通用策略有两个原因始适合SQLServer的。首先,所有必要地执行串行计划SQL代码已经存在并且已经被优化多年和在线发布。其次,方法的方位很合适:如果更多线程被调用,SQLServer
能轻易添加额外计划分之来分配更多线程。

额外的线程数量分配给每一个并行计划,这被称为并行度(缩写为DOP)。SQLServer
在查询开始之前就选择了DOP,然后不需要计划重新编译就能改变并行度。最大DOP对于每一个并行区域都是由SQLServer的逻辑处理单元的可利用数量决定的(物理核)

并行扫描和并行页支持

   
图4中的问题是每个索引扫描操作符都会去数整个输入集的每一行。不及时纠正,计划就会产生错误的结果集并且和可能花费更多时间。手工并行的例子通过使用where子句来避免这个问题。

    SQLServer
没有用相同的方法,因为分配工作假定平均地使每个查询接收相等的可利用资源,并且每个数据行需要相同的处理。在一个简单例子中,例如统计一个表中的行数,这种假定可能会效果很好(同一个服务器没有其他活动的时候),并且三个查询可能返回的查询也是完全等时的。

   
与分配固定数量行数给每个线程不同,SQLServer使用存储引擎的功能叫做“Parallel
Page Supplier
”来按需分配行数给线程。在查询计划中是看不到“Parallel
Page Supplier

”的,因为它不是查询处理器的一部分,但是我们能拓展图4来形象的展示他的连接方式:

威尼斯官方网站登录 7

图5:  Parallel Page Supplier

    这里的关键点就是demand-based
(基于需求)架构;通过响应现成的请求提供一个行数的批处理给需要更多工作的线程去做。对比数糖豆的案例,Parallel
Page Supplier
就像是专门用勺子从罐子里面拿出糖豆的过程。只有一个勺子防止两个人都去数相同的豆子。并且其他线程将会数更多豆子来补偿。

   注意Parallel Page Supplier
的使用并不阻止现有的优化像预读扫描(在硬盘上提前读取数据)。事实上,这种预读在这种情况下效率要比单线程还要好,这个单线程是底层的物理扫描而不是之前我们看到的三个独立的手动并行的例子。

    Parallel Page Supplier
也不会限制索引扫描;SQLServer利用它当多线程协同读取一个数据架构。数据架构可能是堆、聚集索引表、或者一个索引,并且操作可以是扫描或者查找。如果后者(查找)更高效,考虑索引查找操作就像一个部分扫描,例如它能查找到第一个符合条件的行然后扫面范围的结尾。

执行上下文

    与手动并行例子的机制相似,但是又与创建独立连接的串行查询,SQLServer
使用了一个轻量级的构造称之为“执行上下文”来实现并行。

   
一个执行上下文来自查询计划的一部分,该内容通过填写在计划重新编译和优化后的细节来产生。这些细节包括了直到运行才有的引用对象(如批处理中的临时表)和运行时的参数以及局部变量。这里就不展开讲了,微软的白皮书中由于详细的介绍。

    SQLServer
运行一个并行计划,通过为每一个查询计划的并行区域派生一个DOP执行上下文,利用独立的线程在上下文中运行串行计划包含的部分。为了帮助概念的理解,图6中展示了三个执行上下文,每个颜色区分执行上下文的范围。虽然并不是明显地展示出来,但是一个Parallel
Page Supplier 还是被用来协调索引扫描,避免重复读取。

威尼斯官方网站登录 8

图6: 并行计划执行上下文

 

   
为了更具体的观察抽象概念,图7展示了并行行计数查询包含的信息,在SSMS的选项中,“Actual
Execution Plan”(实际执行计划),打开左侧扩展+。

威尼斯官方网站登录 9

图7: 并行计划行计数

   
两个图片对比,行处理的数字一个是3一个是113443。信息来自于属性窗口,通过点击操作符(或者链接线)然后按下F4,或者右键属性。右键操作符或者线,并且选择弹出菜单的属性。

   
右边的插图中我们能看到每个线程读取的行数和总行数;注意两个线程处理了相似的行数(40000左右),但是第三个线程值处理了32000行。如上所述,基于需求的架构取决于每个线程时间因素和处理器负载等等,及时是轻负载的机器也会有不平衡的现象。

   
左侧的这个图展示了三个结果结被收集在一起的过程,汇总了每个进程的结果集。它的元素是并行执行线程的数量。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章