Just another WordPress.com site

Something about SSIS Performance Counters

Source: http://blogs.msdn.com/b/sqlperf/archive/2007/05/01/something-about-ssis-performance-counters.aspx

SQL Server Integration Services provide a set of performance 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 

photo

photo

“Buffers in use”, “Flat buffers in use” and “Private buffers in use” are useful to discover leaks. During package execution time, you will see these counters fluctuating. But once the package finishes execution, their values should return to the same value as what they were before the execution. Otherwise, buffers are leaked. In occasions like that, please contact Microsoft PSS.

“Buffers spooled” has an initial value of 0. When it goes above 0, it indicates that the engine has started memory swapping. In a case like that, please follow my previous blog (“Set BLOBTempStoragePath and BufferTempStoragePath to Fast Drives”) to set Data Flow Task properties BLOBTempStoragePath and BufferTempStoragePath appropriately for maximal I/O bandwidth. 

“Rows read” and “Rows written” show how many rows the entire Data Flow has processed. They give you an overall idea about the execution progress.

———————————-

More Perfmon Counters for SSIS
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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s