Increasing Throughput of Pipelines by Splitting Synchronous Transformations into Multiple Tasks

Authors: Sedat Yogurtcuoglu, Henk van der Valk, and Thomas Kejser

Technical Reviewers: David Noor, Bob Bojanic, Xiaoning Ding, An Liu, Carl Rabeler, Robert Zare

Editor: Beth Inghram

Test Setup

We performed the tests using SQL Server 2008 R2 RC0 running on a 96-core Unisys ES7000 Server. To eliminate other variables, we used simple package design that reads from a flat file source, calculates data in one or more Derived Column tasks, and then sends the result to a Row Count task transformation.

Before we ran the Derived Column transformation, we needed a baseline of the throughput of reading a file without transformations. To measure this, we used the data source and a row count.

Fig. 1: Baseline without transformations

The input file contained 1 million rows with 96 columns and has a size of 1 GB. It took 23 seconds, with a throughput of 50 MB per second, to read the data into a row count.

The Integration Services flat file reader retrieved data with 128 KB Read IOPS, which was completed within 1 millisecond. The average CPU utilization of the the DTEXEC process was less than 5 percent. There was room to spare for our transformations, and there was no I/O bottleneck.

Next, we established the baseline for the Derived Column task. Initially, we tested with 96 transformations in a single Derived Column task. The package we ran looks like this.

Fig. 2: One derived column task

The run time was 56 seconds, and throughput was 20 MB per second.

We then proceeded to split the transformations into more Derived Column tasks. For example, we ran a package like this.

Fig. 3: Derived Column task package

Each Derived Column task included 24 tranformations, or a quarter of the original 96 transformations. The run time was 24 seconds, and throughput was 47 MB per second.The following table and graph show the tests we did with varying numbers of Derived Column tasks used to perform the 96 transformations.

* DC stands for derived column

** The tests were performed on a 96-core Unisys ES7000/7600R server

Fig. 4: Measuring derived columns (DC) and duration

Why This Works

Even inside a single execution tree in Integration Services you can increase parallelism. As long as source throughput keeps up, Integration Services will spawn multiple buffers (up to five for sources, more for asynchronous transformations) in a single execution tree.

Fig. 5: Multiple buffers within an execution tree

The Derived Column task performs each column transformation serially inside a single task on the currently active buffer. However, when you split the column transformations into multiple Derived Column tasks, each individual column transformation can now be fully parallelized across multiple buffers. As you will notice from the test results, there is a sweet spot around four or five buffers where throughput reaches the peak. Because the data source cannot provide more than five buffers concurrently, five is also the maximum amount of added parallelism we can gain in this scenario by splitting into multiple Derived Column tasks. If you have a scenario with multiple sources, this sweet spot may be different in your case.

Conclusion

Synchronous tasks do NOT add a new execution tree to the execution plan of the Integration Services package – and hence, you would not expect synchronous tasks chained together to increase throughput of a pipeline. However, even inside one synchronous execution tree, you can increase parallelism by splitting the transformation into multiple task steps.

If you have a Derived Column task that contains complex operations, splitting the work in the Derived Column task into multiple tasks can improve overall throughput performance.