SSIS - out of memory error again

Question

I have cca 25 databases which I need to consolidate into 1 database. First I tried to build a ssis package which would copy all data from each table into one place but then I got error:

Information: The buffer manager failed a memory allocation call for 10485760 bytes, but was unable to swap out any buffers to relieve memory pressure. 1892 buffers were considered and 1892 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.

Then I realized this is not good idea and that I need to insert only new records and update existing ones. After that I tried this option:

• Get a list of all conn. strings
• foreach db, copy new records and update existing ones (those which need to be updated copy from source to temp table, delete them from destination and copy from temp to destination table)

Here's how data flow task looks like

In some cases data flow procceses more than million rows. BUT, I still get the same error - ran out of memory.

In task manager the situation is following:

I have to note that there are 28 databases being replicated on this same server and when this package is not running sql server is still using more than 1gb of memory. I've read that it's normal, but now I'm not that sure...

I have installed hotfix for SQL Server I've found in this article: http://support.microsoft.com/kb/977190 But it doesn't help... Am I doing something wrong or this is just the way things work and I am suppose to find a workaround solution?

Thanks,
Ile

Answer 1

I had this error when I made an aggregation that I merged into its own source table again.

This was not needed since grouping and counting had to be just one data stream. After shrinking it down to this one data stream, it did not crash anymore:

From this can be seen that merging is the bottleneck. From the mere buffer error, this could not be seen:

Information: The buffer manager failed a memory allocation call for 10482984 bytes, but was unable to swap out any buffers to relieve memory pressure. 2 buffers were considered and 0 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.

You did not know where the buffer error came from.

Answer 2

I found a solution and the problem was in SQL Server - it was consuming too much of memory. By default max server memory was set to 2147483647 (this is default value). Since my server has 4gb RAM, I limited this number to 1100 mb. Since then, there were no memory problems, but still, my flow tasks were very slow. The problem was in using Lookup. By default, Lookup selects everything from Lookup table - I changed this and selected only columns I need for lookup - it fastened the process several times.

Now the whole process of consolidation takes about 1:15h.

Answer 3

You might run into memory issues if your Lookup transformation is set to Full cache. From what I have seen, the Merge Join performs better than Lookup transformation if the number of rows exceed 10 million.

Have a look at the following where I have explained the differences between Merge Join and Lookup transformation.

What are the differences between Merge Join and Lookup transformations in SSIS?