Today, as I was working on a project, I found an opportunity to use the For Loop Container. The basic setup, was three tables, A, B, C for simplicity.
Table A is the Header, Table B a sub-header related to table A via an ID, and Table C related to Table B via an id. I wanted to loop through the Id’s in table B one at a time due to the amount of data. I pulled in TableAId, MinTableBId and MaxTableBId into an object variable, Using For Each Loop Container, I wanted to loop through each TableAId and then loop through each TableBId, pulling records from Table C one at a time. I decided to drop a For Loop Container into the For Each Loop Container. Initially, there was no issue, everything looped through. As the iterations increased, my laptop stopped responding. I was able to see the memory was sitting at 500 megs…then 600… eventually topping out around 850 megs (There is only 1 gig of RAM on the laptop I use at work.). By this time, I had to kill the task.
I thought maybe it was a fluke, so I restarted (when in doubt restart right=)). Cleaned out the tables and ran again. Same result.
After ripping out the for loop, and using a different technique, 150 megs of RAM was consumed. I plan on doing some more tests to see if I can find more useful information for everyone, but I wanted to post this as a caution to be careful with a For Loop Container. This is with SQL 2005 SP2.
I wanted to provide some quick information on the post above. SSIS uses buffers to manage the work that it handles in memory. You will not find these documented anywhere. You just have to know the person(s) who wrote that piece in SSIS. In SQL Server 2005 the buffers used inside of SSIS are as follows; Physical, Prime, Column View, Virtual. Now depending on how the task blocking or non blocking it utilizes the buffers in different ways. One thing that is important is the sheer size of the graph with SSIS. (Graph is the official term for the work/data flow orchestrations in SSIS) For performance and memory reasons to perf tune your package you need to size your graph to your buffer size that you setup in SSIS. Engine threads and work/transformation types will cause this number to move around a bit. You may also want to setup up some disk buffer on your laptop if you need to run the package on the 1 GB RAM system. By the sounds of the task we are holding multiple datasets in the prime buffers and the column view buffer as well. This will cause RAM swelling. As the data is kept in more than one buffer. I hope that my explain is sorta clear. The most important thing in SSIS is sizing your graph to your buffer. You can always pre stage your data in memory (included in SQL 2008 as Source and Destination Buffers) with a small amount of effort as well.