SSIS工程师为您揭秘数据流

转载 2011年01月12日 10:00:00

我上个月有幸参加了在西雅图召开的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版中改为让每个缓存上都有一个线程在执行,这样一棵树中就可以有多个线程在执行。可能第一个线程先把一个缓存进行了三个转换, 然后第二个线程捡起这个缓存继续向下游转换,同时第一个线程开始捡起下一个缓存。这样就再也不需要上述间接的方法了。

看完以上揭秘,你有收获吗?

3.23、BI之SSIS之数据流目标(记录集目标)

BI之SSIS之数据流目标(记录集目标)     1          新建SSIS包   2          连接管理器-》新建OLEDB连接“LocalHost.AdventureWo...
  • soldierluo
  • soldierluo
  • 2015年10月22日 09:29
  • 909

SSIS数据流中数据源发生长度被截断的问题

虽然是警告,但是运行时还是报错。检查数据库中列的长度也是符合要求的。   右键数据源,点击显示高级编辑器     将Length改为相应的长度就解决了...
  • nieyan9900
  • nieyan9900
  • 2013年11月15日 10:24
  • 557

3.22、BI之SSIS之数据流目标(excel目标)

BI之SSIS值数据流目标(excel目标)     1          新建SSIS包   2          控制流-》拖入“数据流任务”   3          连接管理器-...
  • soldierluo
  • soldierluo
  • 2015年10月22日 09:29
  • 718

SSIS - 无法保存数据流工具,无法保存包

新建一个 Integration Services Project ,添加  数据流任务  控件后,点击保存,提示无法保存保存数据流工具,无法保存包。 按网上教程,改配置文件,找不到和网上一样的需要...
  • qianqianstd
  • qianqianstd
  • 2015年11月29日 00:16
  • 983

SSIS_数据流转换(查找)

查找转换通过联接输入列中的数据和引用数据集中的列来执行查找。是完全匹配查找。 本章功能:在源表中查找与字表能关联的所有源表记录。 1. 准备数据。 源表tb_Main: ...
  • kk185800961
  • kk185800961
  • 2013年10月07日 19:01
  • 5023

SSIS----改进数据流的性能

可以配置数据流任务的下列属性,这些属性都会对性能产生影响: 为缓冲区数据(BufferTempStoragePath 属性)和包含二进制大型对象 (BLOB) 数据的列(BLOBTempStor...
  • liguoming05
  • liguoming05
  • 2013年10月28日 16:30
  • 1535

SSIS中的容器和数据流—简单介绍

容器 容器是SSIS为一个或多个任务提供一种结构的对象。例如可以执行一个循环直到到达边界条件,或者将一系列的task按逻辑组织在一起。一个容器也可以包含其他的容器。容器和任务(task)一样也放在C...
  • web_go_run
  • web_go_run
  • 2015年10月23日 16:08
  • 869

SSIS_数据流转换(Union All&合并联接&合并)

Union All : 与sql语言 Union All 一样,不用排序,上下合并多个表。Union All转换 替代合并转换:输入输出无需排序,合并超过两个表 合并联接 : 有左连接、内连接、完全连...
  • kk185800961
  • kk185800961
  • 2013年10月07日 13:22
  • 5445

3.12、BI之SSIS之数据流(源-目标-连接)

BI之SSIS之数据流(源-目标-连接)   1          源-目标-连接 1.1         源,数据流源,为数据的来源,它可以是各种文件、数据库等等 1.2         目...
  • soldierluo
  • soldierluo
  • 2015年10月21日 11:37
  • 782

错误 0xc0202009: 数据流任务 1: SSIS 错误代码 DTS_E_OLEDBERROR。出现 OLE DB 错误。

原来是一个varchar字段出出现了“和‘等特殊字符。 这个在insert语句中没有问题,但是使用导入导出会报错。 最后要注意的是,导入导出使用的是BulkInsert 方式,每次可能读取一大段(...
  • djd1234567
  • djd1234567
  • 2015年05月15日 11:17
  • 9745
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SSIS工程师为您揭秘数据流
举报原因:
原因补充:

(最多只允许输入30个字)