Understanding SSIS Data Flow Buffers (SQL Server Video)
This video looks at the memory buffers that transfer data to the data flow of an Integration Services package. The video includes the following items:
Demonstration of a simple package that shows you how Integration Services divides the data along the data flow paths and how buffers carry data along those paths.
This simple package performs the following operations:
Creation of new columns by using a synchronous transformation
Sorting by using a blocking (asynchronous) transformation.
Design guidelines for building and maintaining the data flow of a package.
Technical notes are selected excerpts from the video.
You can improve the performance of the package data flow by doing the following tasks:
Keep the data flow buffers in memory, in particular during the transformation phase of the data flow. You can watch the "Buffers spooled" counter to determine whether data buffers are being written to disk temporarily while a package is running. For more information, see the topic, Monitoring the Performance of the Data Flow Engine, and the MSDN blog posting, Something about SSIS Performance Counters.
Adjust the size of the buffer by using the DefaultBufferSize and DefaultBufferMaxRows properties.
Minimize the use of blocking transformations, such as the Aggregate and Sort transformations.
For more information, see the topics, Improving the Performance of the Data Flow and Understanding Synchronous and Asynchronous Transformations.
To learn more about how to improve the performance of the data flow, watch the video, Tuning Your SSIS Package Data Flow in the Enterprise (SQL Server Video).