Why does my application have over 20,000 idle (sleeping) connections in SQL Server?

All we need is an easy explanation of the problem, so here it is.

I have an analytics application running with .NET 6 in a Linux Kubernetes container. It receives http messages and logs them.

For my data layer, I’m using the Microsoft.Data.SqlClient v4.1.0 library, and straight ADO.NET connections – like this:

var connString = ConnectionService.GetConnectionString();

using var conn = new SqlConnection(connString);
using var cmd = new SqlCommand(...sql..., conn);
cmd.Parameters.Add("@....", SqlDbType...).Value = ...;

try
{
    await cmd.Connection.OpenAsync();
    await cmd.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
    Log.Error(ex);
    await cmd.Connection.CloseAsync();
    throw;
}

await cmd.Connection.Close();

The analytics service is averaging about 250 writes per second. It’s currently writing to SQL Server 2017 (there is a plan to upgrade, but it hasn’t happened yet).

Watching SQL Server Profiler, the writes take from 1 ms to 6 ms. Never anything long running. Checking for sleeping sessions with this query:

SELECT  login_name,
        COUNT(*) [sleeping_sessions]
FROM sys.dm_exec_sessions
WHERE database_id > 0
  AND status = 'sleeping'
GROUP BY login_name
ORDER BY 2 DESC

As of this writing, there are 23,173 sleeping connections.

I ran a closed test on a dev server, and I can see that when the application opens a connection and closes it, the connection is left in a sleeping state. It only disappears when the application is stopped.

My understanding is that this is how ADO.NET "connection pools" work. However, I was under the impression there was a 100 connection limit, unless otherwise increased.

I changed the SQL process to run synchronously:

try
{
    cmd.Connection.Open();
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    Log.Error(ex);
    cmd.Connection.Close();
    throw;
}
cmd.Connection.Close();

But this didn’t change anything. Still 20k+ sleeping connections.

I’m taking advantage of a C# 8.0 feature called a using statement, on both the connection and the command object. It’s semantically equivalent of wrapping the code block in a using with {}.

I’m using a single connection string, identical for each, and the same security context. The connection string is pulled from an app.config setting.

I tried an old-school using block + synchronous + max pool size=100 in the connection string, but had no impact. Still 20k+ sleeping sessions. It starts at 0 and keeps rising but eventually levels out.

Does anyone have any recommendations as to what steps I should take? SQL Server only allows a max of 32,767 connections, so I’m getting close to the danger zone.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

For anyone else that runs into this, I opened a ticket with MS and was able to get a resolution.

My abbreviated code in the original question didn’t include my SqlCredential logic, which was the source of the problem.

Here is the full code that was generating the SqlConnection from the service:

static readonly Regex r_cred = new(@"\b(User|User ID|UID|Password|PWD)\s*=\s*'?([\s\S]+?)'?;\s*", RegexOptions.IgnoreCase);

public SqlConnection GetConnection()
{
    var connString = AppConfig.Settings.DBConnection;

    string? uid = null;
    string? pwd = null;
    var replaced = r_cred.Replace(connString, m =>
    {
        switch (m.Groups[1].Value.ToLowerInvariant())
        {
            case "user":
            case "user id":
            case "uid":
                uid = m.Groups[2].Value;
                break;
           case "password":
            case "pwd":
                pwd = m.Groups[2].Value;
                break;
        }
        return string.Empty;
    });

    if (string.IsNullOrEmpty(uid) || string.IsNullOrEmpty(pwd))
        return new SqlConnection(connString);

    var securePwd = MakeSecure(pwd);

    var conn = new SqlConnection(replaced)
    {
        Credential = new SqlCredential(uid, securePwd)
    };

    return conn;
}

public static System.Security.SecureString MakeSecure(string value)
{
    System.Security.SecureString secure;
    unsafe
    {
        fixed (char* chars = value)
            secure = new System.Security.SecureString(chars, value.Length);
    }
    secure.MakeReadOnly();
    return secure;
}

The problem was, by creating a NEW SqlCredential object each time, I was fragmenting the connection pools. Although the connection string itself was the same, and the username/password was the same, the new instances of the credentials object was enough for ADO.NET to keep creating new connection pools.

One I started caching and re-using the same SqlCredential object, the number of sleeping connections went from 20k+ down to about 6.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply