Performance Counters In SSIS

There are a few key performance counters to watch when you’re trying to monitor the performance of your SSIS package. These counters greatly help you troubleshoot issues if you have memory contention or you need to tweak your SSIS settings. Inside the System Monitor (also known to old-school administrators as perfmon) is a performance object called SQLServer: SSIS Pipeline. (There are quite a few other objects as well, but they are not useful enough to describe here.)

If you’re trying to benchmark how many rows are being read and written, you can use the Rows Read and Rows Written counters. These counters show you the number of rows since you starting monitoring the packages. It sums all rows in total across all packages and does not allow you to narrow the results down to a single package.

The most important counters are the buffer counters. The Buffer Memory counter shows you the amount of memory, in total, being used by all the packages. The Buffers In Use counter indicates how many buffers are actually in use. The critical counter here, though, is Buffers Spooled. This shows you how many buffers have been written from memory to disk. This is critical for the performance of your system. If you have buffers being spooled, you have a potential memory contention, and you may want to consider increasing the memory or changing your buffer settings in your package. This is covered in more detail in Understanding and Tuning the Data Flow Engine Topic, but you should never see this number creep above a 5 if not 0.

Frequently Asked SSIS Interview Questions & Answers

List of Related Microsoft Certification Courses: