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

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

相关文章推荐

SSIS常用的包—数据流任务

数据流任务如果你对 SQL Server 2000 DTS比较熟悉,对这种数据流任务不会有了解,因为同其他任务比较它是一种很特殊的任务。主要说明数据怎样写入系统。SQL Server 2005中最大...
  • plean
  • plean
  • 2011-01-31 10:48
  • 702

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

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

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

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

首席工程师揭秘:LinkedIn大数据后台是如何运作的

Jay Kreps是来自LinkedIn的首席工程师,他表示日志几乎在计算机产生的时候就存在,除了可用在分布式计算或者抽象分布式计算模型内部之外,还有广泛的用途。本文中他讲述的日志的原理和通过把日志用...

SSIS操作MSSQL的千万级数据导入到Oracle数据库

前提:服务器配置8G内存,安装Oralce 11G数据库,MSSQL2008 R2数据库(服务没启动) 客户机配置8G内存,安装MSSQL2008 R2数据库,数据从客户机导入到服务器。SSIS在服...

SSIS入门教程

  • 2015-09-11 15:06
  • 1.09MB
  • 下载

在SSIS 2012中使用CDC(数据变更捕获)

最新项目稍有空隙,开始研究SQL Server 2012和2014的一些BI特性,参照(Matt)的一个示例,我们开始体验SSIS中的CDC(Change Data Capture,变更数据捕获)。 ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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