SSIS 2019 AutoAdjustBufferSize causes buffer allocation error

Question

To increase my ETL performance, I have enabled "AutoAdjustBufferSize" property on my data flow tasks. However, it is not allocating enough buffer to the memory I need.

Check out what SSIS tells me...

Information: The buffer manager failed a memory allocation call for 1954545664 bytes, but was unable to swap out any buffers to relieve memory pressure. 2 buffers were considered and 2 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.

Information: Buffer manager allocated 1864 megabyte(s) in 1 physical buffer(s).

Error: The system reports 36 percent memory load. There are 34156761088 bytes of physical memory with 21535158272 bytes free. There are 4294836224 bytes of virtual memory with 1996070912 bytes free. The paging file has 39257034752 bytes with 24542248960 bytes free.

Several questions on this:

1. Why are there only 2 buffers allows here? (max buffer rows is set to 1048576)
2. Why does it say BOTH that it allocated the same bytes that it says it couldn't allocate?

To note:

• It works when I manually set the buffer row size to the default (104857360)
• All development and source data files live on a network server. I'm using visual studio in my local computer to access the development/source files

Answer 1

I disagree with the statement "bypassing upper limit specified by max buffer size and max buffer rows" from @Ferdipux. It bypasses max buffer size, but it does not bypass max buffer rows.

I'll quote the official MS statement:

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.

Based on this same site, the max SSIS buffer memory is 2GB. Your message says 2 buffers, probably because you have 2 tasks. This means that you can have 1GB max buffer per task.

You have to calculate your row size (see this blog) and based on that, you can decide your max rows. By the looks of it your table is very wide.

For example:
Your row size is 1000 bytes.
2147483647 / 1000 bytes = 2147483.647
2147483.647 / 2 tasks = 1073741.8235
So to be on safe side round it up to 1 070 000 rows.