Simple Performance Monitoring for SSIS

I come from an operating system support and monitoring background so I’ve always been aware of the manageability tools available in Windows operating systems and the tools available on the market. I also have learned that the native tools in windows have improved so much over the last decade that anyone can build a relatively simple yet power monitoring process.

 

Performance Monitor, System Monitor, or Reliability and Performance Monitor (or whatever we call it next)

 

Performance Monitoring (or whatever we want to call it today) has been introducing since Windows 3.51 and has gotten better over the releases on subsequent Windows operating systems.  It’s still relatively complex for users that don’t understand operating systems components, but even with some basic computer OS knowledge (i.e. I know what I CPU, RAM, and Disk is used for) you can gather and understand some of the basic information performance statistics. In our discussion, I’ll speak about SSIS related performance stats.

 

Data Collector Sets via Permon.exe

Within Performance Monitor application, there is an additional toolset called Data Collector Sets. Data Collector Sets are used to establish what will be collected, how frequently, and in which format.

What to Collect?

Adding SSIS Performance Counters

This will depend heavily on what your role is and what needs to be monitored from a performance standpoint. From a purely SSIS standpoint (specifically SSIS 2008), I recommend the following performance counters to be captured…

•    SQLServer:SSIS Pipeline 10.0 - Buffers in use 
•    SQLServer:SSIS Pipeline 10.0 -Rows read
•    SQLServer:SSIS Pipeline 10.0 -Buffers spooled
•    SQLServer:SSIS Pipeline 10.0 - Rows written
•    SQLServer:SSIS Service 10.0 – SSIS Package Instances

These five counters will give you a good view of the overall performance of your SSIS package processing. Buffers represent the amount of memory available to do SSIS work. So if you allocate 1MB for activity the Buffers is use should present up to 1MB. This is useful because it tells you how much memory is being used and if it has met the upper limit that has been allocated. This gets a bit more complicated as you add more packages and run them in parallel because if you allocate different amounts a memory between packages it would be difficult to see if one package has maxed out is assigned amount of memory. Its gets even more complicated if you commonly specify that DefaultMaxBufferRows exceeds the available to the DefaultBufferRows in the package setting. Because of this, we need to look at Buffers Spooled to see if we are sending SSIS work to disk (very bad). 

How Frequently?

The frequency of how you should monitor SSIS activity depends on when it executes, how many packages are being monitored, and how much disk space you are willing to assign to this monitoring process. Since the premise around this post is using Windows performance counters to address large SSIS installations, this assumes that your environment SSIS processing is an issue. Therefore, a collection interval of about 15 seconds would be suitable. If your packages typically take a few minutes to process, we don’t need to monitor this activity as that would generally be a suitable processing expectation. However, SSIS processing that takes hours is where we will have the most use of collecting this performance data.

 

What Format?

As a SQL guy, my preferred method would be to use a SQL format which allows you to insert the data into an existing SQL Database. To have this work, you need to have to establish a DSN so that the Data Collector Set properties can connect and insert to the targeted database. If you don’t have access to a database DSN (which I would not be surprised as most BI developers will not often have created these) then you can simply retain the performance monitor format (*.mlb) or write the results to a CSV file.

 

Overall

 

Get in the habit of using the native OS tools to help access the performance issues within your BI environment. Although I spoke specifically on SSIS, Data Collector sets can be created for other BI applications SSAS and SSRS processing/usage as well.