Something about SSIS Performance Counters

  • Article

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

“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.

- Runying Mao

Comments

  • Anonymous
    May 07, 2007
    Runying Mao and Len Wyatt did a great study of SSIS performance, and posted some results: Getting Optimal

  • Anonymous
    October 01, 2008
    Authors: Thomas Kejser, Denny Lee Contributors: Runying Mao Technical Reviewers: Burzin Patel, Kevin

  • Anonymous
    March 19, 2012
    Great information and really to the point.  Was really helpfull.

  • Anonymous
    February 03, 2014
    Nice information. It would really help, if u can be more declarative.

  • Anonymous
    October 07, 2015
    the url (“Set BLOBTempStoragePath and BufferTempStoragePath to Fast Drives”) does not work