What are the performance concerns or the issues that we might face when updating AutoAdjustBufferSize property to true from False in SSIS package task

Question

Hi ,

We want to improve the performance of one of the task in the SSIS package . So , when we tried different approaches we found out that the performance increased when we update the below parameter in one of the SSIS dataflow Task.

Before :

AutoAdjustBufferSize property= False

After

AutoAdjustBufferSize property= true

we could see a huge improvement in the performance and full load is completed in very less time than before. So we want to understand the pros and cons of this change and want to know if there are any steps we need to follow before we deploy this change to production.

Answer 1

Hi @Nalam, Leela Naga Vivek,

The data flow engine begins the task of sizing its buffers by calculating the estimated size of a single row of data. Then it multiplies the estimated size of a row by the value of DefaultBufferMaxRows to obtain a preliminary working value for the buffer size.

• If AutoAdjustBufferSize is set to true, the engine data flow engine uses the calculated value as the buffer size, and the value of DefaultBufferSize is ignored.
• If AutoAdjustBufferSize is set to false, the engine data flow engine uses the following rules to determine the buffer size.
  • If the result is more than the value of DefaultBufferSize, the engine reduces the number of rows.
  • If the result is less than the internally-calculated minimum buffer size, the engine increases the number of rows.
  • If the result falls between the minimum buffer size and the value of DefaultBufferSize, the engine sizes the buffer as close as possible to the estimated row size times the value of DefaultBufferMaxRows.

For more details about SSIS performance, you may refer to below.

Improving data flow performance with SSIS AutoAdjustBufferSize property

Data Flow Performance Features

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.