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

更新时间:2019-08-26    来源:班主任工作总结    手机版     字体:

【www.bbyears.com--班主任工作总结】

技术准备

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

一、并行运算符

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

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

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

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

 

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

 

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

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

 

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

 

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

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

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

 

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

 

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

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

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

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

并行计划一般应用于数据量比较大的表,小表采用串行的效率是最高的,所以这里我们新建一个测试的大表,然后插入部分测试数据,我们插入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

 

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

 

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

SELECT [KEY],[DATA]

FROM BigTable

WHERE DATA<1000

 

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

 

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

 

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

 

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

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

SELECT MIN([DATA])

FROM BigTable

 

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

 

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

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

上面的过程中,因为我们使用的并行聚集索引扫描数据,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

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

 

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

 

可以看到,线程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列不存在,所以这里肯定为聚集索引扫描,对数据进行查找

来看执行计划

 

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

 

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

 

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

 

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

 

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

 

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

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

 

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

 

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

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

 

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

 

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

4*25=100次。

 

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

 

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

 

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

 

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

参考文献

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

参照书籍《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的深入研究。共同学习,一起进步。

本文来源:http://www.bbyears.com/banzhurengongzuo/64329.html

热门标签

更多>>

本类排行