First published on MSDN on May 07, 2009
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
http://blogs.msdn.com/maoni/archive/2004/09/25/234273.aspx
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:
32bit
C:program filesMicrosoft SQL Server90DTSBinn
64bit
C:program filesMicrosoft SQL Server90DTSBinn <-- 64bit version
C:program files (x86)Microsoft SQL Server90DTSBinn <-- 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:
<configuration>
<startup>
<requiredRuntime version="v2.0.50727"/>
</startup>
</configuration>
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.
<gcConcurrent> Element
http://msdn.microsoft.com/en-us/library/yhwwzef8(VS.80).aspx
<gcServer> Element
http://msdn.microsoft.com/en-us/library/ms229357(VS.80).aspx
Lets see how this changed with SSIS 2008 (this would be in the following path: C:program filesMicrosoft SQL Server100DTSBinn):
<configuration>
<startup>
<requiredRuntime version="v2.0.50727"/>
</startup>
<runtime>
<gcServer enabled="true"/>
<disableCommitThreadStack enabled="true"/>
<generatePublisherEvidence enabled="false"/>
</runtime>
</configuration>
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
http://blogs.msdn.com/michen/archive/2008/03/14/configuring-net-for-running-ssis-packages-from-...
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
