I was troubleshooting an SSIS Out of Memory issue the other day which led us into how the .NET Garbage Collector (GC) works. When debugging it, I found that we were using the Workstation GC with Concurrent GC On (this is the default for .NET applications). The following blog discusses the different flavors of the GC (Workstation with Concurrent GC On, Workstation with Concurrent GC Off and Server GC)
That doesn’t really tell us a lot. Well, maybe it does. From a .NET Configuration file perspective, we have a few default settings. gcServer is not enabled by default and gcConcurrent is enabled by default. So, with the above config file, that would place us in the “Workstation with Concurrent GC On” mode.
Where does that leave us? For SSIS packages, you will probably see a performance improvement by using the Server GC as opposed to the Workstation GC. This will give us more threads for the GC to work with and also changes how we allocate segments within the process. This is only if we are running on a box that has multiple processors or Cores. For a single processor machine, you will want to stick with the Workstation GC.
There are also Pro’s and Con’s with this. Concurrent with the Workstation GC will allow GC to occur without interrupting the process. The Server GC will always stop processing when the GC kicks in, so this could result in some performance differences. Ultimately, if you look to change these settings with SSIS 2005, you will want to be sure to test the changes thoroughly to make sure it works as desired in your environment. You may find that the Workstation GC works better for you.
Adam W. Saxton | Microsoft SQL Server Escalation Services