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

　　最佳实践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事件的日志，相同的信息会被记录到日志文件中，如：

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

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

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

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

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