This article is part 2 of a 4 part series that explores the internals of SQL Server Integration Services. This article looks at SSIS Transformation and Execution Trees.
Transformation and Execution Tree
Synchronous transformation/component
A synchronous transformation processes incoming rows and passes them on in the data flow one row at a time. Output is synchronous with input, it occurs at the same time. Therefore, to process a given row, the transformation does not need information about other rows in the data set. When a transform can modify the row in place so as to not change the physical layout of the result set, it is said to be a synchronous transformation. The output of a synchronous component uses the same buffer as the input and does not require data to be copied to a new buffer to complete the transformation. Reuse of the input buffer is possible because the output of a synchronous component usually contains the same number of records as the input; each output row has a 1:1 relationship with an input row (though the redirect method can be used to conditionally direct rows to different outputs as multicast and conditional split transformations).
An example will aid understanding. A Character Map transformation for upper case operation, while processing a buffer, it converts the letters in a text column to upper-case without changing the layout of the buffer itself. Thus, the character map transformation is a “synchronous transform”, others which fall under this category include Derived Column, Data Conversion, Copy column, Multicast, Row count, Lookup etc. Synchronous transformations are also called Row Transformations.
Asynchronous transformation/component
When the output buffer of a transformation defines a new buffer type, it is an asynchronous transformation. The output buffer or output rows are not in sync with the input buffer, output rows use a new buffer. In these situations it’s not possible to reuse the input buffer because an asynchronous component can have more, the same or less output records than input records.
Asynchronous components can further be divided into the two types described below:
Partially Blocking Transformation – the output set may differ in terms of quantity from the input set. Thus new buffers need to be created to accommodate the newly created set. With partially blocking transformations, the output of the transformation is copied into a new buffer and a new thread may be introduced into the data flow.
Blocking Transformation – a transformation that must hold one or more buffers while it waits on one or more buffers, before it can pass that buffer down the pipeline. All input records must read and processed before creating any output records. For example, a sort transformation must see all rows before sorting and block any data buffers from being passed down the pipeline until the output is generated.
It’s important to remember is that synchronous components reuse buffers and therefore are generally faster than asynchronous components, which need a new buffer, perform the most work and can have the greatest impact on available resources.
Note:
To achieve a high level of parallelism on a multiprocessor machine, sometimes an asynchronous transformation is added to create a new tree (Last tree might be executed by one thread and later one by another). For example, inserting a “Union All” asynchronous transformation with one input, though it does not change the outputs, splits the execution tree into two new trees – each one can be executed on its own processor. The drawback of this approach is even though there are two execution trees executed by two threads in parallel, the inclusion of an asynchronous transform will require additional memory to copy the data into the new buffers created after the transformation; hence this creates additional overhead. Beginning with SSIS, this transformation is no longer required to achieve parallelism, the pipeline engine has been enhanced to a use a multiprocessor system, more details can be found in the article viz. “SQL Server Integration Services – New Features in SQL Server 2008”.
All source adapters are asynchronous; they create two buffers; one for the success output and one for the error output. All destination adapters on the other hand, are synchronous.
To summarise what has been covered so far:
Row Transformation (synchronous transformation):
- Row-by-row basis
- Do not block data flow in the pipeline
- Data is not copied around, only pointers
- Examples: Data Conversion, Derived Columns, Copy column, Multicast, Row count, Lookup etc.
Partially Blocking Transformation (asynchronous transformation):
- Introduces new buffers in memory layout
- Transformed data is copied into new buffers
- Examples: Merge, Merge Join, Union All etc.
Blocking Transformation (asynchronous transformation):
- Must see all data before passing on rows
- Block the data flow – can be heavy on memory
- May also use “private buffers” to assist with transforming data
- Examples: Sort, Aggregate etc.
Execution Tree
An execution tree is a group of transformations which start at either a source adapter or an asynchronous transform and end at the first asynchronous transform or a destination adapter. A data buffer has the scope of an execution tree.
At run time, the data flow engine breaks down Data Flow task operations into execution trees. These execution trees specify how buffers and threads are allocated in the package. Each tree creates a new buffer and may execute on a different thread. When a new buffer is created, such as during a partially blocking or blocking transformation, it is added to the pipeline, additional memory is required to handle the data transformation. It is important to note that each new tree may also give an additional worker thread.
Execution trees are enormously valuable in understanding buffer usage. They can be displayed for packages by turning on package logging (event logging was described in the article viz. “SQL Server Integration Services – Features and Properties”), enabling logging for the Data Flow task, and then selecting the following events related to the pipeline:
BufferSizeTunning – The data-flow engine may change the size of the buffer away from the default values. This event gives details of the new sizes with the reasons as given below.
BufferSizeTuning,ARALI-LAPTOP,FAREASTARALI,Data Flow Task, {48C467B8-6063-45AF-B7EF-43C2CDA6BEA4},{69F539CB-3544-40CE-8E59-786A9475C14C},12/22/2008 5:59:20 AM,12/22/2008 5:59:20 AM,0,0x,Rows in buffer type 0 would cause a buffer size greater than the configured maximum. There will be only 9497 rows in buffers of this type.
PipelineInitialization – This event gives the various initialization details of the data flow task as given below:
PipelineInitialization,ARALI-LAPTOP,FAREASTARALI,Data Flow Task,{48C467B8-6063-45AF-B7EF-43C2CDA6BEA4},{F1F001F5-0613-46EA-B183-3D25EE08FF09},12/22/2008 5:48:16 AM,12/22/2008 5:48:16 AM,0,0x,No temporary buffer storage locations were provided. The buffer manager will consider the directories in the TEMP and TMP environment variables.
PipelineInitialization,ARALI-LAPTOP,FAREASTARALI,Data Flow Task,{48C467B8-6063-45AF-B7EF-43C2CDA6BEA4},{F1F001F5-0613-46EA-B183-3D25EE08FF09},12/22/2008 5:48:16 AM,12/22/2008 5:48:16 AM,0,0x,The default buffer size is 10485760 bytes.
PipelineInitialization,ARALI-LAPTOP,FAREASTARALI,Data Flow Task,{48C467B8-6063-45AF-B7EF-43C2CDA6BEA4},{F1F001F5-0613-46EA-B183-3D25EE08FF09},12/22/2008 5:48:16 AM,12/22/2008 5:48:16 AM,0,0x,Buffers will have 10000 rows by default.
PipelineInitialization,ARALI-LAPTOP,FAREASTARALI,Data Flow Task,{48C467B8-6063-45AF-B7EF-43C2CDA6BEA4},{F1F001F5-0613-46EA-B183-3D25EE08FF09},12/22/2008 5:48:16 AM,12/22/2008 5:48:16 AM,0,0x,The data flow will not remove unused components because its RunInOptimizedMode property is set to false.
Note:
Running the package or data flow task in Optimized mode improves performance by removing unused columns, outputs, and components from the data flow.
PipelineExecutionTrees – reports the execution trees layout in the data flow. The scheduler of the data flow engine uses the trees to build the execution plan for the data flow, example is give below.
PipelineComponentTime – a new event with SQL Server 2008, it tells the number of milliseconds that each component in the data flow spends on each of the five major processing steps (Validate, PreExecute, PostExecute, ProcessInput and PrimeOutput), below is subset of the messages for this event.
The component “OLE DB Source” (1) spent 0 milliseconds in PostExecute. 12/24/2008 6:18:07 PM 12/24/2008 6:18:07 PM
The component “Derived Column” (40) spent 0 milliseconds in PostExecute. 12/24/2008 6:18:07 PM 12/24/2008 6:18:07 PM
The component “OLE DB Destination” (58) spent 0 milliseconds in PostExecute. 12/24/2008 6:18:07 PM 12/24/2008 6:18:07 PM
The component “Derived Column” (40) spent 0 milliseconds in ProcessInput. 12/24/2008 6:18:07 PM 12/24/2008 6:18:07 PM
The component “OLE DB Destination” (58) spent 1591 milliseconds in ProcessInput.
The component “OLE DB Source” (1) spent 3244 milliseconds in PrimeOutput filling buffers on output “OLE DB Source Output” (11).
Below is an example package to see the execution tree in action:
Note:
Execution trees can’t been seen until the package is executed. When executed, the execution trees appear in the Log Events window in Business Intelligence Development Studio (BIDS) or written to the log providers. Runing the above package and will provide the different execution trees below.
Analyzing the execution trees provides understanding of how a buffer is created and utilized by SSIS. For In the above example, how SSIS creates buffer for OLE DB source was observed and the same buffer being used by Derived Column transformation as it is a row/synchronous transformation. Separate sets of buffers are being created for Sort and Union All transformations as they are Blocking and Partially Blocking transformations respectively and need additional buffers to store their outcome.
In the next article in this series will continue exploration of the Internals of SSIS and look at Buffer Management.