CODEWITHSUNDEEP
c#asp.netsql-serverentity-framework
Tal C
Tal C

Reputation: 567

Find out where sql server connections are being left open in c#

I inherited a ton of C# dotnet code that apparently in some place is not closing the connection or something to sql server and I get

The timeout period elapsed prior to obtaining a connection from the pool: This may have occurred because all pooled connections were in use and max pool size was reached

This happens quite a bit and usually at the same time but I don't know of anything that is scheduled. Also searching manually every little place in code is quite not possible. Any ideas how to fix this without manually going over every single function in the code?

Upvotes: 1

Views: 1367

Answers (1)

Lawrence Johnson
Lawrence Johnson

Reputation: 4043

The problem with not wanting to go through the code is that it's very likely that it's not a single instance or area and may depend on where you're getting the most traffic in the production environment. You could have a fairly innocuous looking query that could be improved slightly with a .AsNoTracking() or a better join/include rather than a single big query that is poorly optimized. There are also instances where the entire design is flawed (I came into a project once where sql connections were attached to individual classes, so there was no real way to wrap the connection in a using statement. We had to restructure all of it.

If you're using custom transactions or sql connection requests, make sure you're wrapping with a using and a try catch finally with a dispose. Relying on the garbage collector is not always great since there's not guarantee the garbage collector whill actually dispose immediately at the end of a using statement.

using (var conn = new SqlConnection())
{
    try
    {
        // Transactions, SQL Commands, etc
    }
    catch (SqlException ex)
    {
        // Trans rollback, error handling
    }
    finally
    {
        // Tell the garbage collector to clean up this resource now.
        conn.Dispose();
    }
}

If you're strictly using a database context, some statements can be optimized marginally by using .AsNoTracking() for instances where you are only retrieving a result-set but have no intention of modifying the results from that query. Something like the following:

var tier = _dbContext.Tier.AsNoTracking()
    .Include(t => t.NameSchema)
    .Include(t => t.TierPackageGroups)
    .Where(t => t.TierNumber == tierNumber).FirstOrDefault();

Make sure you're using joins or .Include wherever possible as opposed to lazy loading.

It's not the answer that you want to hear, and I've been in your shoes, but most likely the best solution is to go through the code. You could always start by cleaning up a few areas, gauging impact, a few more areas, gauging impact, etc. This sort of creep investigation can help narrow your search as you see improvements in some areas or don't see some in others.

If you're convinced it has to be a single query that you want to find, then there are tools for evaluating sql performance, but I usually pull in a DBA to handle that so I'm not familiar with those tools. If you're using Azure there is a panel where you can see the queries and have them organized by performance impact. It's not going to point you to the code, but it could give you a hint.

Since the question is specifically for a solution that doesn't require searching for code, the one other answer that will work is to just throw more hardware at the problem, but this a poor stop-gap that can be costly and ultimately just be delaying the problem or only solving for the problem 99% of the time (until you hit a peak traffic period, for instance). One client we had a few years back has probably spent more than $50k in additional hosting costs when they could've had us fix the issue for under $5k at the time of the initial request.

Upvotes: 1

Related Questions