Why does my application have over 20,000 idle (sleeping) connections in SQL Server?
2
votes
1
answer
2217
views
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.
Asked by Jake McGraw
(51 rep)
Jun 3, 2022, 03:29 PM
Last activity: Jun 22, 2022, 04:56 PM
Last activity: Jun 22, 2022, 04:56 PM