SSIS工程师为您揭秘数据流

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

数据流一瞥

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

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

数据流水线（pipeline）

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

转换与缓存拷贝

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

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

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

线程机制

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

此图修改自Matt的幻灯片

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

看完以上揭秘，你有收获吗？

杨珂，SSIS软件开发工程师

Comments

• Anonymous
  December 21, 2009
  代码: 0xC0202009     源: DIMLOGCONTENT 查找 [1074]     说明: SSIS 错误代码 DTS_E_OLEDBERROR。出现 OLE DB 错误。错误代码: 0x80040E21。  已获得 OLE DB 记录。源:“Microsoft SQL Native Client” Hresult: 0x80040E21 说明:“输入参数值无效。请检查状态值，以获得详细信息。”。  错误结束  错误: 2009-12-22 06:33:17.56     代码: 0xC0208253     源: DIMLOGCONTENT 查找 [1074]     说明: 提取参数化行集时发生 OLE DB 错误。请检查 SQLCommand 和 SqlCommandParam 属性。 在包里执行查找组件的时候出现这个错误,知道是什么原因吗?

• Anonymous
  December 25, 2009
  nice~ it helps，thanks

• Anonymous
  December 28, 2009
  rominzhu, 你好! 你的SQL Server是什么版本? Error Log文件夹下有没有minidump给出详细错误? 能否简化你的package, 定位出问题的输入参数(如 三值tinyint, datetime等)? 建议把这个问题post到SQL Server中/英文论坛上 http://social.microsoft.com/forums/zh-CN/sqlserverzhchs/threads/ http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/threads/ 祝好.