SqlCommand.Dispose() not disposing the SqlParameters in it - Memory Leak - C#.NET

Question

I've a windows forms application with MS SQL Server 2005 as the back end. I have written code in the form to call few stored procedures using SqlConnection, SqlCommand objects and i properly dispose everything.

I've disposed sqlcommand object by calling

oSqlCommand.Dispose()

But i witnessed my application consuming huge amount of memory. I basically pass large XML files as SqlParameters.

I finally decided to memory profile it using RedGate Memory profiler and i noticed that the System.Data.SqlClient.SqlParameters are not disposed.

Any insights on this?

Thanks

NLV

Answer 1

I see this:

i properly dispose everything.

and this:

I've disposed sqlcommand object by calling oSqlCommand.Dispose()

However, those are mutually exclusive! If you call .Dispose() directly, you're doing it wrong. Specifically, you leave open the possibility an exception will make the program skip over the call to the Dispose() method. The "proper" way to dispose of a command creates it with a using block, like so:

using (SqlCommand cmd = new SqlCommand("sql string here"))
{
    // use the command here
} // compiler transforms your code to make sure .Dispose() is called here

Now, I gather from the question this isn't the main problem at the moment, but it is a point worth driving home.

As for the question about parameters: SqlParameters do not implement IDisposable. Therefore, you do not dispose them directly. They are an entirely managed resource, and that means they ar ecleaned up by the garbage collector at some point after they are no longer reachable. You don't have to do anything to clean them up yourself.

If you can seriously show that SqlParameter objects are hanging around long after they should, it means you are holding a reference to them somewhere. For example, perhaps you are "caching" old SqlCommand objects somewhere, which in turn hold on to all their parameters. Don't do that. Find and eliminate whatever still references the SqlParameters, and the garbage collector will clean them up for you.

Update:

After re-reading your question, it sounds like the xml parameters end up on the Large Object Heap. The garbage collector in .Net is generational – it doesn't clean up everything every time it runs. As an object moves to a higher generation, it's more likely to hang around a while. The Large Object Heap is basically the last generation, and it does not get cleaned up much at all. More than that, it does not get compacted ever, such that over time it fragments. This can cause a program to hold on to much more data than it needs. What you need to do is try to find a way to keep from loading the entire xml data for a parameter into memory, such that it never makes it to the large object heap. Use a file stream, or something similar instead.

Answer 2

Since SqlParameter is not IDisposable, it isn't an issue of disposing it; and normally there would be little benefit in tidying up the references etc, since it is still subject to the same GC.

If sounds like you have accidentally kept a reference to the SqlCommand. But if you are sure that you are done, you could try explicitly setting each .Value to null, and calling Clear() on the parameters list. But that is really just masking the fact that you are clinging onto a dead command.