BI performance counter: Buffers spooled

Applies to

SSIS

Description

When Integration Services runs out physical or virtual memory during process/package execution, it will begin spooling buffers to hard disk.

This performance counter measures the total number whereby spooling of buffers to hard disk was done by Integration Services service. An increase in value of this counter may indicate that you have memory contention in your environment and you may have to consider changing DefaultBufferSize data flow task package property level.

Resolved by

DBAs, Server administrators, ETL developers

Suggested solutions

  1. Optimize the SQL data source, Lookup transformations, and destination
  2. Do not sort within Integration Services unless it is absolutely necessary
  3. Do not perform excessive casting of data types – it will only degrade performance
  4. SSIS is an in-memory pipeline, so ensure that all transformations occur in memory
  5. Try to perform your data flows in bulk mode instead of row by row

Additional research

SSIS Is An In Memory Pipeline Computer Science Essay
SSIS Pipeline performance counters
Perfmon Counters for the Integration Services Data Flow Engine

 

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