SQL Server并行运算学习总结教程

技术准备

同前几篇一样，基于SQL Server2008R2版本，利用微软的一个更简洁的案例库(Northwind)进行解析。

一、并行运算符

在我们日常所写的T-SQL语句，并不是所有的最优执行计划都是一样的，其最优的执行计划的形成需要多方面的评估才可以，大部分根据SQL Server本身所形成的统计信息，然后对形成的多个执行计划进行评估，进而选出最优的执行方式。

在SQL Server根据库内容形成的统计信息进行评估的同时，还要参照当前运行的硬件资源，有时候它认为最优的方案可能当前硬件资源不支持，比如：内存限制、CPU限制、IO瓶颈等，所以执行计划的优劣还要依赖于底层硬件。

当SQL Server发现某个处理的数据集比较大，耗费资源比较多时，但此时硬件存在多颗CPU时，SQL Server会尝试使用并行的方法，把数据集拆分成若干个，若干个线程同时处理，来提高整体效率。

在SQL Server中可以通过如下方法，设置SQL Server可用的CPU个数

 

 

默认SQL Server会自动选择CPU个数，当然不排除某些情况下，比如高并发的生产环境中，防止SQL Server独占所有CPU，所以提供了该配置的界面。

还有一个系统参数，就是我们熟知的MAXDOP参数，也可以更改此系统参数配置，该配置也可以控制每个运算符的并行数(记住：这里是每个运算符的，而非全部的)，我们来查看该参数

 

 

这个并行运算符的设置数，指定的是每个运算符的最大并行数，所以有时候我们利用查看系统任务数的DMV视图sys.dm_os_tasks来查看，很可能看到大于并行度的线程数据量，也就是说线程数据可能超过并行度，原因就是两个运算符重新划分了数据，分配到不同的线程中。

这里如没特殊情况的话，建议采用默认设置最佳。

我们举一个分组的例子，来理解并行运算

 

 

采用并行运算出了提升性能还有如下几个优点：

不依赖于线程的数量，在运行时自动的添加或移除线程，在保证系统正常吞吐率的前提下达到一个性能最优值

能够适应倾斜和负载均衡，比如一个线程运行速度比其它线程慢，这个线程要扫描或者运行的数量会自动减少，而其它跑的快的线程会相应提高任务数，所以总的执行时间就会平稳的减少，而非一个线程阻塞整体性能。

下面我们来举个例子，详细的说明一下

并行计划一般应用于数据量比较大的表，小表采用串行的效率是最高的，所以这里我们新建一个测试的大表，然后插入部分测试数据，我们插入250000行，整体表超过6500页，脚本如下

 

--新建表，建立主键，形成聚集索引

CREATE TABLE BigTable

(

[KEY] INT,

DATA INT,

PAD CHAR(200),

CONSTRAINT [PK1] PRIMARY KEY ([KEY])

)

GO

--批量插入测试数据250000行

SET NOCOUNT ON

DECLARE @i INT

BEGIN TRAN

SET @i=0

WHILE @i<250000

BEGIN

INSERT BigTable VALUES(@i,@i,NULL)

SET @i=@i+1

IF @i%1000=0

BEGIN

COMMIT TRAN

BEGIN TRAN

END

END

COMMIT TRAN

GO

 

我们来执行一个简单查询的脚本

SELECT [KEY],[DATA]

FROM BigTable

 

 

这里对于这种查询脚本，没有任何筛选条件的情况下，没必要采用并行扫描，因为采用串行扫描的方式得到数据的速度反而比并行扫描获取的快，所以这里采用了clustered scan的方式，我们来加一个筛选条件看看

SELECT [KEY],[DATA]

FROM BigTable

WHERE DATA<1000

 

 

对于这个有筛选条件的T-SQL语句，这里SQL Server果断的采用的并行运算的方式，聚集索引也是并行扫描，因为我电脑为4个逻辑CPU(其实是2颗物理CPU，4线程)，所以这里使用的是4线程并行扫描四次表，每个线程扫描一部分数据，然后汇总。

 

 

这里总共用了4个线程，其中线程0为调度线程，负责调度所有的其它线程，所以它不执行扫描，而线程1到线程4执行了这1000行的扫描!当然这里数据量比较少，有的线程分配了0个任务，但是总得扫描次数为4次，所以这4个线程是并行的扫描了这个表。

可能上面获取的结果比较简单，有的线程任务还没有给分配满，我们来找一个相对稍复杂的语句

SELECT MIN([DATA])

FROM BigTable

 

 

这个执行计划挺简单的，我们依次从右边向左分析，依次执行为：

4个并行聚集索引扫描——>4个线程并行获取出前当前线程的最小数——>执行4个最小数汇总——>执行流聚合获取出4个数中的最小值——>输出结果项。

 

 

然后4个线程，每个线程一个流聚合获取当前线程的最小数

 

 

然后，将这个四个最小值经过下一个“并行度”的运算符汇聚成一个表

 

 

然后下一个就是流聚合，从这个4行数据中获取出最小值，进行输出，关于流聚合我们上一篇文章中已经介绍

 

 

以上就一个一个标准的多线程并行运算的过程。

上面的过程中，因为我们使用的并行聚集索引扫描数据，4个线程基本上是平均分摊了任务量，也就是说每个线程扫描的数据量基本相等，下面我们将一个线程使其处于忙碌状态，看看SQL Server会不会将任务动态的平摊到其它几个不忙碌的线程上。

我们在来添加一个大数据量表，脚本如下

SELECT [KEY],[DATA],[PAD]

INTO BigTable2

FROM BigTable

我们来写一个大量语句的查询，使其占用一个线程，并且我们这里强制指定只用一个线程运行

SELECT MIN(B1.[KEY]+B2.[KEY])

FROM BigTable B1 CROSS JOIN BigTable2 B2

OPTION(MAXDOP 1)

以上代码想跑出结果，就我这个电脑配置估计少说五分钟以上，并且我们还强行串行运算，速度可想而知，

我们接着执行上面的获取最小值的语句，查看执行计划

SELECT MIN([DATA])

FROM BigTable

我们在执行计划中，查看到了聚集索引扫描的线程数量

 

 

可以看到，线程1已经数量减少了近四分之的数据，并且从线程1到线程4，所扫描的数据量是依次增加的。

我们上面的语句很明确的指定了MAXDOP为1，理论上讲只可能会影响一个线程，为什么这几个线程都影响呢?其实这个原因很简单，我的电脑是物理CPU只有两核，所谓的线程数只是超线程，所以非传统意义上的真正的4核数，所以线程之间是互相影响的。

我们来看一个并行连接操作的例子，我们查看并行嵌套循环是怎样利用资源的

SELECT B1.[KEY],B1.DATA,B2.DATA

FROM BigTable B1 JOIN BigTable2 B2

ON B1.[KEY]=B2.[KEY]

WHERE B1.DATA<100

上面的语句中，我们在BigTable中Key列存在聚集索引，而查询条件中DATA列不存在，所以这里肯定为聚集索引扫描，对数据进行查找

来看执行计划

 

 

我们依次来分析这个流程，结合文本的执行计划分析更为准确，从右边依次向左分析

 

 

第一步，就是利用全表通过聚集索引扫描获取出数据，因为这里采用的并行的聚集索引扫描，我们来看并行的线程数和扫描数

 

 

四个线程扫描，这里线程3获取出数据100行数据。

然后将这100行数据，重新分配线程，这里每个线程平均分配到25行数据

 

 

到此，我们要获取的结果已经均分成4个线程共同执行，每个线程分配了25行数据，下一步就是交给嵌套循环连接了，因为我们上面的语句中需要从BigTable2中获取数据行，所以这里选择了嵌套循环，依次扫描BigTable2获取数据。

关于嵌套循环连接运算符，可以参照我的第二篇文章。

 

 

我们知道这是外表的循环数，也就是说这里会有4个线程并行执行嵌套循环。如果每个线程均分25行，数据那么内部表就要执行

4*25=100次。

 

 

然后，执行完，嵌套扫描获取结果后，下一步就是，将各个线程执行的结果通过并行运算符汇总，然后输出

 

 

上述过程就是一个并行嵌套循环的执行流程。充分利用了四核的硬件资源。

参考文献

微软联机丛书逻辑运算符和物理运算符引用

参照书籍《SQL.Server.2005.技术内幕》系列

结语

此篇文章先到此吧，文章短一点，便于理解掌握，后续关于并行操作还有一部分内容，后续文章补充吧，本篇主要介绍了查询计划中的并行运算符，下一篇我们接着补充一部分SQL Server中的并行运算，然后分析下我们日常所写的增删改这些操作符的优化项，有兴趣可提前关注，关于SQL Server性能调优的内容涉及面很广，后续文章中依次展开分析。

有问题可以留言或者私信，随时恭候有兴趣的童鞋加入SQL SERVER的深入研究。共同学习，一起进步。

 

SQL Server并行运算学习总结教程二

技术准备

同前几篇一样，基于SQL Server2008R2版本，利用微软的一个更简洁的案例库(Northwind)进行解析。

内容

文章开始前，我们先来回顾上一篇中介绍的并行运算，来看文章最后介绍的并行运算语句：

SELECT B1.[KEY],B1.DATA,B2.DATA
FROM BigTable B1 JOIN BigTable2 B2
ON B1.[KEY]=B2.[KEY]
WHERE B1.DATA<100

 

 

上面是详细的执行计划，从右边依次向左执行，上图中有一个地方很有意思，就是在聚集索引扫描后获取的数据，又重新了使用了一次重新分配任务的过程

(Repartition Streams)，就是上图的将获取的100行数据重新分配到并行的各个线程中。

其实这里本可以直接将索引扫描出来的100行数据直接扔到嵌套循环中执行。它这里又重新分配任务的目的就是为了后面嵌套循环的并行执行，最大限度的利用硬件资源!

但这样做又带了另一个弊端就是执行完嵌套循环之后，需要将结果重新汇总，就是下面的(Gather Sreams)运算符。

我们来看看该语句如果不并行的执行计划

SELECT B1.[KEY],B1.DATA,B2.DATA
FROM BigTable B1 JOIN BigTable2 B2
ON B1.[KEY]=B2.[KEY]
WHERE B1.DATA<100
option(maxdop 1)

 

 

这才是正宗的串行执行计划。

和上面的并行执行计划相比较，你会发现SQL Server充分利用硬件资源而形成的并行计划，是不是很帅!

如果还没感觉到SQL Server并行执行计划的魅力，我们再来举个例子，看如下语句

 

 

 

SELECT BIG_TOP.[KEY],BIG_TOP.DATA,B2.DATA
FROM
(
SELECT TOP 100 B.[KEY],B.DATA
FROM BigTable B
ORDER BY DATA
) BIG_TOP,
BigTable2 B2
WHERE BIG_TOP.[KEY]=B2.[KEY]

 

先来分析下上面的语句，这个语句我们在外表中加入了TOP 100.....ORDER BY DATA关键字，这个关键字是很有意思....

因为我们知道这个语句是获取根据DATA关键字排序，然后获取出前100行的意思...

1、根据DATA排序.....丫的多线程我看你怎么排序?每个线程排列自己的?那你排列完了在汇聚在一起...那岂不是还得重新排序!!

2、获取前100行数据，丫多线程怎么获取?假如我4个线程扫描每个线程获取25条数据?这样出来的结果对嘛?

3、我们的目标是让外表和上面的100行数据还要并行嵌套循环连接，因为这样才能充分利用资源，这个怎么实现呢?

上面的这些问题，我们来看强大的SQL Server将为我们怎样生成强悍的执行计划

 

 

上面的执行计划已经解决了我们以上所述的三个问题，我们依次来分析下，这几个问题的解决方法

第一个问题，关于并列排序问题

首选根据聚集索引扫描的方式采用并列的方式从表中获取出数据

 

 

然后，在并行的根据各个线程中的数据进行排序，获取前几列值，我们知道，我们的目标获取的是前100行，它这里获取的方式是冗余获取，也就是说每个线程各自排序自己的数据

然后获取出前面的数据，通过循环赛的方式进行交换，获取出一部分数据

 

 

第二个问题，关于并列获取前100行数据问题

我们知道要想获取前100行数据，就必须将各个线程的数据汇总到一起，然后通过比较获取前100行数据，这是必须的，于是在这一步里SQL Server又的重新将数据汇总到一起

 

 

第三个问题，下一步需要将这100行数据和外表进行连接，获取出结果，这里面采用的嵌套循环连接的方式，为了充分利用资源，提升性能，SQL Server又不得不将这100行数据均分到各个线程中去执行，所以这里又采用了一个拆分任务的运算符分发流(Distribute Sreams)任务

 

 

所以经过此步骤又将系统的硬件资源充分利用起来了，然后下一步同样就是讲过嵌套循环进行关联获取结果，然后再重新将结果汇总，然后输出

 

 

我们可以看到上面的一个流程，SQLServer经过了：先拆分(并行扫描)——》再并行(获取TOP 100....)——》再拆分(为了并行嵌套循环)——》再并行(为了合并结果)

总之，SQL Server在运行语句的时候，经过各种评估之后，利用各种拆分、各种汇总，目的就是充分的利用硬件资源，达到一个性能最优化的方式!这就是SQL Server并行运算的精髓。

当然凡事有利就有弊，我们通过这条语句来对比一下串行和并行在SQL Server中的优劣项

一下是串行执行计划：

 

SELECT BIG_TOP.[KEY],BIG_TOP.DATA,B2.DATAz
FROM
(
SELECT TOP 100 B.[KEY],B.DATA
FROM BigTable B
ORDER BY DATA
) BIG_TOP,
BigTable2 B2
WHERE BIG_TOP.[KEY]=B2.[KEY]
option(maxdop 1)

 

 

 

串行执行的执行计划：简单、大气、没有复杂的各种拆分、各种汇总及并行。

我们来比较下两者的不同项，先比较一个T-SQL语句的各个参数值：

 

 

前者是串行、后者是并行

串行编译耗费CPU：2、并行编译耗费CPU:10

串行编译耗费内存：184、并行编译耗费内存：208

串行编译耗时：2、并行编译耗时：81

上面是采取并行的缺点：1、更消耗CPU、2、编译更消耗内存、3、编译时间更久

我们来看一下并行的优点：

上图中串行内存使用(1024)，并行内存(448)

优点就是：并行执行消耗内存更小

当然还有一个更重要的优点：执行速度更快!

 

 

采用并行的执行方式，执行时间从218毫秒提升到187毫秒!数据量少，我机器性能差所以提升不明显!

在并行运算执行过程中，还有一种运算符经常遇到：位图运算符，这里我们顺带也介绍一下

举个例子：

SELECT B1.[KEY],B1.DATA,B2.[KEY]
FROM BigTable B1 JOIN BigTable2 B2
ON B1.DATA=B2.DATA
WHERE B1.[KEY]<10000

这里我们获取大表中Key列小于10000行的数据。

 

 

上述的执行语句，就引入了位图计算。

其实位图计算的目标很简单：提前过滤，因为我们的语句中要求获取的结果项比较多10000行数据，在我们后面的线程中采用的并行扫描的方式获取出数据。由于数据量比较多的原因，各个线程在执行的过程中获取完数据的时间不同，为了避免因某个线程执行速度缓慢，导致整体堵塞，索引引入了位图运算，先将获取出来的部分结果过滤输出到前面的哈希匹配，完整执行。

关于位图运算符更多详细可参照：http://msdn.microsoft.com/zh-cn/library/bb510541

结语

此篇文章先到此吧，本篇主要是上一篇并行运算的一个延续，两篇文章介绍了SQL Server中关于并行运算的原理和使用方式，关于并行运算这块就到这吧，下一篇我们补充SQL Server中关于索引的利用方式和动态索引的内容，关于索引我相信很多了解数据库产品的人都熟悉，但是SQL Server中一些语句利用索引的方式可能还不清楚，我们下一篇分析这块，借此了解索引的建立方式和优化技巧，有兴趣可提前关注，关于SQL Server性能调优的内容涉及面很广，后续文章中依次展开分析。

有问题可以留言或者私信，随时恭候有兴趣的童鞋加入SQL SERVER的深入研究。共同学习，一起进步。