Saturday, February 22, 2014

SSIS Synchronous and Asynchronous

Transformations are broken into two main categories: synchronous and asynchronous.
Synchronous transformations are components like the Derived Column and Data Conversion
Transforms where rows flow into memory buffers in the transform and the same buffers come
out. No rows are held and typically these transforms perform very quickly with minimal impact
to your Data Flow.

There are two types of asynchronous transforms: fully blocking and partial blocking.
Partial blocking transforms, such as the Union All Transform, create new memory buffers for
the output other transform than what come into the transform. Full blocking transforms, such
as the Sort and Aggregate Transforms, do the same thing but cause a full block of the data. In
order to sort the data, SSIS must first see every single row of the data. If you have a 100MB
file, then you may require 200MB of RAM in order to process the Data Flow because of a fully
blocking transform. These fully blocking transforms represent the single largest slowdown in
SSIS and architecture decisions you must make.

No comments:

Post a Comment