SQL 2016 SSIS new feature AutoAdjustBufferSize

If you have not already seen in the What’s New in Integration Services list there is a new property for Data Flows that is called AutoAdjustBufferSize.  Why is this important and what does it actually do?

First, let’s remember that in SQL 2014 we had a max DefaultBuffersize of 100MB and to try and optimize this we would use calculations to figure out or row size and how to set the DefaultBufferMaxRows.   I’ve covered these techniques in the past in this video or you can read about it via Ginger Grant’s Blog.

Now in SQL 2016 we don’t have to worry as much about trying to perform these calculations as setting the AutoAdjustBufferSize to true will take care of this for us.   So what is happening when I set this to true.  SSIS will now ignore the DefaultBufferSize property and set the buffer size based off of the following calculation of DefaultBufferMaxRows * rowsize.  When performing this calculation the rowsize is actually calculated at runtime, while the DefaultBufferMaxRows is a property value you specify either when designing the package or through an expression and can be tied to a parameter.

I do have a big caveat to this and with doing some extensive testing you need to make sure that if you have a wide file and try to match SQL Server max row group size of 1,048,576 you can get an error of “Buffer failed while allocating”  due to not enough free memory on your system to auto adjust the buffer size (See Below).  So as with anything I recommend you test, test and then test again as I have seen some significant performance increase by changing this setting but also the dangers of not having enough memory.

Buffer error:


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s