某大型银行电子渠道报表系统SSIS ETL优化报告

1. 问题分析

1.1 问题场景

在生产环境下，从电子渠道的多个交易系统通过SSIS(SQL Server Integration Services)进行数据服务平台的ETL数据抽取时出现性能问题。在初期使用过程中出现了数据抽取速度过慢和系统资源占用过高的问题，除数据库基础架构建设存在优化可能外，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 测试环境

服务器配置：IBM 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

OLAP数据库：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任务执行速度。

数据流的相关性能参数：BLOBTempStoragePath、BufferTempStoragePath、DefaultBufferMaxRows、DefaultBufferSize、EngineThreads。目前主要用到了DefaultBufferMaxRows、DefaultBufferSize、EngineThreads。

OLEDB目标中使用快速加载，参数主要设置了 每批行数和最大插入大小。

另外参数 控制流/执行 属性中的 MaxConcurrentExecutables ，对SSIS的性能也有些影响，这个属性是设置包中并发执行的可执行文件的数目。目前测试验证SSIS项目中没有针对这个属性做专门的设置，因为包中的可执行文件都是顺序执行的，可执行文件之间都约束限制。

系统性能的调优是一个需要实地化进行工作的过程，硬件环境、网络环境、软件环境（如是否64位系统，分配给SQL Server的内存等），很多理论上的技巧和开发环境性的优化措施，在生产环境下未必是最优的。如果一个系统确实对性能要求很高，性能调优的工作，应该包含在项目计划当中，在尽量接近生产环境配置和数据量级的环境下有计划地提早开始，而不是上线后再临时仓促展开。希望对大家的调优认识和工作能有所帮助。