Using SSIS for Performance Monitoring

You can perform performance monitoring for SSIS using System Monitor; compared to SQL Server, this has relatively few performance objects. The first object is the SSIS Service counter, and it has only one counter; SSIS Package Instances, which tells you how many packages are currently executing.

The other object is SQLServer: SSIS Pipeline, and it contains a longer list of counters, listed briefly here:

• Blob Bytes Read   The number of blob bytes read from all data sources in total

• Blob Bytes Written   The number of blob bytes written to all data sources in total

• Blob Files in Use   The number of blob spooling files in use throughout the pipeline

• Buffer Memory   The amount of memory allocated to buffers in the pipeline

• Buffers in Use   The number of pipeline buffers in use throughout the pipeline

• Buffers Spooled   The number of buffers spooled to disk

• Flat Buffer Memory   The amount of memory allocated to flat memory buffers

• Flat Buffers in Use   The number of flat memory buffers in use throughout the pipeline

• Private Buffer Memory   The amount of memory allocated to private transformation buffers

• Private Buffers in Use   The number of private transformation buffers in use throughout the pipeline

• Rows Read   The number of rows read from all data sources in total

• Rows Written   The number of rows written to all data destinations in total

Of those, the one that is generally worth watching is Buffers Spooled. This is the number of buffers that have been written to disk because of a shortage of physical memory. Buffering to disk will decrease the performance of your package.

More on Performance Tuning

Microsoft has published a whitepaper titled “Integration Services: Performance Tuning Techniques” that goes into greater detail if you are interested. It is available for download at http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx.