Part of the performance monitoring of the Integration Services service includes configuring the logging of performance counters. These counters enable you to view and understand the use of resources consumed during the execution of SSIS packages. Specifically, the logging encompasses event-resource usage, whereas packages perform the Data Flow Tasks.
Begin by focusing on some of the more insightful counters, including the following (at a server-level):
- Rows Read: Provides the number of rows read from all data sources during package execution
- Buffers in Use: Details the number of pipeline buffers (memory pools) in use throughout the package pipeline
- Buffers Spooled: Specifies the number of buffers used to handle the data flow processes
The Buffers Spooled counter is important because it is a good indicator of when your machine runs out of physical memory or runs out of virtual memory during data flow processing. The importance of using buffers rather than spooling to disk is the difference between a package with 20 minutes execution time versus 20 hours in some cases. Each time you see a buffer spooled, a 10MB buffer has been written to disk.
One example of how these performance counters can be used includes ensuring that your server running the SSIS packages has enough memory. One of the bottlenecks in any transformation process includes input/output operations, whereby data is staged to disk during the transformations. Integration Services was designed to optimize system resources when transforming data between a source and destination, including attempting to perform these transformations in memory, rather than having to stage data to disk and incur I/O performance penalties. You should expect to see the value of the Buffers Spooled counter remain at zero (0) when only memory is being used during the transformation processes being performed by the SSIS packages. When you observe that the Buffers Spooled counter is normally valued higher than zero (0), it’s a good indication that more memory is needed on the server processing the SSIS packages.
SQL Server Profiler enables you to analyze the data operations and query plans generated for various data flow pipeline activities. You can use this information to refine indexes or apply other optimization techniques to the data sources your SSIS solution uses.
Leave a Reply