Expert SQL Server™ 2005 Integration Services

9.10. Performance Counters

There are a few key performance counters to watch when you're trying to monitor performance of your SSIS package. These counters will greatly help you to troubleshoot if you have memory contention, or if 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 is another object as well, but it's 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 started monitoring the packages. It sums all rows in total across all packages, and does not allow you to narrow down to a single package.

The most important counters are the buffer counters. The Buffer Memory counter shows you the total amount of memory being used by all the packages. The Buffers In Use counter shows you 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 will be discussed more in Chapter 12, but for the time being, know that you should never see this number creep above ...