SSIS最佳实践：SQL Server提升执行性能

原文出自【风信网】，转载请保留原文链接： http://www.ithov.com/server/94832.shtml

 

SQL Server集成服务(SQL Server Integration Services，SSIS)在其前辈DTS(Data Transformation Services，数据转换服务)的基础上进步了不少，从可用性、性能和并行等方面来说，它已经成长为一个企业级ETL(Extraction, Transformation and Loading，抽取、转换和加载)产品，除了是一个ETL产品外，它也提供了各种内置任务来管理SQL Server实例。虽然SSIS的内部架构已经被设计为提供极好的性能和并行处理能力，但如果遵循最佳实践，其性能还可进一步优化，在本系列文章中，我将讨论SSIS的最佳实践，我会将我过去几年学习和使用SSIS的经验与大家分享。

　　正如上面所说的，SSIS是DTS(SQL Server 7/2000)的替代产品，如果你曾经使用过DTS，你会发现SSIS包和DTS包非常类似，但本质上已经发生了很大的变化，SSIS不是DTS的增强版本，而是从零开始构建的一个新产品，与DTS相比，SSIS提供了更好的性能和并行处理能力，并克服了DTS的许多限制。

　　SSIS 2008进一步增强了内部数据流管道引擎，提供了更好的性能，你可能已经看到了SSIS 2008创造的一个ETL世界记录，那就是在半小时内加载1TB数据。

　　SSIS的最大好处是它是SQL Server的一个组件，它可以随SQL Server安装而免费获得，不再需要为它购买额外的许可，BI开发人员、数据库开发人员和DBA都可以使用它转换数据。

　　最佳实践1：抽取大批量数据

　　最近我们从一个有3亿条记录的大表中抽取数据，起初，当SSIS包启动时一切正常，数据如预期的那样在转换，但性能开始逐渐下降，数据转换 速率直线下降。通过分析，我们发现目标表有一个主聚集键和两个非聚集键，因为大量数据插入这个表，导致其索引碎片水平达到了85%-90%。我们使用索引在线重建特性重建/重组索引，但在加载期间，每过15-20分钟，索引碎片水平又回到90%，最终数据转换和并行执行的在线索引重建过程花了12-13个小时，远远超出了我们的预期。

　　我们想出了一个办法，当转换开始前，我们将目标表的索引全部删掉，转换结束后又再重新创建索引，通过这样处理后，整个转换过程花了3-4小时，完全符合我们的预期。

　　整个过程我画在下面的图中了。因此我建议如果可能，在插入数据前，删掉目标表上的所有索引，特别是插入大数据量时。

　　

　　转换数据前，删除目标表上的所有索引，转换完后，再重建索引

　　最佳实践2：避免使用select *

　　SSIS的数据流任务(Data Flow Task，DFT)使用一个缓冲区作为数据传输和转换的中转站，当数据从源表传输到目标表时，数据首先进入缓冲区，数据转换是在缓冲区中完成的，转换完毕后才会写入到目标表中。

　　缓冲区的大小受服务器硬件本身限制，它要估算行的大小，行大小是通过一行中所有列大小的最大值求和得出的，因此列数越多，意味着进入缓冲区的行就会越少，对缓冲区的需求就会越多，性能就会下降。因此转换时最好明确指定需要转换到目标表的列。即使你需要源表中的所有列，你也应该在select语句中明确指定列的名称，如果你使用select *，它会绕到源表收集列的元数据，SQL语句执行时间自然就会长一些。

　　如果你将目标表不需要的列也做了转换，SSIS将会弹出警告提示信息，如：

[SSIS.Pipeline] Warning: The output column "SalariedFlag" (64) on output "OLE DB Source Output" (11) and component "OLE DB Source" (1) is not subsequently used in the Data Flow task.�0�2

　　Removing this unused output column can increase Data Flow task performance.�0�2

　　[SSIS.Pipeline] Warning: The output column "CurrentFlag" (73) on output "OLE DB Source Output" (11) and component "OLE DB Source" (1) is not subsequently used in the Data Flow task.�0�2

　　Removing this unused output column can increase Data Flow task performance.�0�2

　　当你在OLEDB源中使用“表或视图”或“来自变量的表名或视图名”数据访问模式时要小心，它的行为和select *一样，都会将所有列进行转换，当你确实需要将源表中的所有列全部转换到目标表中时，你可以使用这种方法。

最佳实践3：OLEDB目标设置的影响

　　下面是一组会影响数据转换性能的OLEDB目标设置：

　　数据访问模式：这个设置提供“快速载入”选项，它使用BULK INSERT语句将数据写入目标表中，而不是简单地使用INSERT语句(每次插入一行)，因此，除非你有特殊需求，否则不要更改这个快速载入默认选项。

　　保持一致性：默认设置是不会检查的，这意味着目标表(如果它有一个标识列)将会创建自己的标识值，如果你检查这个设置，数据流引擎将会确保源标识值受到保护，会向目标表插入相同的值。

　　保持空值：默认设置也是不会检查的，这意味着来自源表中的空值将会插入到目标表中。

　　表锁：默认设置是要检查的，建议保持默认设置，除非是同一时刻还有其它进程使用同一个表，指定一个表锁将会取得整个表的访问权，而不是表中多行的访问权，这很可能会引发连锁反应。

　　检查约束：默认设置是要检查的，如果你能确保写入的数据不会违反目标表上的约束，建议不要检查，这个设置会指定数据流管道引擎验证写入到目标表的数据，如果不检查约束，性能会有很大提升，因为省去了检查的开销。

　　最佳实践4：每批插入的行数以及最大插入大小设置的影响

　　每批插入的行数：这个设置的默认值是-1，意味着每个输入行都被看做是一个批次，你可以改变这个默认行为，将所有行分成多个批次插入，值只允许正整数，它指定每一批次包含的最大行数。

　　最大插入提交大小：这个设置的默认值是“2147483647”，它指定一次提交的最大行数，你可以修改这个值，注意，如果这个值设得太小，会导致提交次数增加，但这样会释放事务日志和tempdb的压力，因为大批量插入数据时，对事务日志和tempdb的压力是非常大的。

　　上面两个设置对于理解改善tempdb和事务日志的性能是非常重要的，例如，如果你保持最大插入提交大小的默认值，在抽取期间事务日志和tempdb会不断变大，如果你传输大批量数据，内存很快就会消耗光，抽取就会失败，因此最好基于你自身的环境为其设置一个合理的值。

　　注意：上面的建议得益于我多年的DTS和SSIS使用经验，但如前所示，还有其它因素影响性能，如基础设施和网络环境，因此，当你将这些措施推向生产环境之前，最好做一次彻底的测试。

最佳实践5：SQL Server目标适配器�0�2

　　如果你的目标是本地数据库，建议你使用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：尽可能避免异步转换�0�2

　　在讨论不同种类的转换对性能的影响之前，我们首先简要地回顾一下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,FAREAST\arali,Data Flow Task,{C80814F8-51A4-4149-8141-D840C9A81EE7},{D1496B27-9FC7-4760-821E-80285C33E74D},10/11/2009 1:38:10 AM,10/11/2009 1:38:10 AM,0,0x,No temporary BLOB data storage locations were provided. The buffer manager will consider the directories in the TEMP and TMP environment variables.

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

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

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

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

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

　　最佳实践10：使用并行执行提升性能

　　通过并行执行包和数据流任务，SSIS实现了较好的性能，SSIS包和数据流任务的并行执行可以由SSIS的两个属性进行控制。

　　MaxConcurrentExecutables：它指定一个包内的最大并行执行数(包内不同的任务)，即SSIS运行引擎可以创建的线程数量，如果你的包制定的是连续工作流，这些属性不会有任何差异，但如果你的包制定了并行任务，这个属性就需要改变，其默认值是-1，表示所有可用的处理器数+2，如果你的处理器支持超线程，那它就是所有逻辑处理器的数量+2。

　　EngineThreads：MaxConcurrentExecutables是SSIS运行时引擎并行执行时使用的属性，EngineThreads是数据管道引擎使用的属性，在SSIS 2005中默认值是5，在SSIS 2008中默认值是10，这个属性指定源线程(从源抽取数据)和工作线程(执行数据转换和加载)的数量，这些线程是由数据流管道引擎创建的，管理数据流任务中数据的传输和转换。如果EngineThreads的值为5，表示最大可以创建5个源线程和5个工作线程。注意，这个属性仅仅是给数据流管道引擎的一个建议，管道引擎可以视需求创建更多或更少的线程。

　　假设你有一个包，它有5个并行数据流任务，MaxConcurrentExecutables属性的值为3，当你开始执行这个包时，运行时将会并行启动3个数据流任务，任何数据流任务执行完时，下一个等待的数据流任务就会启动，以此类推，此时在数据流任务内发生的事情是由EngineThreads属性控制的。在最佳实践6中我已经谈到，一个数据流任务会被拆分成多个执行树，数据流管道引擎会创建源和工作线程，它们的数量等于EngineThreads属性的值，也就是可以并行执行的执行树的数量。如果你将EngineThreads的值设为5，那么你的数据流任务就会被拆分成5个执行树，但并不意味着所有执行树会并行执行。

　　在修改这些属性时一定要非常小心，在应用到生产环境之前进行彻底的测试是必要的，因为如何正确配置了这些属性，在系统有限的资源限制下，通过并行执行可以提高性能，但如果配置不当，也会损害性能，因为从一个线程到另一个线程存在太多的上下文切换，建议创建并行执行的线程数量不要超过可用的处理器数量。

　　最佳实践11：什么时候使用时间日志，什么时候应该避免使用事件日志

　　日志时诊断运行期间发生的问题的最佳方法，当你的代码没有按预期执行时，它可以帮上大忙。现在，几乎所有的编程语言都提供了日志机制，通过日志确定异常问题或运行失败的根本原因。SSIS允许你开启日志功能，它允许你选择不同的事件和组件记录日志，并且可以指定日志的存放位置。

　　虽然日志可以帮你确定问题的根本原因，但它会引起性能下降，特别是如果过度使用日志，性能下降会更明显，因此我建议你仅当必要时才开启日志功能，你可以动态设置LoggingMode属性的值来启用或禁用日志功能，当你怀疑某个包有问题时，你可以开启日志功能，并选中合适的事件进行记录。

最佳实践12：使用性能计数器监控SSIS性能

　　除了日志可以进行性能诊断外，SSIS还引入了性能计数器来监控SSIS运行时和数据流管道引擎的性能。例如，SSIS包实例计数器表示运行在系统上的SSIS包数量，行读取和行写入计数器分别表示来自源的总行数和写入到目标的总行数，缓冲区使用和缓冲区内存计数器分别表示创建的总缓冲区数量和缓冲区使用的内存大小，缓冲区输出是一个非常重要的计数器，表示当物理内存不足时写入到磁盘的缓冲区数量，BLOB字节读、BLOB字节写和BLOB文件使用计数器分别表示BLOB数据传输时BLOB字节读、写和数据流引擎目前在使用的用于输出BLOB数据的文件的数量。

　　如果你从安装有SQL Server和SSIS的Windows Server 2003升级到Windows Server 2008，SSIS性能计数器将会消失，因为升级过程移除了这些性能计数器，如果想找回这些性能计数器，请参考这篇知识库文章(http://support.microsoft.com/kb/955632)。

　　最佳实践13：SSIS中的分布式事务及其影响

　　SSIS允许你将多个可执行程序组合到一起，然后通过分布式事务在一个事务中执行，你需要启动Windows服务中的分布式事务协调处理器服务，起初一听起来感觉很酷，但它可能会有阻塞问题，特别是当某个任务的执行周期很长时，很容易发生阻塞。例如，假设在队列中你有一组数据流任务，一个Web Service任务，然后又是一个数据流任务，第一个数据流任务从源抽取数据，需要几分钟时间，Web Service任务从一个Web Service抽取数据，需要花几个小时，最后的数据流任务合并这些数据，并将它们上载到最终表中。现在如果你在一个事务中执行这三个任务，资源将会被第一个任务锁定，直到其结束，即使它不需要Web Service任务执行需要的资源。

　　因此即使SSIS提供了分布式事务的功能，但也应该谨慎使用，即使你真要使用它，也要及时将任务清除出组，并要谨慎设置IsolationLevel属性。我的建议是尽可能避免使用这个特性，转而寻求其他替代解决方案。

　　最佳实践14：检查点特性如何帮助包重启

　　SSIS有一个很酷的新特性叫做检查点(Checkpoint)，它允许你的包在下次执行时，从上次失败的地方开始启动，这样可以节省不少执行时间。你可以通过设置包的三个属性(CheckpointFileName，CheckpointUsage 和 SaveCheckpoints)开启用这个新特性，除此之外，你还需要将所有需要重启任务的FailPackageOnFailure属性设为True，设置了这个属性后，执行失败时会将相关信息捕获到检查点文件中，下次执行时就从上次失败的地方开始执行起走。

那它是如何工作的呢?当你为某个包启用检查点属性后，执行状态会写入到检查点文件中，这个文件的名字和位置是通过CheckpointFileName属性指定的。下一次执行时，运行时引擎会参考检查点文件，在重新执行包之前，先查看最后一次执行的状态，如果它发现最后一次执行的状态为失败，它就知道最后一次失败的地方，然后就从那个地方重新开始执行。如果在下一次执行前你删除了这个文件，包将会从头开始执行。

　　启用这个特性后，在下一次执行时，你可以节省大量的时间，因为它会跳过执行成功的步骤。这里需要注意的是，你可以让一个任务加入检查点，包括数据流任务，但它不能应用到数据流任务内，即你只能在数据流任务级别启用它，不能在数据流任务内部使用检查点。假设你有一个数据流任务，并将FailPackageOnFailure属性设为True让其加入检查点，现在在数据流任务内，你有5个连续转换，前四个都执行成功了，在第五个时执行失败了，在下一次执行时，还是会从第一个转换开始执行，前四个转换会再执行一次。