1. 语句优化
1.1. 索引重建
从一个有3亿条记录的大表中抽取数据，起初，当SSIS包启动时一切正常，数据如预期的那样在转换，但性能开始逐渐下降，数据转换速率直线下降。通过分析，我们发现目标表有一个主聚集键和两个非聚集键，因为大量数据插入这个表，导致其索引碎片水平达到了85%-90%。我们使用索引在线重建特性重建/重组索引，但在加载期间，每过15-20分钟，索引碎片水平又回到90%，最终数据转换和并行执行的在线索引重建过程花了12-13个小时，远远超出了我们的预期。
我们想出了一个办法，当转换开始前，我们将目标表的索引全部删掉，转换结束后又再重新创建索引，通过这样处理后，整个转换过程花了3-4小时，完全符合我们的预期。
整个过程我画在下面的图中了。因此我建议如果可能，在插入数据前，删掉目标表上的所有索引，特别是插入大数据量时
1.2. 避免使用select *
SSIS的数据流任务(Data Flow Task，DFT)使用一个缓冲区作为数据传输和转换的中转站，当数据从源表传输到目标表时，数据首先进入缓冲区，数据转换是在缓冲区中完成的，转换完毕后才会写入到目标表中。
缓冲区的大小受服务器硬件本身限制，它要估算行的大小，行大小是通过一行中所有列大小的最大值求和得出的，因此列数越多，意味着进入缓冲区的行就会越少，对缓冲区的需求就会越多，性能就会下降。因此转换时最好明确指定需要转换到目标表的列。即使你需要源表中的所有列，你也应该在select语句中明确指定列的名称，如果你使用select *，它会绕到源表收集列的元数据，SQL语句执行时间自然就会长一些
如果你将目标表不需要的列也做了转换，SSIS将会弹出警告提示信息，如：
当你在OLEDB源中使用“表或视图”或“来自变量的表名或视图名”数据访问模式时要小心，它的行为和select *一样，都会将所有列进行转换，当你确实需要将源表中的所有列全部转换到目标表中时，你可以使用这种方法
2. OLEDB目标设置的影响
下面是一组会影响数据转换性能的OLEDB目标设置：
数据访问模式：这个设置提供“快速加载”选项，它使用BULK INSERT语句将数据写入目标表中，而不是简单地使用INSERT语句(每次插入一行)，因此，除非你有特殊需求，否则不要更改这个快速载入默认选项。
保留标识(保持一致性)：默认设置是不会检查的，这意味着目标表(如果它有一个标识列)将会创建自己的标识值，如果你检查这个设置，数据流引擎将会确保源标识值受到保护，会向目标表插入相同的值。
保留Null(保持空值)：默认设置也是不会检查的，这意味着来自源表中的空值将会插入到目标表中。
表锁：默认设置是要检查的，建议保持默认设置，除非是同一时刻还有其它进程使用同一个表，指定一个表锁将会取得整个表的访问权，而不是表中多行的访问权，这很可能会引发连锁反应。
检查约束：默认设置是要检查的，如果你能确保写入的数据不会违反目标表上的约束，建议不要检查，这个设置会指定数据流管道引擎验证写入到目标表的数据，如果不检查约束，性能会有很大提升，因为省去了检查的开销。
3. 每批插入和最大插入设置影响
3.3. 每批插入行数设置的影响
每批插入的行数：这个设置的默认值是-1，意味着每个输入行都被看做是一个批次，你可以改变这个默认行为，将所有行分成多个批次插入，值只允许正整数，它指定每一批次包含的最大行数。
3.4. 最大插入行数设置影响
最大插入提交大小：这个设置的默认值是“2147483647”，它指定一次提交的最大行数，你可以修改这个值，注意，如果这个值设得太小，会导致提交次数增加，但这样会释放事务日志和tempdb的压力，因为大批量插入数据时，对事务日志和tempdb的压力是非常大的
上面两个设置对于理解改善tempdb和事务日志的性能是非常重要的，例如，如果你保持最大插入提交大小的默认值，在抽取期间事务日志和tempdb会不断变大，如果你传输大批量数据，内存很快就会消耗光，抽取就会失败，因此最好基于你自身的环境为其设置一个合理的值。
4. SQL Server目标设置
如果你的目标是本地数据库，建议你使用SQL Server目标，它提供了与Bulk Insert任务类似的数据插入性能，并提供了某些额外增强。使用SQL Server目标，在写入目标表之前你可以对数据做转换操作，但Bulk Insert任务是不行的，除了OLEDB目标可用的选项外，SQL Server目标还有更多选项，如图1所示。例如，你可以指定是否触发目标表上的插入触发器，默认情况下，这个选项设置为“false”，意味着不会触发目标表上的触发器，如果启用触发器，将会引起性能下降，但为了强制实时数据和业务规则，触发器是不可避免的。另外还有选项可以指定第一次和最后一次载入的数量，指定错误的最大数量，以及指定插入列的顺序
图一
如果你的SQL Server数据库在远程服务器上，就不能使用SQL Server目标适配器，这个时候只能使用OLEDB目标适配器。此外，如果目标数据库可能会从本地改为远程，或从一个数据库实例改为另一个数据库实例，也最好使用OLEDB目标适配器，以减小未来可能的改变
5. 尽可能避免异步转换
在讨论不同种类的转换对性能的影响之前，我们首先简要地回顾一下SSIS的工作原理，SSIS运行时引擎执行包，当SSIS运行时引擎遇到数据流任务时，它会将数据流任务交给数据流管道引擎，数据流管道引擎会将数据流任务拆分成多个执行树，可能会同时执行两个或多个执行树以提高并发处理能力和性能。你可能还不知道什么是执行树，下面就是答案。
正如其名，执行树与树的结构类似，每个执行树有一套缓冲区，其范围与执行树紧密相关，每个执行树也分配有一个操作系统线程，与缓冲区不同，线程可能与其它执行树是共享的，即一个线程可以执行一个或多个执行树。在SSIS 2008中，将数据流任务拆分成执行树的进程已经得到了极大的增强，它可以创建一个执行路径和子路径，以便你的包可以利用高端多处理器系统。
同步转换获得一个记录，经过处理，然后将其传给其它转换进程或下一个目标，记录的处理不依赖于其它传入的行，因为同步转换输出的记录数和输入的记录数是相同的，它不需要新的缓冲区(处理是在相同的入站缓冲区中完成的)，因为就这样已经很快了。例如，在Derived列转换过程中，在每个入站行增加一列，但不会增加输出的记录数。
与同步转换有点不一样，异步转换输出的记录数和输入的记录数可能不一样，需要创建新的缓冲区，因为一个输出依赖于一条或多条记录，也被称作阻塞转换。例如，排序转换就是一个不折不扣的阻塞转换，它要求所有入站的行在处理之前必须抵达才行。
正如上面所讨论的，异步转换需要额外的缓冲区用于输出，不会重复利用入站输入缓冲区，在处理之前它也会等待所有的入站行抵达，这也是异步转换执行得慢的原因，因此要尽可能避免这种情况。例如，如果不是有排序转换，你可以ORDER BY子句从源表本身获得已经排好序的结果。
6. DefaultBufferMaxSize和DefaultBufferMaxRows
正如我在最佳实践5中谈到的，执行树为入站数据排序和执行转换创建一个缓冲区，那么要创建多大的缓冲区合适呢?单个缓冲区有多少行数据进入呢?它对性能有何影响呢?
缓冲区的大小依赖于有多少行数据进入缓冲区，有多少行数据进入缓冲区又依赖于其它一些因素。首先要考虑的是评估每一行的大小，它等于所有入站行包含的所有列的最大大小，其次要考虑的是数据流任务的DefaultBufferMaxSize属性，它指定了一个缓冲区的默认最大大小，默认值是10MB，它的上下限是由SSIS的两个内部属性限制的，分别是MaxBufferSize(100MB)和MinBufferSize(64KB)，意味着一个缓冲区的大小范围是64KB到100MB，第三个因素是DefaultBufferMaxRows，它也是数据流任务的一个属性，它指定了进入缓冲区的默认行数，默认值是10000。
虽然SSIS提供了这么多的属性可以设置一个合适的缓冲区大小，如果大小超出了DefaultBufferMaxSize的值，它会减少进入缓冲区的记录行数。为了提高缓冲区的性能，你可以做两件事情：
1、首先从源中移除不需要的列，并为每一列设置正确的数据类型，特别是你的源是一个平面文件时，这样可以让缓冲区尽可能容纳更多的记录行。
2、如果你的系统有充足的内存，你可以通过调整这些属性，最后创建少量的大缓冲区，这样会提升性能。注意，如果你将这些属性的值修改到某个分页开始的临界值，会对性能产生不利的影响，因此在设置这些属性之前，首先应在你的环境进行全面的测试，最终找到一个合适的值。
你可以开启BufferSizeTuning事件的日志，这样就可以看到进入缓冲区的行数，你也可以监视“Buffers spooled”性能计数器查看SSIS是否开始了分页
7. BufferTempStoragePath和BLOBTempStoragePath
如果内存资源不够，Windows会触发一个内存过低的通知事件，内存溢出、内存压力、输入记录，除了BLOB，SSIS会将它们输出到文件系统，文件系统的位置就是由数据流任务的BufferTempStoragePath属性设置的，默认是空的，在这种情况下，输出位置基于TEMP/TMP系统变量指定的位置。
同样，SSIS在将BLOB数据发到目标之前，可能会将其先写入到文件系统，因为BLOB数据通常非常大，SSIS缓冲区中存储不下，输出的位置是有数据流任务的BLOBTempStoragePath属性设置的，默认是空的，在这种情况下，输出位置也是基于TEMP/TMP系统变量的，如果你不为这些属性指定具体的值，TEMP和TMP系统变量的值将会被当做输出的目标，如果你开启了数据流任务PipelineInitialization事件的日志，相同的信息会被记录到日志文件中，如：
最重要的就是改变BufferTempStoragePath和BLOBTempStoragePath的默认值，最好是将它们设为不同的磁盘路径，这样可以提高I/O效率，从而提升整体性能
8. 好好利用DelayValidation属性
SSIS使用验证确定包在运行时是否会失败，它使用两种类型的验证，第一种是包验证，在开始执行包之前，验证包及其包含的所有组件，第二种是组件验证，一开始就验证包中的所有组件。
我们假设一个场景，包中的第一个组件创建一个对象，如一个临时表，包中的第二个组件将引用这个临时表，在包的验证过程中，第一个组件还没来得及执行，因此临时表也还没创建好，在验证第二个组件时最终导致包验证失败。SSIS会抛出一个验证异常，并不会启动包的执行，那么你将如何处置这种场景中的包?
为了解决这种场景存在的问题，每个组件都有一个DelayValidation属性，默认值为“flase”，如果你将其设为“true”，所有验证都会忽略，在包执行过程中，只会在组件级验证组件。