2

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
Community
  • 1
  • 1
Wichie Artu
  • 85
  • 1
  • 9

2 Answers2

2

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.

Scratte
  • 3,056
  • 6
  • 19
  • 26
Iviglious
  • 61
  • 4
  • 1
    Apologies for the late reply, but thank you for explaining the calculation. When I do performance testing and optimization on my ETL process, I'll implement and test it. – Wichie Artu Jun 23 '20 at 11:56
  • This was VERY helpful - especially both the blog and example. With this alone I was able to improve my performance by 20%. Appreciate the help @Iviglious! – Wichie Artu Sep 23 '20 at 15:23
1

The SSIS is telling you that it experienced a RAM pressure, requested 19+ GB of RAM with no avail, and then tried to swap out some buffers used. However, from 2 present buffers all 2 are used/locked, and cannot be swapped out.
The reason for this can be your setting AutoAdjustBufferSize=true, it allows to grow Data Flow buffer quite extensively, bypassing upper limit specified by max buffer size and max buffer rows settings. That is why buffer grows beyond the limit. Main purpose for this is to speed up data processing at cost of higher RAM utilization. It is ok when your data can flow fast through Data Flow Task, but if it is not the case - you might receive the error message you mentioned above.
Recommendation - set AutoAdjustBufferSize=false and experiment with buffer sizes which do not rise such error.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33
  • Thanks for your insight. I understand that AutoAdjust setting will bypass the limits I've set (though to my understanding it doesn't go past the limit of max buffer rows I set - does it?). What I DON'T understand is why it will only use 2 buffers to allocate that much RAM, and even if there were only 2 buffers, why it wouldn't increase the buffer sizes to match its needs. In my local computer (where I run the program), I have 30GB total of RAM (as detected in "information" statement). Why won't SSIS use it in the auto adjust setting? – Wichie Artu Dec 30 '19 at 18:04
  • 1
    @WichieArtu, Unfortunately, I can add little to the explanation above. Classical SSIS creates buffers minding the limits specified in *max buffer rows* and *max buffer size*. SSIS creates buffers with defined size. With *AutoAdjust* setting, it creates buffers beyond limits, but MS do not describe the math. Once buffer is created, it cannot be resized. – Ferdipux Jan 15 '20 at 06:58