2

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.

3
  • 1
    Have you reviewed docs.microsoft.com/en-us/dotnet/framework/data/adonet/…?
    – Paul White
    Jun 8 at 13:03
  • 1
    @PaulWhite, Yes, thank you. The oddity here is that the application is allowing more than 100 connections, as opposed to honoring the limit. It's almost as though ADO.NET doesn't recognize that sleeping connections are available in the pool and it instead makes new ones? Jun 8 at 17:14
  • 1
    Are you able to use docs.microsoft.com/en-us/dotnet/framework/data/adonet/… to determine how many connection pools are being generated? You mention Linux in your question, but don't say whether SQL Server is running on Linux or Windows. A full repro would be nice of course. If it were me, I'd probably try a specific SQL Sever authentication login and password in the connection string on the dev server.
    – Paul White
    Jun 9 at 7:06

1 Answer 1

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.