patternsqlMinor
How much resources does a sleeping connection require in SQL Server?
Viewed 0 times
muchserversqlsleepingdoeshowrequireresourcesconnection
Problem
One of our DBAs complained to our team that he noticed about a dozen sleeping connections are virtually permanent. Each of them indicates a very short query (a single record from a single record was returned filtered by its clustered PK).
My assumption is that the cause is the usage of the ADO.NET connection pooling and the application that makes the same query quite often.
I am trying to find out if these sleeping connections can have a meaningful impact on the SQL Server performance. By default, the ADO.NET connection pool has a limit of 100 connections, so my assumption is that a dozen sleeping connections should be negligible.
I could only find information in this thread:
The minimum cost per sleeping session is on the order of 32kb of RAM -
very very modest!
There is probably some minuscule additional cost in CPU overheads but
it is going to be nearly undetectable even for 10,000 sessions.
On the matter of session/connection overheads, SQL Server is very well
behaved!
Is this information accurate?
My assumption is that the cause is the usage of the ADO.NET connection pooling and the application that makes the same query quite often.
I am trying to find out if these sleeping connections can have a meaningful impact on the SQL Server performance. By default, the ADO.NET connection pool has a limit of 100 connections, so my assumption is that a dozen sleeping connections should be negligible.
I could only find information in this thread:
The minimum cost per sleeping session is on the order of 32kb of RAM -
very very modest!
There is probably some minuscule additional cost in CPU overheads but
it is going to be nearly undetectable even for 10,000 sessions.
On the matter of session/connection overheads, SQL Server is very well
behaved!
Is this information accurate?
Solution
I am trying to find out if these sleeping connections can have a meaningful impact on the SQL Server performance.
Generically, if the word performance here is used to discuss only executing queries and the assumption is that none of these sleeping connections hold any locks or open transactions, then it would be fairly safe to assume there would be no meaningful impact on performance (this also assumes there is no memory pressure on the instance, internal or external).
A common issue with these connections is that some hold open transactions or locks, which can be problematic to find at times due to most admins only looking for executing queries. This can have effects from holding up truncation of the transaction log (causing log growth which will impact performance), blocking other queries, to causing internal memory pressure on caches, which I believe we can all agree are performance impacting.
Without getting into all the minutia of having 10,000 sleeping connections brings, there is a limit of 32k connections to the server. If there are 10,000+ sleeping connections, then running out of connections could became an actual issue. Unfortunately, this isn't as farfetched as you might believe as I've help with this issue multiple times.
Generically, if the word performance here is used to discuss only executing queries and the assumption is that none of these sleeping connections hold any locks or open transactions, then it would be fairly safe to assume there would be no meaningful impact on performance (this also assumes there is no memory pressure on the instance, internal or external).
A common issue with these connections is that some hold open transactions or locks, which can be problematic to find at times due to most admins only looking for executing queries. This can have effects from holding up truncation of the transaction log (causing log growth which will impact performance), blocking other queries, to causing internal memory pressure on caches, which I believe we can all agree are performance impacting.
Without getting into all the minutia of having 10,000 sleeping connections brings, there is a limit of 32k connections to the server. If there are 10,000+ sleeping connections, then running out of connections could became an actual issue. Unfortunately, this isn't as farfetched as you might believe as I've help with this issue multiple times.
Context
StackExchange Database Administrators Q#317746, answer score: 5
Revisions (0)
No revisions yet.