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)
Using GC Efficiently – Part 2
This is configured through the config file for the executable (Client applications – Web Applications are a little different). These config files are located in the following locations:
C:\program files\Microsoft SQL Server\90\DTS\Binn
C:\program files\Microsoft SQL Server\90\DTS\Binn <-- 64bit version
C:\program files (x86)\Microsoft SQL Server\90\DTS\Binn <-- 32bit version
We are interested in three config files: dtexec.exe.config, dtshost.exe.config and dtsdebughost.exe.config
Lets look at dtexec.exe.config:
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.
Lets see how this changed with SSIS 2008 (this would be in the following path: C:\program files\Microsoft SQL Server\100\DTS\Binn):
This would put us in the Server GC where we don’t use Concurrent. The following blog talks about the settings used with 2008.
Configuring .NET for running SSIS packages from custom applications
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