Debugging .NET Memory leak calling StringBuilder.ToString() inside while loop

Question

Background:

I am downloading a large (>500mb) text file which contains lots of SQL statements which I need to run across a database. To achieve this, I am processing the file line by line until I find a full query and then execute it. When running this application, the logic inside the while loop uses more memory than anticipated.

I have removed the code which is running the query against the database - after debugging it doesn't seem the be what is causing the issue.

Code:

Below is some example code to demonstrate - obviously this is not the full program, but this is where I've narrowed the problem down to. Note that sr is a StreamReader which has been initialised to read from my MemoryStream.

StringBuilder query = new StringBuilder();

while (!sr.EndOfStream)
{
    query.AppendLine(await sr.ReadLineAsync());

    string currentQueryString = query.ToString();

    if (currentQueryString.EndsWith($"{Environment.NewLine}GO{Environment.NewLine}"))
    {
        // Run query against database

        // Clean up StringBuilder so it can be used again
        query = new StringBuilder();
        currentQueryString = "";
    }
}

For this example, let's say that every new line in the file could be between 1 and 300 characters long. Also 99% of the queries are INSERT statements containing 1,000 records (each record on a new line).

When I run the application:

I can see in my Windows Task Manager that as the application runs, the memory allocated to the app increases what looks like almost every iteration of the while loop. I placed a break point on currentQueryString = ""; and every time it gets hit (knowing that I've just read another 1,000 lines of the file into memory) I can see that memory used by the application increases (this time from using the Diagnostic Tools inside Visual Studio) anywhere from 100mb to 200mb roughly, however from taking snapshots each time the breakpoint is hit I can see that the Heap Size is barely changing, maybe a few hundred kb either way.

What I think is causing the issue:

My best guess at the moment is that the string currentQueryString = query.ToString(); line is somehow initialising a variable possibly in unmanaged memory which is not being released. One reason for this is I tested with the following code which removes calling toString() on the StringBuilder and the memory usage is drastically lower as it only increases by about 1-2mb or so for every 1,000 lines processed:

while (timer.Elapsed.TotalMinutes < 14 && !sr.EndOfStream && !killSwitch)
{
    query.AppendLine(await sr.ReadLineAsync());

    currentExeQueryCounter += 1;

    if (currentExeQueryCounter > 1000)
    {
        query = new StringBuilder();
        currentExeQueryCounter = 0;
    }
}

For debugging purposes only I added in GC.Collect() underneath currentQueryString = ""; in the first code snippet which completely resolved the issue (observed in both Visual Studio Diagnostic Tools and Task Manager) and I am trying to understand why this is and how I can best address this issue as I aim to run this as a serverless application which will be allocated a limited amount of memory.

Answer 1

To add to the very sensible answer from JonasH: The call to query.ToString() is probably costing you quite a bit. It is also an unnecessarily convoluted way to check for a line that says "GO". If you instead just compare the most recently read line to "GO", you can cut down on the ToString() calls. For example, something like this:

string line = await sr.ReadLineAsync();
query.AppendLine(line);

if (line == "GO")
{
    string currentQueryString = query.ToString();

    // Run query against database

    query.Clear(); // Clean up StringBuilder so it can be used again
}

Answer 2

Just increasing memory usage does not indicate a memory leak, the Garbage collector will run according to its own rules, for example when there is not sufficient memory. If inserting a GC.Collect resolves it there was probably never a leak to begin with.

Whenever there is a potential memory problem I would recommend using a memory profiler. This should allow you to trigger GCs at will, collect snapshots of all the allocated objects, and compare them to see if some kind of object count is steadily increasing.

That said, I would suggest changing query = new StringBuilder(); to query.Clear(). No need to re-allocate a bunch of memory when you already have a buffer available.

You could perhaps further reduce allocation rate by using Span<char>/Memory<char> as much as possible rather than strings. This should let you refer to a specific sequence of characters within a larger buffer without doing any copying or allocation at all. This was a major reason for Span<>, since copying strings is a bit inefficient when doing lots of xml/json deserialization & html processing.