Improving the Performance of the Data Flow

The MaxConcurrentExecutables property is a property of the package itself. This property defines how many tasks can run simultaneously. The default value is -1, which means the number of physical or logical processors plus 2.

To understand how this property works, consider a sample package that has three Data Flow tasks. If you set MaxConcurrentExecutables to 3, all three Data Flow tasks can run simultaneously. However, assume that each Data Flow task has 10 source-to-destination execution trees. Setting MaxConcurrentExecutables to 3 does not ensure that the execution trees inside each Data Flow task run in parallel.

The EngineThreads property is a property of each Data Flow task. This property defines how many threads the data flow engine can create and run in parallel. The EngineThreads property applies equally to both the source threads that the data flow engine creates for sources and the worker threads that the engine creates for transformations and destinations. Therefore, setting EngineThreads to 10 means that the engine can create up to ten source threads and up to ten worker threads.

To understand how this property works, consider the sample package with three Data Flow tasks. Each of Data Flow task contains ten source-to-destination execution trees. If you set EngineThreads to 10 on each Data Flow task, all 30 execution trees can potentially run simultaneously.

A number of data flow components use queries, either when they extract data from sources, or in lookup operations to create reference tables. The default query uses the SELECT * FROM <tableName> syntax. This type of query returns all the columns in the source table. Having all the columns available at design time makes it possible to choose any column as a lookup, pass-through, or source column. However, after you have selected the columns to be used, you should revise the query to include only those selected columns. Removing superfluous columns makes the data flow in a package more efficient because fewer columns create a smaller row. A smaller row means that more rows can fit into one buffer, and the less work it is to process all the rows in the dataset.

To construct a query, you can type the query or use Query Builder.

Sorting is inherently a slow operation, and avoiding unnecessary sorting can enhance the performance of the package data flow.

Sometimes the source data has already been sorted before being used by a downstream component. Such pre-sorting can occur when the SELECT query used an ORDER BY clause or when the data was inserted into the source in sorted order. For such pre-sorted source data, you can provide a hint that the data is sorted, and thereby avoid the use of a Sort transformation to satisfy the sorting requirements of certain downstream transformations. (For example, the Merge and Merge Join transformations require sorted inputs.) To provide a hint that the data is sorted, you have to do the following tasks:

• Set the IsSorted property on the output of an upstream data flow component to True.

• Specify the sort key columns on which the data is sorted.

For more information, see How to: Sort Data for the Merge and Merge Join Transformations.

If you have to sort the data in the data flow, you can improve performance by designing the data flow to use as few sort operations as possible. For example, the data flow uses a Multicast transformation to copy the dataset. Sort the dataset once before the Multicast transformation runs, instead of sorting multiple outputs after the transformation.

For more information, see Sort Transformation, Merge Transformation, Merge Join Transformation, and Multicast Transformation.

When you use an OLE DB source to retrieve data from a view, select "SQL command" as the data access mode and enter a SELECT statement. Accessing data by using a SELECT statement performs better than selecting "Table or view" as the data access mode.

The Aggregate transformation includes the Keys, KeysScale, CountDistinctKeys, and CountDistinctScale properties. These properties improve performance by enabling the transformation to preallocate the amount of memory that the transformation needs for the data that the transformation caches. If you know the exact or approximate number of groups that are expected to result from a Group by operation, set the Keys and KeysScale properties, respectively. If you know the exact or approximate number of distinct values that are expected to result from a Distinct count operation, set the CountDistinctKeys and CountDistinctScale properties, respectively.

If you have to create multiple aggregations in a data flow, consider creating multiple aggregations that use one Aggregate transformation instead of creating multiple transformations. This approach improves performance when one aggregation is a subset of another aggregation because the transformation can optimize internal storage and scan incoming data only once. For example, if an aggregation uses a GROUP BY clause and an AVG aggregation, combining them into one transformation can improve performance. However, performing multiple aggregations within one Aggregate transformation serializes the aggregation operations, and therefore might not improve performance when multiple aggregations must be computed independently.

For more information, see Aggregate Transformation.

For information about optimizing the performance of the Fuzzy Lookup and Fuzzy Grouping transformations, see the white paper, Fuzzy Lookup and Fuzzy Grouping in SQL Server Integration Services 2005.

Minimize the size of the reference data in memory by entering a SELECT statement that looks up only the columns that you need. This option performs better than selecting an entire table or view, which returns a large amount of unnecessary data.

The Merge Join transformation includes the MaxBuffersPerInput property, which specifies the maximum number of buffers that can be active for each input at one time. You can use this property to tune the amount of memory that the buffers consume, and consequently, the performance of the transformation. The larger the number of buffers, the more memory the transformation uses and the better the performance is. The default value of MaxBuffersPerInput is 5, which is the number of buffers that works well in most scenarios. To tune performance, you may want to try using a slightly different number of buffers such as 4 or 6. If possible, you should avoid using a very small number of buffers. For example, setting MaxBuffersPerInput to 1 instead of 5 has a significant impact on performance. Also, you should not set MaxBuffersPerInput to 0 or less. This range of values means that no throttling occurs, and depending on the data load and the amount of memory available, the package may not complete.

To avoid a deadlock, the Merge Join transformation may temporarily increase the number of buffers it uses beyond the value of MaxBuffersPerInput. After the deadlock condition is resolved, MaxBuffersPerInput returns to its configured value.

For more information, see Merge Join Transformation.

The Slowly Changing Dimension Wizard and the Slowly Changing Dimension transformation are general-purpose tools that meet the needs of most users. However, the data flow that the wizard generates is not optimized for performance.

Typically, the slowest components in the Slowly Changing Dimension transformation are the OLE DB Command transformations that perform UPDATEs against a single row at a time. Therefore, the most effective way to improve the performance of the Slowly Changing Dimension transformation is to replace the OLE DB Command transformations. You can replace these transformations with destination components that save all rows to be updated to a staging table. Then, you can add an Execute SQL task that performs a single set-based Transact-SQL UPDATE against all rows at the same time.

Advanced users can design a custom data flow for slowly changing dimension processing that is optimized for large dimensions. For a discussion and example of this approach, see the section, "Unique dimension scenario," in the white paper, Project REAL: Business Intelligence ETL Design Practices.

When a package loads data to an instance of SQL Server on the same computer, use a SQL Server destination. This destination is optimized for high-speed bulk loads.

You may find that saving data to destinations takes more time than expected. To identify whether the slowness is caused by the inability of the destination to process data quickly enough, you can temporarily replace the destination with a Row Count transformation. If the throughput improves significantly, it is likely that the destination that is loading the data is causing the slowdown.

Perhaps the most useful custom log entry is the PipelineComponentTime event. This log entry reports the number of milliseconds that each component in the data flow spends on each of the five major processing steps. The following table describes these processing steps. Integration Services developers will recognize these steps as the principal methods of a PipelineComponent.

When you enable the PipelineComponentTime event, Integration Services logs one message for each processing step performed by each component. The following log entries show a subset of the messages that the Integration Services CalculatedColumns package sample logs:

The component "Calculate LineItemTotalCost" (3522) spent 356 milliseconds in ProcessInput.

The component "Sum Quantity and LineItemTotalCost" (3619) spent 79 milliseconds in ProcessInput.

The component "Calculate Average Cost" (3662) spent 16 milliseconds in ProcessInput.

The component "Sort by ProductID" (3717) spent 125 milliseconds in ProcessInput.

The component "Load Data" (3773) spent 0 milliseconds in ProcessInput.

The component "Extract Data" (3869) spent 688 milliseconds in PrimeOutput filling buffers on output "OLE DB Source Output" (3879).

The component "Sum Quantity and LineItemTotalCost" (3619) spent 141 milliseconds in PrimeOutput filling buffers on output "Aggregate Output 1" (3621).

The component "Sort by ProductID" (3717) spent 16 milliseconds in PrimeOutput filling buffers on output "Sort Output" (3719).

These log entries show that the data flow task spent the most time on the following steps, shown here in descending order:

• The OLE DB source that is named "Extract Data" spent 688 ms. loading data.

• The Derived Column transformation that is named "Calculate LineItemTotalCost" spent 356 ms. performing calculations on incoming rows.

• The Aggregate transformation that is named "Sum Quantity and LineItemTotalCost" spent a combined 220 ms—141 in PrimeOutput and 79 in ProcessInput—performing calculations and passing the data to the next transformation.