CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

SSIS 2005 and the .NET Garbage Collector

SSIS 2005 and the .NET Garbage Collector

  • Comments 1

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 files\Microsoft SQL Server\90\DTS\Binn

64bit
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:

<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 files\Microsoft SQL Server\100\DTS\Binn):

<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-custom-applications.aspx

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

Leave a Comment
  • Please add 4 and 6 and type the answer here:
  • Post