SSIS性能调优备忘

目 录

内容

1. 问题分析... 3

1.1 环境描述... 3

1.2 问题场景... 3

1.3 原因分析... 3

2. 解决步骤... 4

2.1 性能参数调整... 4

2.2 测试对比... 6

2.3 结论... 6

1. 问题分析

1.1 环境描述

服务器配置：IBM XSERIES_3755 Dual-Core AMD Opteron(tm) Processor 8212

CPU <chmetcnv w:st="on" unitname="g" sourcevalue="2" hasspace="False" negative="False" numbertype="1" tcsc="0">2G</chmetcnv> X 4 Dual-Core 内存<chmetcnv w:st="on" unitname="g" sourcevalue="8" hasspace="False" negative="False" numbertype="1" tcsc="0"><span lang="EN-US"><font face="Times New Roman">8G</font></span></chmetcnv> 存储 本地SCSI

Windows 2003 SP2 R2企业版

OLTP数据库：Sybase 12.5

OLAP数据库：SQL Server2005企业版SP2

网络：<chmetcnv w:st="on" unitname="m" sourcevalue="100" hasspace="False" negative="False" numbertype="1" tcsc="0"><span lang="EN-US"><font face="Times New Roman">100M</font></span></chmetcnv>

1.2 问题场景

在某国有银行内部管理报表开发过程中，选定了SQL2005的SSIS(SQL Server Integration Services)进行ETL数据采集和统计预处理，从网银Sybase OLTP数据库中抽取数据供OLAP数据分析和报表展现。但在初期使用过程中出现了数据抽取速度过慢的问题，以网银日志表为例，在未进行性能参数调整前，采集255,8374条共计923,560 KB的数据，需要一个半小时左右，CPU、内存、硬盘、网络的设备利用率都很低，由于参数设置不当导致无法发挥SSIS真实性能的现象明显。

1.3 原因分析

SSIS采用VS2005集成的开发环境，对数据采集的过程可进行可视化的定制开发，对数据量小的维度表（字典表）可同时并行抽取多个，以加快抽取速度，而对于大的事实表(业务表)可采用串行化抽取，是一个日志表，数据量较大，采用串行化方式提取。

在分析开发人员开发的SSIS 包时，主要发现几个与性能有关的参数值设置不当：

1．事实上在大数据量操作时，将大事务拆解成小事务是实践证明的最高效的处理方式，如把数据提交放在一个很大的事务中，就会出现事务保持和回滚需要更多的资源和时间，并造成物理内存不足，且更容易出错。

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

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

2. 解决步骤

2.1 性能参数调整

SSIS数据流引擎可以通过计算一行数据的估计大小来调整其缓冲区大小的任务。 引擎将估计的单行大小与 DefaultBufferMaxRows 值相乘以获得缓冲区大小的初步工作值。可计算出单行数据大小，乘以每次提交数据的行数，得到适当的缓冲大小

1、如果该结果大于 DefaultBufferSize 值，引擎将减少行数。

2、如果该结果小于内部计算的最小缓冲区大小，引擎将增加行数。

3、如果结果在最小缓冲区大小和 DefaultBufferSize 值之间，引擎将调整缓冲区大小，以尽可能接近估计行大小乘以 DefaultBufferMaxRows 值得出的结果。

4、默认缓冲区大小为 10 MB，最大缓冲区大小为 100 MB。 默认最大行数为 10,000。

参数	值	说明
DefaultBufferMaxRows	1000	较小的事务会加速提交速度
DefaultBufferSize	10485760	为事务准备充足而适当的缓冲空间
EngineThreads	7	线程数贴近物理CPU个数

<shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></path><lock aspectratio="t" v:ext="edit"></lock></shapetype><shape id="_x0000_i1026" style="WIDTH: 471.75pt; HEIGHT: 384pt" type="#_x0000_t75"><imagedata o:title="SSIS_1" src="file:///C:%5CDOCUME~1%5Cadmin%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image001.jpg"></imagedata></shape>

数据访问模式设置为：表或视图-快速加载，每批行数设为1000。

<shape id="_x0000_i1025" style="WIDTH: 472.5pt; HEIGHT: 361.5pt" type="#_x0000_t75"><imagedata o:title="SSIS_2" src="file:///C:%5CDOCUME~1%5Cadmin%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image003.jpg"><font size="3"></font></imagedata></shape>

2.2 测试对比

几组测试数据对比：

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条，数据约<chmetcnv w:st="on" unitname="m" sourcevalue="900" hasspace="False" negative="False" numbertype="1" tcsc="0"><span lang="EN-US"><font face="Times New Roman">900M</font></span></chmetcnv>

2.3 结论

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