Perfmon Counters for the Integration Services Data Flow Engine

By: Jeremy Kadlec | Read Comments (2) | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

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 could 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.  Many 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:

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.

 

Related Tips: More | Become a paid author

Last Update: 12/10/2007

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

Comments and Feedback:

Wednesday, April 16, 2008 - 4:30:54 AM - marcosgalvani	Read The Tip
Very good explanation about SSIS pipeline. Thanks.  Marcos Galvani

Monday, August 04, 2008 - 11:52:35 AM - bkshilo	Read The Tip
Hi, On a production box where I work, when I run PERFMON I can find the SQLServer:SSIS Service performance object, but not the SQLServer:SSIS Pipeline performance object.  Our system is Microsoft Windows Server 2003 R2, x64 edition.  Our SQL Server is 2005 SP2, v9.00.3257.00  How do I find the SQLServer:SSIS Pipeline performance object in Perfmon?

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL doctor is the best SQL product on the market, by far. All of Idera's tools are great, but this is the icing on the cake!" Time-strapped database professionals use SQL Monitor to look after their servers. Try it online. SQL Servers keeping you up at night? Contact the Edgewood SQL Server DBAs for a Health Check. Prices start at $995. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Join the over million SQL Server Professionals who get their issues resolved daily. The SQL Server Security THREAT - It’s Closer Than You Think