SQL Server SMO Assemblies Leak in 64-bit Environments

Recently I was faced with having to debug a service application that was gobbling memory at the rate of 70 MB per minute. The service in question was using the SQL Server SMO API to query SQL Server databases for a number of things, including whether the service was running or not. To compound matters, it was working fine in a development environment (isn’t that always the way?), as well as some other servers.

The first thing I did was look at the code. That was not much help, as the leak was not obvious. In short, all the objects were being disposed correctly, and no unmanaged objects were being left around.

I hit on the idea of then having a look at a crash dump. To get that, on the server where the service was leaking. opened Task Manager, selected the process and right-clicked then chose Create Dump File.

Once I had the dump file, I opened it up in Visual Studio 2010. I then ran the debugger, but there were issues with the bit-ness (i.e. 32-bit CLR 2 dump running in a CLR 4 application). In short, I could not load the SOS.dll (Son of Strike – I am sure there a good reason for this name – maybe I should Google it?) that I needed so that I could have a look and see what was chewing the memory.

So instead I downloaded the 32-bit version WinDbg package from the Microsoft Site. Once I had that installed I was ready to go.

I started WinDbg, selected File –> Open Crash Dump, navigated to the crash dump and opened it.

image

Next I entered the following command to load the SOS.dll

.load C:WindowsMicrosoft.NETFrameworkv2.0.50727sos.dll

The next step was to get a summary of the number and size of the objects in the dump.

!DumpHeap –stat

When we had a look at the output, we noticed that there were a large number of SQLConnections and SQLCommand objects, so we zoned in on those by using the following command:

                      !DumpHeap –type System.Data.SqlClient.SqlCommand

                      !DumpHeap –type System.Data.SqlClient.SqlConnection

This showed that there were 966 SQLCommand objects, and 247 SQLConnection objects.

Some other information then started to help out:

  • The server where it was failing was a Windows Server 2008 R2
  • An older version of the SMO was installed (File Version 10.0.1600)
  • The server where the service was working was a Windows Server 2008 Standard 32-bit server

The upshot of all of this is that when we got the service to load a later version of the SMO (from SQL Server 2008 R2 – File Version 10.50.1600.1), the service stopped chewing memory, and settled at 32 MB of usage.

Chalk that one up to experience.

Richard

Richard is a Director and the principal Consultant at Dev iQ Pty Ltd. He specialises in SharePoint, Team Foundation Server/Visual Studio and .NET Development.

Subscribe to richard angus

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!