When it comes to optimizing the performance of an SSIS package, one of the main recommendations is to modify the data stream buffer so that as much data as possible can be loaded into a single buffer size.
According to Microsoft, for optimum performance, SSIS should be configured for optimal memory utilization by arranging buffers with as many lines as possible without exceeding the internal limit of 100 MB.
Buffer settings
Two default settings are used for loading data into memory, and they must all be aligned correctly to prevent data being sent from memory to disk.
DefaultMaxBufferSize – the default value is 10 MB. The maximum size is 100 MB, which SSIS stores under the name MaxBufferSize. This value can never exceed 100 MB. So adapt it to your environment.
DefaultMaxBufferRows – The default value is 10,000 rows. This parameter must be set in the same way as you win with the price is correct. To win, maximum buffer rows * (times) by default, the row size should be as close as possible to DefaultMaxBufferSize without exceeding it.
What size ?
To know what to put in the DefaultMaxBufferRows, you need to know the size of the lines. Remember that to win, the value must be as close to the size of DefaultMaxBufferSize without exceeding it.
If the buffer size is too large, the package will be slower because the lines will be cached on disk.
So make sure you don’t miss anything. How big is the line? There are several ways to find out. You can look at each field of the query being loaded by SSIS and, depending on the data type, add the value of all the fields. Fortunately, this isn’t the only way to determine row size. If your data source is SQL Server, you’re in luck, as system tables can help you determine its size. Here’s an example query, assuming your table name is “F_Fact” .
Exec sp_SpaceUsed ‘F_Fact’
To determine the size of your buffer for this entire table, take the number of (data * 1024) / Lines, as 100 MB is the maximum size you can define.
To calculate the row size, use the formula values 1402424/437418 * 1024 = 3283.088, or 3284 bytes per row.
If you set DefaultMaxBufferRows to 100 MB, which is the maximum and what I recommend in most cases, this is 104857600 bytes corresponding to the buffer size. Buffer size / Line size = DefaultMaxBufferRows. 104857600/3284 = 31929.84, so set DefaultMaxBufferRows to 31930.
If you’re using columns, you can obtain the same information by consulting the syscolumns. Using column length, it’s relatively easy to determine the appropriate size of your buffer, by adding up the column lengths. A word of caution. I don’t mean to imply that, since the information is available by table, you should choose Table or View in the SSIS source. On the contrary.
Always access data using a Select statement, because it works better.
