SSIS流程优化

1.
问题分析 1.1
问题场景 在生产环境下，从V7\V8等多个交易系统通过( Integration Services)进行服务平台的数据抽取时出现性能问题。在初期使用过程中出现了数据抽取速度过慢和系统资源占用过高的问题，除基础架构建设存在优化可能外，SSIS未做面向生产环境硬件的优化也是一个重要原因，导致无法正常发挥SSIS真正性能。

1.2
原因分析 SSIS采用VS2005集成的开发环境，对数据采集的过程可进行可视化的定制开发，对数据量小的表（字典表）可同时并行抽取多个，以加快抽取速度，而对于大的事实表(业务表)可采用串行化抽取，对于数据量较大的表，采用串行化方式提取。
在分析开发人员开发的SSIS 包时，主要发现几个与性能有关的参数值设置问题：
1．事实上在大数据量操作时，将大事务拆解成小事务是实践证明的最高效的处理方式，如把数据提交放在一个很大的事务中，就会出现事务保持和回滚需要更多的资源和时间，并造成物理内存不足，且更容易出错。

2．线程做为操作系统争用CPU资源的系统对象，本身会花费一定的CPU时间进行线程切换与同步，所以线程数不应比物理CPU数量多太多，考虑到操作系统本身和其他运行中的对CPU的占用，甚至要略少于物理CPU。

3．SSIS提供不检查check约束和触发器的快速加载方式(Fast load)，可提高数据写入的速度，对于不需要严格审查的数据比较适用。


1.3
参数优化建议 以下的章节提供了一些较为常用的SSIS执行性能优化的参数和建议，以供调优参考。由于目前我们缺少大数据量的模拟环境，建议在生产环境中调整参数时，循序渐进，逐步调整。

2.
优化包参数 2.1
MaxConcurrentExecutables MaxConcurrentExecutables是包的属性，它定义有多少个任务能被同时运行
  如果值为－1的话就意味着同时可以运行的任务数为当前处理器的个数加上2
  当超线程被打开的时候，它是逻辑处理器的个数
3.
控制流参数 3.1
MaxConcurrent 这是ForEachLoop组件的一个属性，意味着有多少个循环实例能并行运行
4.
数据流参数
4.1
EngineThreads 属性   这是数据流任务中的属性，它定义有多少个工作线程在引擎调度时可以被使用。缺省值为5，可设置范围为2-60之间，建议根据物理CUP个数调高到总CUP个数左右。如双核8C的，可设置为15-17个左右，具体应依实际对比测试性能而定。
注：多核处理器应当算作多个处理器。
4.2
DefaultBufferMaxRows属性 SSIS数据流引擎可以通过计算一行数据的估计大小来调整其缓冲区大小的任务。 引擎将估计的单行大小与 DefaultBufferMaxRows 值相乘以获得缓冲区大小的初步工作值。可计算出单行数据大小，乘以每次提交数据的行数，得到适当的缓冲大小
1、如果该结果大于 DefaultBufferSize 值，引擎将减少行数。
2、如果该结果小于内部计算的最小缓冲区大小，引擎将增加行数。
3、如果结果在最小缓冲区大小和 DefaultBufferSize 值之间，引擎将调整缓冲区大小，以尽可能接近估计行大小乘以 DefaultBufferMaxRows 值得出的结果。
4、默认缓冲区大小为 10 MB，最大缓冲区大小为 100 MB。 默认最大行数为 10,000。

4.3
DefaultBufferSize属性 此参数应与DefaultBufferMaxRows配合使用。
  
4.4
BufferTempStoragePath属性
为缓冲区数据指定临时存储位置。默认情况下，这些属性包含 TEMP 和 TMP 环境变量的值。您可能希望指定不同或更快的硬盘驱动器上的其他文件夹来存放临时文件，或将它们分布在多个驱动器上。可以指定多个目录，并用分号来分隔这些目录名。
4.5
BLOBTempStoragePath属性 为包含二进制大型对象 (BLOB) 数据的列指定临时存储位置。默认情况下，这些属性包含 TEMP 和 TMP 环境变量的值。您可能希望指定不同或更快的硬盘驱动器上的其他文件夹来存放临时文件，或将它们分布在多个驱动器上。可以指定多个目录，并用分号来分隔这些目录名。


4.6
数据流目标参数设置建议 在选用OLEDB做为数据目标时，可将数据访问模式可设置为：表或视图-快速加载，每批行数可设为1000、5000、10000、20000、50000做分别的性能对比测试。
另外，也建议使用SQL Server目标做性能对比测试，一般情况下SQL Server目标性会比OLE DB目标更好。

5.
模拟测试 5.1
测试环境 服务器配置： XSERIES_3755 Dual-Core AMD Opteron(tm) Processor 8212
CPU 2G X 4 Dual-Core

内存8G
存储 本地SCSI
Windows 2003 SP2 R2企业版
OLTP数据库：Sybase 12.5
数据库：SQL Server2005企业版SP2
网络：100M

5.2
模拟测试对比 批行数设置为1000时的几组测试数据对比：

DefaultBufferMaxRows	DefaultBufferSize	EngineThreads	已用时间	数据量	数据大小
10000	20971520	5	2125.53秒	2558374条	923560 KB
10000	10485760	5	1818.7 秒	2558374条	923560 KB
1000	10485760	6	220.891秒	2558374条	923560 KB
1000	10485760	7	220.438 秒	2558374条	923560 KB
1000	10485760	8	236.047秒	2558374条	923560 KB

最佳性能的测试指标为220秒(3分40秒)，数据笔数255,8374条，数据约900M
5.3
结论

测试对比结果表明，设置适合的缓冲大小，采用较小的事务提交，并使用适当的线程数可数十倍地提高SSIS包的ETL任务执行速度。