SYSMON (PERFMON) counters that will help you to resolve SQL Server Integration Services issue
As you are aware that SYSMON (PERFMON) provides very useful counters to analyze the system performance or to schedule a historical growth of your system usage.
From the changes (upgrade) to the Operating System and SQL Server we now have new set of SQL Server Integration Services counters. Among them the following few are helpful when you tune or debug your package:
- Buffers in use
- Flat buffers in use
- Private buffers in use
- Buffers spooled
- Rows read
- Rows written
When a SSIS package is performing poorly and you see high spikes in CPU & disk then best go with “Buffers in use”, “Flat buffers in use” and “Private buffers in use” are useful to discover leaks that are specific to buffer, similar to this you would see the errors within Event Viewer such as 'this buffer has been orphaned...' or ' 'A call to ProcessInput unexpectedly kept a reference...'. Both of these messages ensures there is a serious problem within the system as this causes when the SQL engine is shutting down but a component still has a reference to a buffer. This usually mean the component has actually leaked a buffer since when the engine is shutting down everything else in the dataflow has already finished. These counters would have fluctuation when you have your package running, once it finishes then it should go to 0. If not it is nothing but a buffer leak in the system and better to raise a support call with CSS.
Next in the line is “Buffers spooled” which will have a default value of 0 and if you see any change to this value then you to set Data Flow Task properties BLOBTempStoragePath and BufferTempStoragePath appropriately for maximal I/O bandwidth.
The last ones “Rows read” and “Rows written” are stats for how entire Data Flow has been processed. This is nothing but a high-level information of execution progress as the data flow task in SSIS sends data in series of buffers, by design. Further reference to the optimal performance with SSIS lookups blog post and SQL Server 2005 SSIS: Tuning the Dataflow Task with further tips of using SYSMON counters from http://www.mssqltips.com/tip.asp?tip=1390 website.