ASP.NET Memory Leak/High Memory using DataTable

Question

I have an asp.net website that is being hosted in IIS. I have recently noticed that with large result sets being returned from the database, that the memory for IIS Worker Process just keeps growing (about 400MB each time the query is run). If a few of these large queries happen to be run at the same time it can just eat away at the memory (have seen it reach 5GB) and the server slows right down.

I have narrowed it down to a single line of code, when the data is loaded into the DataTable.

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
using(DataTable dataTable = new DataTable())
{
    command.CommandType = System.Data.CommandType.StoredProcedure;
    connection.Open();

    using (SqlDataReader reader = command.ExecuteReader())
    {
        // Memory Spikes on dataTable.Load
        dataTable.Load(reader);
    }
}

What I dont understand is that the memory being allocated to the DataTable doesn't seem to be disposed of as I would expect. When the DataTable falls out of scope or when the webpage is navigated away from or even when the user logs out of the site, the memory stays at the same level. This is obviously an issue in a multi user system.

I have used a memory profiler and it is holding thousands of strings in memory which are the results of the query held in the DataTable, but I am not really sure where to go from here? Am I misunderstanding how I should be handling this?

Answer 1

It's not the issue. It's how Garbage Collector works. When you dispose of an object it is not removed from memory right away. It's just marked as ready to be disposed of for garbage collector.

This is a quote from MS Exams book

The stack is automatically cleared at the end of a method. The CLR takes care of this and you don’t have to worry about it. The heap is another story—it is managed by the garbage collector. In unmanaged environments without a garbage collector, you have to keep track of which objects were allocated on the heap and you need to free them explicitly. In the .NET Framework, this is done by the garbage collector.

The garbage collector works with a mark and compact algorithm. The mark phase of a collection checks which items on the heap are still being referenced by a root item. A root can be a static field, a method parameter, a local variable, or a CPU register. If the garbage collector finds a “living” item on the heap, it marks the item. After checking the whole heap, the compact operation starts. The garbage collector then moves all living heap objects close together and frees the memory for all other objects. For doing this, the garbage collector has to make sure that no state is changing while performing all the marking and compacting. Because of this, all threads are frozen while doing a collect operation. It also has to make sure that all references to living objects are correct. After moving objects around, the garbage collector will fix all existing references to objects.

You can try to force garbage collector to perform cleaning using:

GC.Collect(); 
GC.WaitForPendingFinalizers(); 

Moreover, GC uses multiple generations and only Generation 0 is easy to clean-up and GC clean it up first. Only then GC decided that it cannot release enough memory it will start to process the other generations. And moving to these generations could create latency.

UPDATE: You can try also divide your big dataset to small chunks and retrieve them accordingly.