BI performance counter: Buffers in use

Applies to

SSIS

Description

Buffers are the mechanism used by the data flow engine to process data. This performance counter monitors the total number of buffers currently used by the data flow engine.

The value of this performance counter fluctuates as the amount of data being processed in the data flow changes.

A low value indicates a less busy data flow engine while a high value indicates a busy data flow engine. Whenever a Sort transformation is used within the data flow task, expect an increase in this performance counter as the Sort transformation is a blocking transformation that uses multiple buffers.

Resolved by

DBAs, Server administrators, ETL developers

Suggested solutions

  1. Make sure you are using the most up-to-date driver configurations for your network, data source, and disk I/O
  2. If possible, perform your date-time conversions at your source or target databases, as it is more expensive to perform within Integration Services
  3. Try to perform your data flows in bulk mode instead of row by row
  4. If you need to perform delete operations, organize your data in a way so that you can TRUNCATE the table instead of running a DELETE

Additional research

Top 10 SQL Server Integration Services Best Practices
SSIS: Performance Counter
Introduction to BLOB data types in SSIS: Part 1

 

To track BI performance metrics – consider using ApexSQL BI Monitor, a web application that monitors system, SSAS, SSIS and SSRS performance in real time.

⇐ Back to index