SSIS performance monitoring

Perfmon counters for the data flow engine in SQL Server integration services SSIS Written By: Jeremy Kadlec Problem We have adopted SQL Server 2005 Integration Services and we are moving a great deal of data on a consistent basis throughout the day for a number of systems. unfortunately, we have been seeing some memory related issues and wanted to find out how we cocould monitor these on a regular basis? We want someway to collect performance related data and monitor the overall process. What tools and metrics are available for monitoring an SSIS Package? Can you provide some examples? Solution With SQL Server 2005 Microsoft provided counters in Performance Monitor (System Monitor) to monitor some key metrics of the SQL Server 2005 Integration Services metrics. because of these new counters are related to memory, so they may offer some insight into your issue, but that is not guaranteed based on the problem provided. one item to keep in mind is that these counters are new with SSIS and equivalent (or similar) counters were not available with SQL Server 2000/7. 0 DTS. Performance Monitor counters Below are the SSIS Performance Monitor counters that are available: Sqlserver: SSIS Service SSIS package instances-Total number of simultaneous SSIS packages running Sqlserver: SSIS Pipeline Blob bytes read-Total Bytes read from Binary large objects during the monitoring period. Blob bytes written-total bytes written to Binary large objects during the monitoring period. Blob files in use-Number of Binary large objects files used during the data flow task during the monitoring period. Buffer Memory-the amount of physical or virtual memory used by the data flow task during the monitoring period. Buffers in use-the number of buffers in use during the data flow task during the monitoring period. Buffers spooled-the number of buffers written to disk during the data flow task during the monitoring period. Flat buffer memory-the total number of blocks of memory in use by the data flow task during the monitoring period. Flat buffers in use-the number of blocks of memory in use by the data flow task at a point in time. Private buffer memory-the total amount of physical or virtual memory used by data transformation tasks in the data flow engine during the monitoring period. Private buffers in use-the number of blocks of memory in use by the transformations in the data flow task at a point in time. Rows read-Total number of input rows in use by the data flow task at a point in time. Rows written-Total number of output rows in use by the data flow task at a point in time. One word of caution with these counters, they appear to be calculations in the aggregate as opposed to a single SSIS Package execution. depending on the issue, it might make sense to conduct some isolated testing as well as monitor the overall environment. if you suspect a single package or group of packages causing the issue, then these metrics and general For additional information reference SQL Server 2005 books online-monitoring performance of the data flow engine. Sample Performance Monitor Data Below is an explanation of how to access into the data flow tasks via performance monitor: Launch performance monitor by navigating: Start | all programs | Administrative Tools | Performance   Load the SSIS related counters Click on the '+' from the menu bar In the performance object select sqlserver: SSIS pipeline and sqlserver: SSIS Service   Monitor the performance monitor results In the menu select the light bulb icon to highlight a single counter Press the up and down arrow keys to highlight varous counters   Next steps If you are faced with the need to gain insight into how the SSIS Data Flow task is using resources, primarily memory, performance monitor offers some insight into the processing. If you have a busy SSIS environment, fire up performance monitor with your favorite counters and add the SSIS counters to get a better feel for the load from the data flow task operations. Stay tuned for more options to monitor SSIS packages progress via the Microsoft tool set in the upcoming weeks.