SSIS工程师为您揭秘数据流

我上个月有幸参加了在西雅图召开的PASS&＃xff08;Professional Association for SQL Server&＃xff09;峰会。我的同事Matt Masson做了个关于SQL Server 数据集成服务&＃xff08;Integration Services&＃xff0c;SSIS&＃xff09;的讲座&＃xff08;下载&＃xff09;&＃xff0c;现场非常火爆&＃xff0c;讲完后他被听众围住了个把小时。他的题目是Maximize Your SSIS Investment with Tuning Tricks and Tips&＃xff0c;主要关于提升数据集成包&＃xff08;package&＃xff09;的性能。 他讲了四部分&＃xff0c;其中第二部分深入浅出地介绍了SSIS数据流&＃xff08;Data flow&＃xff09;。我估计我国的用户会特别感兴趣这一块&＃xff0c;因此在这里分享给你 :-)

数据流一瞥

SSIS的引擎&＃xff08;engine&＃xff09;是内存式&＃xff08;in-memory&＃xff09;的&＃xff1a;从源&＃xff08;source&＃xff09;读数据&＃xff0c;在内存中执行package&＃xff0c;再把结果写到端&＃xff08;destination&＃xff09;。尽量不碰外存是其高性能的原因之一。很多以前使用ETL&＃xff08;Extract-Transform-Load&＃xff09;工具的人需要对此调整观念&＃xff1a;那些工具先把数据加载到数据库里再做SQL转换&＃xff0c;其实是ELT&＃xff08;Extract-Load-Transform&＃xff09;。Matt讲了个很有趣的案例&＃xff1a;有位客户的package以前运行只要几分钟&＃xff0c;自从服务器升级到新机器后竟然更慢了&＃xff0c;要花一个小时。那个package很简单&＃xff0c;只是源到端拷贝&＃xff0c;中间没有转换&＃xff08;transform&＃xff09;&＃xff0c;因此客户很生气。Matt他们急忙去会诊&＃xff0c;才发现这个package的源和端以前就在它所运行的那台机器上&＃xff0c;在美国&＃xff1b; 后来升级了的机器在中国&＃xff0c;源和端都跑到了中国来&＃xff0c;而package还是在美国那台机器上运行。结果这个package所做的就是从中国读出若干GB的数据到美国的内存&＃xff0c;再拷回中国……Matt说&＃xff0c;类似的客户问题其实并不少见。希望你读本文以后能避免这种设计了 :-)

SSIS在设计时&＃xff08;design time&＃xff09;阶段就确定了数据流的元数据&＃xff08;metadata&＃xff09;。它在运行之前就精确知道了运行时的列将有多宽&＃xff0c;转换需要多少内存&＃xff0c;等等。

数据流水线&＃xff08;pipeline&＃xff09;

当数据流启动时&＃xff0c;源就开始把一行行数据填到一个类似桶的缓存&＃xff08;buffer&＃xff09;中。源根本不知道下游是什么。一旦缓存满了&＃xff0c;桶就随着流水线流到下游组件&＃xff08;component&＃xff09;上&＃xff0c;同时引擎抓一个新的空缓存过来给源。源根本不知道这一切&＃xff0c;它只是不断地填桶。有时源填了太多的桶&＃xff0c;转换和端都来不及应付了&＃xff1b;此时引擎会启动反压&＃xff08;backpressure&＃xff09;机制&＃xff0c;让源睡眠。等到流水线又有空间之后&＃xff0c;源被唤醒继续填桶。其实在实现上&＃xff0c;源甚至都不知道自己被催眠过&＃xff08;好可怜&＃xff09;……直到所有源数据行都发光了&＃xff0c;源才在最后一个缓存上贴个“行集末&＃xff08;End Of Rowset&＃xff09;”的标签&＃xff0c;把它发出去&＃xff0c;告诉下游组件再没有新数据了。

转换与缓存拷贝

SSIS的高性能有部分归功于它在内存使用上比较聪明。在缓存之间拷贝数据是耗时的&＃xff0c;因此引擎会尽量减少缓存拷贝。按照缓存使用的不同&＃xff0c;可将众多转换组件分为三类。

第一类是同步&＃xff08;synchronous&＃xff09;转换&＃xff0c;它们一般逐行对数据做就地修改&＃xff0c;从不拷贝缓存。它们有可能增加新行&＃xff0c;比如数据转换&＃xff08;Data Convert&＃xff09;和派生列&＃xff08;Derived Column&＃xff09;转换&＃xff0c;而仍然是同步的&＃xff1a;引擎事先确定了新列将加在哪里&＃xff0c;提前就在缓存里加了空列&＃xff0c;只是上游组件看不到这些空列罢了。异步&＃xff08;asynchronous&＃xff09;转换会动态创建新缓存&＃xff0c;包括两小类: 部分阻塞&＃xff08;Partially Blocking&＃xff09;转换&＃xff0c;一伺新缓存满了就把它输出&＃xff0c;比如联合全体&＃xff08;Union All&＃xff09;组件接受多个输入流&＃xff0c;一旦从各输入得到了足够多的行就把它输入到一个新缓存里。由于要拷贝数据&＃xff0c;这种转换比同步转换慢&＃xff1b;但和全阻塞&＃xff08;Blocking&＃xff09;转换相比就好多了。排序&＃xff08;Sort&＃xff09;、聚集&＃xff08;Aggregate&＃xff09;这些全阻塞转换在接收完所有输入行之前&＃xff0c;是不会输出一行的。这是由运算本身的特点决定的&＃xff1a;不到看到所有数据&＃xff0c;是无法确定哪个是最小值的。

因此&＃xff0c;在使用全阻塞转换时要格外审慎&＃xff0c;尤其是数据量很大时。一旦内存用完&＃xff0c;缓存被置换到硬盘上&＃xff0c;性能就完了。要想提高数据流性能&＃xff0c;最好设法从package中去除全阻塞转换。

线程机制

要理解数据流&＃xff0c;还需要了解其线程机制。流水线在运行时被分成若干执行树&＃xff08;Execution Trees&＃xff09;。每个创建新缓存的组件就是一棵新执行树的起点&＃xff1b;因此起点要么是个数据源&＃xff0c;要么是个异步转换。下图的数据流中有5棵执行树&＃xff0c;如蓝箭头所示。引擎限定了每棵树中最多工作的缓存数&＃xff08;目前定为五个&＃xff09;&＃xff0c;一旦更多缓存进来&＃xff0c;就启动反压。注意到多播&＃xff08;Multicast&＃xff09;和条件分割&＃xff08;Conditional Split&＃xff09;转换都是同步的&＃xff0c;它们在分割数据流时并不创建新缓存&＃xff1b;引擎只是创建了一些能映射到同一块内存的虚拟缓存。所以即使你多播20次也不会看到内存消耗增多。

此图修改自Matt的幻灯片

值得一提的是&＃xff0c;数据流线程调度在SQL 2008版本中被改进了&＃xff1a;在2005版中&＃xff0c;每棵树只分到一个线程执行&＃xff0c;其问题是对于图中右边那种较长的树&＃xff0c;虽然树里都是一序列同步转换&＃xff0c;但每次只能在树中移动一个缓存&＃xff0c;执行完它之后才能开始执行下一个缓存。很多人为了打碎较长的执行树&＃xff0c;就在中间插入一个单输入的联合全体&＃xff08;Union All&＃xff09;组件&＃xff0c;由于它是异步的&＃xff0c;就能间接引入另一个线程。而现在&＃xff0c;我们在2008版中改为让每个缓存上都有一个线程在执行&＃xff0c;这样一棵树中就可以有多个线程在执行。可能第一个线程先把一个缓存进行了三个转换&＃xff0c; 然后第二个线程捡起这个缓存继续向下游转换&＃xff0c;同时第一个线程开始捡起下一个缓存。这样就再也不需要上述间接的方法了。

看完以上揭秘&＃xff0c;你有收获吗&＃xff1f;