兴义信息网
健康
当前位置:首页 > 健康

SQL Server的SSIS最佳实践-优化数据表

发布时间:2019-09-13 19:17:02 编辑:笔名

本文将继续讨论SQL Server目标适配器,异步传输,DefaultBufferMaxSize和DefaultBufferMaxRows,BufferTempStoragePath和BLOBTempStoragePath,以及DelayValidation属性。

第一篇:SQL Server集成服务最佳实践:语句优化

最佳实践5:SQL Server目标适配器

如果你的目标是本地数据库,建议你使用SQL Server目标适配器,它提供了与Bulk Insert任务类似的数据插入性能,并提供了某些额外增强。使用SQL Server目标适配器,在写入目标表之前你可以对数据做转换操作,但Bulk Insert任务是不行的,除了OLEDB目标适配器可用的选项外,SQL Server目标适配器还有更多选项,如图1所示。例如,你可以指定是否触发目标表上的插入触发器,默认情况下,这个选项设置为“false”,意味着不会触发目标表上的触发器,如果启用触发器,将会引起性能下降,但为了强制实施数据和业务规则,触发器是不可避免的。另外还有选项可以指定第一次和最后一次载入的数量,指定错误的最大数量,以及指定插入列的顺序。

图1 SQL Server目标适配器选项

图2 OLEDB目标适配器选项

如果你的SQL Server数据库在远程服务器上,就不能使用SQL Server目标适配器,这个时候只能使用OLEDB目标适配器。此外,如果目标数据库可能会从本地改为远程,或从一个数据库实例改为另一个数据库实例,也最好使用OLEDB目标适配器,以减小未来可能的改变。

最佳实践6:尽可能避免异步转换

在讨论不同种类的转换对性能的影响之前,我们首先简要地回顾一下SSIS的工作原理,SSIS运行时引擎执行包,当SSIS运行时引擎遇到数据流任务时,它会将数据流任务交给数据流管道引擎,数据流管道引擎会将数据流任务拆分成多个执行树,可能会同时执行两个或多个执行树以提高并发处理能力和性能。你可能还不知道什么是执行树,下面就是答案。

正如其名,执行树与树的结构类似,每个执行树有一套缓冲区,其范围与执行树紧密相关,每个执行树也分配有一个操作系统线程,与缓冲区不同,线程可能与其它执行树是共享的,即一个线程可以执行一个或多个执行树。在SSIS 2008中,将数据流任务拆分成执行树的进程已经得到了极大的增强,它可以创建一个执行路径和子路径,以便你的包可以利用高端多处理器系统。

同步转换获得一个记录,经过处理,然后将其传给其它转换进程或下一个目标,记录的处理不依赖于其它传入的行,因为同步转换输出的记录数和输入的记录数是相同的,它不需要新的缓冲区(处理是在相同的入站缓冲区中完成的),因为就这样已经很快了。例如,在Derived列转换过程中,在每个入站行增加一列,但不会增加输出的记录数。

与同步转换有点不一样,异步转换输出的记录数和输入的记录数可能不一样,需要创建新的缓冲区,因为一个输出依赖于一条或多条记录,也被称作阻塞转换。例如,排序转换就是一个不折不扣的阻塞转换,它要求所有入站的行在处理之前必须抵达才行。

正如上面所讨论的,异步转换需要额外的缓冲区用于输出,不会重复利用入站输入缓冲区,在处理之前它也会等待所有的入站行抵达,这也是异步转换执行得慢的原因,因此要尽可能避免这种情况。例如,如果不是有排序转换,你可以ORDER BY子句从源表本身获得已经排好序的结果。

最佳实践7:DefaultBufferMaxSize和DefaultBufferMaxRows

正如我在最佳实践六中谈到的,执行树为入站数据排序和执行转换创建一个缓冲区,那么要创建多大的缓冲区合适呢?单个缓冲区有多少行数据进入呢?它对性能有何影响呢?

缓冲区的大小依赖于有多少行数据进入缓冲区,有多少行数据进入缓冲区又依赖于其它一些因素。首先要考虑的是评估每一行的大小,它等于所有入站行包含的所有列的最大大小,其次要考虑的是数据流任务的DefaultBufferMaxSize属性,它指定了一个缓冲区的默认最大大小,默认值是10MB,它的上下限是由SSIS的两个内部属性限制的,分别是MaxBufferSize(100MB)和MinBufferSize(64KB),意味着一个缓冲区的大小范围是64KB到100MB,第三个因素是DefaultBufferMaxRows,它也是数据流任务的一个属性,它指定了进入缓冲区的默认行数,默认值是10000。

虽然SSIS提供了这么多的属性可以设置一个合适的缓冲区大小,如果大小超出了DefaultBufferMaxSize的值,它会减少进入缓冲区的记录行数。为了提高缓冲区的性能,你可以做两件事情,首先从源中移除不需要的列,并为每一列设置正确的数据类型,特别是你的源是一个平面文件时,这样可以让缓冲区尽可能容纳更多的记录行,其次,如果你的系统有充足的内存,你可以通过调整这些属性,最后创建少量的大缓冲区,这样会提升性能。注意,如果你将这些属性的值修改到某个分页开始的临界值,会对性能产生不利的影响,因此在设置这些属性之前,首先应在你的环境进行全面的测试,最终找到一个合适的值。

你可以开启BufferSizeTuning事件的日志,这样就可以看到进入缓冲区的行数,你也可以监视“Buffers spooled”性能计数器查看SSIS是否开始了分页。

最佳实践8:BufferTempStoragePath和BLOBTempStoragePath

如果内存资源不够,Windows会触发一个内存过低的通知事件,内存溢出、内存压力、输入记录,除了BLOB,SSIS会将它们输出到文件系统,文件系统的位置就是由数据流任务的BufferTempStoragePath属性设置的,默认是空的,在这种情况下,输出位置基于TEMP/TMP系统变量指定的位置。

同样,SSIS在将BLOB数据发到目标之前,可能会将其先写入到文件系统,因为BLOB数据通常非常大,SSIS缓冲区中存储不下,输出的位置是有数据流任务的BLOBTempStoragePath属性设置的,默认是空的,在这种情况下,输出位置也是基于TEMP/TMP系统变量的,如果你不为这些属性指定具体的值,TEMP和TMP系统变量的值将会被当做输出的目标,如果你开启了数据流任务PipelineInitialization事件的日志,相同的信息会被记录到日志文件中,如:

User:PipelineInitialization,ARSHADALI-LAP,FAREASTarali,Data Flow Task,,,10/11/20091:38:10AM,10/11/20091:38:10AM,0,0x,No temporaryBLOB data storage locations were provided. The buffer manager will consider the directories inthe TEMPandTMP environment variables.

现在最重要的就是改变BufferTempStoragePath和BLOBTempStoragePath的默认值,最好是将它们设为不同的磁盘路径,这样可以提高I/O效率,从而提升整体性能。

最佳实践9:好好利用DelayValidation属性

SSIS使用验证确定包在运行时是否会失败,它使用两种类型的验证,第一种是包验证,在开始执行包之前,验证包及其包含的所有组件,第二种是组件验证,一开始就验证包中的所有组件。

我们假设一个场景,包中的第一个组件创建一个对象,如一个临时表,包中的第二个组件将引用这个临时表,在包的验证过程中,第一个组件还没来得及执行,因此临时表也还没创建好,在验证第二个组件时最终导致包验证失败。SSIS会抛出一个验证异常,并不会启动包的执行,那么你将如何处置这种场景中的包?

为了解决这种场景存在的问题,每个组件都有一个DelayValidation属性,默认值为“flase”,如果你将其设为“true”,所有验证都会忽略,在包执行过程中,只会在组件级验证组件。

孩子脸色发黄需要查什么
有血栓怎么治疗
产后不排气用四磨汤
孩子发烧怎么办