Understanding SSIS Data Flow Buffers (SQL Server Video)
Applies to: Microsoft SQL Server Integration Services |
Authors: Bob Bojanic, Microsoft Corporation |
Length: 00:11:44 Size: 28.9 Mb Type: WMV file |
Watch this Video on TechNet, to watch, share, and download the video in multiple formats. |
|
|
Video Summary
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:
Data extraction
Character mapping
Creation of new columns by using a synchronous transformation
Multicasting
Sorting by using a blocking (asynchronous) transformation.
Design guidelines for building and maintaining the data flow of a package.
Video Acknowledgements
Thank you to Thomas Kejser for contributing to the material for the series, SSIS: Designing and Tuning for Performance SQL Server Video Series. This video is the third in the series.
Thank you to Carla Sabotta and Douglas Laudenschlager for their guidance and valuable feedback.
Video Technical Notes
Note
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).