SSIS FineTune Details:
When you architect data integration solutions, your design decisions not only determine how successfully your solution meets functional requirements, but also how well your solution meets performance requirements. To make the right performance design decisions, you need to understand the performance architecture of your data integration tool and, just as importantly, the techniques that enable you to maximize the tool’s utilization of system resources such as memory and CPU. Microsoft® SQL Server™ 2005 Integration Services (SSIS) provides full-featured data integration and workflow engines coupled with a rich development environment for building high-performance data integration solutions. SSIS provides a variety of optimization opportunities to help you maximize resource utilization while successfully meeting the needs of your specific data integration scenario.
The following topics which have been covered are
1) Buffer-Oriented Architecture
2) Buffer Sizing
4) Lookup Transformation
- Buffer-Oriented Architecture
Behind the scenes, the data flow engine uses a buffer-oriented architecture to efficiently load and manipulate datasets in memory. The benefit of this in-memory processing is that you do not need to physically copy and stage data at each step of the data integration. Rather, the data flow engine manipulates data as it is transferred from source to destination.
As this data flows through the pipeline, SSIS attempts to reuse data from prior buffers as much as possible when additional operations are performed.
2.1 Buffer-Oriented Architecture for Source
Sources are a special type of asynchronous component. For example, an RDBMS source component creates two types of buffers: one for the Success output and one for the Error output.
2.2 Buffer-Oriented Architecture for Destination
By contrast, destinations are a special type of synchronous component. You will see the interactions of source and destinations components when you examine the Execution Trees of a package.
2.3 Buffer-Oriented Architecture for Destination for Transformations
Transformations are categorized into 3 types
a) Row Transformations
b) Partially blocking transformations
c) Blocking transformations
How buffers are used and reused depend on the type of transformations that you use in a pipeline.
2.3.1 Row Transformations – Row transformations either manipulate data or create new fields using the data that is available in that row. Examples of SSIS components that perform row transformations include Derived Column, Data Conversion, Multicast, and Lookup. While these components might create new columns, row transformations do not create any additional records. Because each output row has a 1:1 relationship with an input row, row transformations are also known as synchronous transformations. Row transformations have the advantage of reusing existing buffers and do not require data to be copied to a new buffer to complete the transformation.
• 2.3.2 Partially blocking transformations- Partially blocking transformations are often used to combine datasets. They tend to have multiple data inputs. As a result, their output may have the same, greater, or fewer records than the total number of input records. Since the number of input records will likely not match the number of output records, these transformations are also called asynchronous transformations. Examples of partially blocking transformation components available in SSIS include Merge, Merge Join, and Union All. 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.
• 2.3.3 Blocking transformations- Blocking transformations must read and process all input records before creating any output records. Of all of the transformation types, these transformations perform the most work and can have the greatest impact on available resources. Example components in SSIS include Aggregate and Sort. Like partially blocking transformations, blocking transformations are also considered to be asynchronous. Similarly, when a blocking transformation is encountered in the data flow, a new buffer is created for its output and a new thread is introduced into the data flow.
- Buffer Sizing
In addition to using row transformations where possible to limit the number of buffers that are created and used, within SSIS you have the ability to influence buffer sizing; that is, the number of records that are read into a buffer. Your overall goal is to pass as many records as possible through a single buffer while efficiently utilizing memory.
3.1 Influencing buffer sizing
At execution time before the data is read from the sources, SSIS automatically tunes buffer sizes to achieve maximum memory utilization based on a series of input parameters. To help SSIS do the best job it can when sizing buffers, you need to be aware of the following input parameters.
• Estimated Row Size – Estimated Row Size is not a specific SSIS setting. Rather, it is something that SSIS calculates based on the metadata that it collects about your source data at design time. You can shrink the row size by identifying the smallest possible data types for all of your columns as early in the data flow as possible. This is especially important for flat file data sources because each column is automatically read into SSIS as a string data type unless you configure the column’s data type explicitly.
• DefaultMaxBufferRows – DefaultMaxBufferRows is a configurable setting of the SSIS Data Flow task that is automatically set at 10,000 records. SSIS multiplies the Estimated Row Size by the DefaultMaxBufferRows to get a rough sense of your dataset size per 10,000 records. You should not configure this setting without understanding how it relates to DefaultMaxBufferSize.
• DefaultMaxBufferSize – DefaultMaxBufferSize is another configurable setting of the SSIS Data Flow task. The DefaultMaxBufferSize is automatically set to 10 MB by default. As you configure this setting, keep in mind that its upper bound is constrained by an internal SSIS parameter called MaxBufferSize which is set to 100 MB and cannot be changed.
• MinBufferSize – While MinBufferSize is not configurable, it is still important to know about it because SSIS uses this internal parameter to gauge whether you have set the DefaultMaxBufferSize too low. MinBufferSize is defined by the granularity of your operating system’s virtual memory allocation functionality. Typically, this is set to 65,536 bytes, but it differs from machine to machine.
Depending on your configured values for these input parameters, SSIS tunes buffer sizes at execution time using one of the following scenarios.
• Scenario 1 – When Estimated Row Size * DefaultMaxBufferRows exceeds MaxBufferSize; SSIS reduces the number of rows that will be stored in a given buffer to manage the memory footprint.
For example, if SSIS calculates an Estimated Row Size of 15,000 bytes per record, then the anticipated buffer size would be calculated as 15,000 bytes/record * 10,000 records, which is approximately 143 MB and about 1.5 times greater than the DefaultMaxBufferSize of 100 MB. Because the anticipated size exceeds the DefaultMaxBufferSize, SSIS reduces the number of records per buffer approximately by a factor of 1.5 to get below the 100 MB threshold. In this scenario, each buffer is sized to hold approximately 6,600 records. Keep in mind that when this adjustment takes place, SSIS does not know how many records you have in your source file. When the data is actually processed, SSIS creates as many instances of the buffer as necessary to hold the source data set. Continuing with the example, if you have 200,000 source records, SSIS creates approximately 30 buffer instances of that buffer type. A buffer type just refers to a buffer’s columnar structure. For example, a buffer with columns A, B, C will be known to SSIS as a buffer with type ABC. In practice, SSIS actually goes one step further and assigns each buffer type a numeric value.
• Scenario 2 – When Estimated Row Size * DefaultMaxBufferRows is less than MinBufferSize; SSIS increases the number of rows that will be stored in a given buffer to maximize memory utilization.
For example, if your Estimated Row Size is 5 bytes per record (much smaller than the previous example), 5 bytes/record* 10,000 records, is approximately 48 KB which is less than a MinBufferSize of 64 KB. Because the anticipated size is less than the MinBufferSize, SSIS slightly increases the records to reach the 64 KB threshold.
• Scenario 3 –If the Estimated Row Size * DefaultMaxBufferRows is somewhere in between MinBufferSize and DefaultMaxBufferSize, then SSIS attempts to size the buffer as closely possible to result of Estimated Row Size * DefaultMaxBufferRows using a multiple of the MinBufferSize to increase memory utilization.
For example, if your Estimated Row Size is 500 bytes per record, 500 bytes/record* 10,000 records, is approximately 4.8 MB which is less than DefaultMaxBufferSize of 10 MB, but greater than a MinBufferSize of 64 KB. In this scenario, SSIS will make adjustments to size the buffer as closely as possible to 4.8 MB.
3.2 Buffer guidelines
In practice, you must test these settings based on your own environment, but you can start with the following general guidelines.
• Reduce your Estimated Row Size as much as possible by removing any unnecessary columns and configuring data types correctly. Any opportunity that you have to reduce the size of the source data set before operations begin saves memory resources.
• Start with the SSIS default settings for DefaultMaxBufferRows and DefaultMaxBufferSize. Turn on package logging with the BufferSizeTuning property enabled. This property adds information to the log that shows you where SSIS has adjusted the buffer size. You will see entries like the following
• If your data integration is similar to Scenario 1, you will see: Rows in buffer type 0 would cause a buffer size greater than the configured maximum. There will be only 383 rows in buffers of this type.
• If your data integration is similar to Scenario 2, you will see: Rows in buffer type 3 would cause a buffer size less than allocation minimum, which is 65536 bytes. There will be 1365 rows in buffers of this type. Note that 65536 bytes is the MinBufferSize of the machine on which this package executes.
• Tweak the values for DefaultMaxBufferRows and DefaultMaxBufferSize to get as many records into a buffer as possible. Setting these values too low causes SSIS to create many small buffers instead of fewer but larger buffers, which is a great scenario if you have enough memory.
• As you tweak the DefaultMaxBufferRows and DefaultMaxBufferSize, realize that once the MaxBufferSize is exceeded, the setting for MaxNumberofRows no longer matters because SSIS always scales down the number of records per buffer to maximize memory utilization.
• Note that the DefaultMaxBufferRows and DefaultMaxBufferSize are configured separately for each Data Flow task. When you integrate data from multiple data sources using a single Data Flow task, these two settings will only impact the data source components and transformations in that task. Also note that the determination of how many rows per buffer is done per buffer type.
- Parallelism and Optimization
Parallelism is a great technique to improve the performance of your data integration operations. SSIS natively supports the parallel execution of packages, tasks, and transformations. The trick to successful parallelism is to configure operations within the constraints of your system resources.
4.1 Configurable settings
Within SSIS, the control flow for each package is controlled by a setting called MaxConcurrentExecutables, which specifies the maximum number of SSIS threads that can execute in parallel per package. By default, this is set to -1, which translates to the number of logical machine processors plus 2.
If SSIS runs on a dedicated server and you have a lot of operations that run in parallel, you will likely want to increase this setting if some of the operations do a lot of waiting for external systems to reply. On the other hand, if you do not have a dedicated SSIS machine and your data integration application runs alongside several other applications, you may need to reduce this setting to avoid resource conflicts.
4.2 Design approaches
As you design packages for parallelism, you need to decide whether to run some or all of the operations in the package in parallel. As with buffer sizing decisions, decisions about parallelism are best made when you take into account available system resources.
Consider the tradeoffs of different design approaches that apply parallelism to a package
One of the most commonly used transformations is the Lookup transformation. There are several techniques for getting optimal performance from a Lookup transform in SSIS. A few of these are surprising the first time you see them. This article discusses some tuning techniques that we have found can be important in different situations.
A common need in a data warehousing context would be to look up a surrogate key based on one or more business keys, before a new row is inserted into the warehouse. The screen shot below shows a highly simplified example: An arriving order record contains a customer name and phone number. Before an order can be inserted in the database, the customer name and phone number must be looked up to find the correct customer key. This simplified example will be used as the basis for the discussion in this article.
Most designers would use the obvious default behavior of the Lookup transform, selecting a table or view to look up in, as shown below.
5.1 Be selective about the lookup columns
The example scenario showed selecting data from a table. After naming the table, the designer also chooses what columns will be used in the lookup and what columns will be returned as a result of the lookup. This is done in the Columns tab of the Lookup Transformation Editor. The figure below shows that the columns C_NAME and C_PHONE will be referenced from the table, and the data from C_CUSTKEY will be returned.
Only three columns from the ORDERS table are used in this example. However, choosing a table will be interpreted as “SELECT *”. This will cause the transformation to fetch un-needed data in the other columns that do not participate in lookup operation. That is a waste of effort and memory. We recommend choosing “Use results of an SQL query” instead of naming a table, and in the query selecting only the columns that are used.
This in turn changes the column mappings. Now it is clear that only the desired columns have been selected. In our experience this can lead to a substantial gain in Lookup performance. In one case we saw recently, using this technique resulted in a doubling of the package speed.
5.2 Enable memory restriction
Reducing the data returned by the Lookup has the advantage that less work is required to return the data, and it also reduces memory usage by SSIS. This is the next area for discussion: memory usage. Being more selective about the rows returned is one technique for saving memory; another is to enable memory restriction in the Lookup transform.
Two things happen when memory restriction is enabled on a Lookup: First, the amount of memory that the Lookup is allowed to use is limited. A cache policy is used and new rows are added to the cache on demand. Second, the new rows are added to the cache individually. In other words, SSIS will query the relational database whenever data for a row cannot be located in the internal cache kept by SSIS. These are singleton (single-row) queries, unlike the large set-based table queries that occur when memory restriction is not enabled. The next figure shows where in the Lookup Transformation Editor the setting is made. In the example, 20 megabytes are allowed for the Lookup cache. This is user-settable and must be adjusted to the needs of each lookup scenario.
The benefit of enabling memory restriction is that large lookups can be performed which might not be possible otherwise. The cost is that singleton queries are used, which collectively are usually slower than a single table query. However, we have found that this performance cost is not always as bad as one might expect, if appropriate indexes are present on the lookup table. In one recent experiment, we ran a package with and without memory restriction in a lookup. The lookup table for this package had millions of rows, and about 10% of them were actually referenced by the incoming data. When there was an index on the lookup table, the package with memory restriction ran only 25% longer than the package without memory restriction. When there was not an index on the lookup table, the package with memory restriction failed to complete after 24 hours. Consider a case where incoming record must be looked up against a large table, but the incoming records match only a few of the records in the lookup table. It might actually be faster to enable memory restriction, thus avoiding a load of the entire table, and letting the few records be fetched individually. A decision to use memory restriction for this reason must be considered in the specific context of each application.
To make the right performance design decisions, you need to understand the performance architecture of your data integration tool and, just as importantly, the techniques that enable you to maximize the tool’s utilization of system.