3

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?

Improve this question
4
  • 2
    What does your DBA have to say when you asked how does this affect performance? (Spoiler alert: it very likely has no meaningful impact.)
    – J.D.
    Commented Oct 3, 2022 at 12:27
  • @J.D. The DBA only mentioned some vague budgetary constraints. I guess that they want to decommission some physical servers and squeeze more applications on the same hardware.
    – Alexei
    Commented Oct 3, 2022 at 15:41
  • Yea sounds like they're just looking for things to call out without any backing to why. Unless they provide an actual reason (without being fed an out) such as some of the examples in Sean's answer, I'm betting you're right regarding connection pooling, and these couple of sleeping connections aren't of concern. Otherwise you'd already be aware in the consuming app when things are slow / timeout. The DBA should instead be focusing their efforts looking for actual issues with costly queries (frequently ran, high CPU, high logical reads, long runtimes, lots of / long blocks, deadlocks etc).
    – J.D.
    Commented Oct 3, 2022 at 18:26
  • EZ-PZ - profile your application and determine a sensible number of idle connections in the pool, and adjust the pool to that number. 100 idle connections might be 99 more than your application needs you may find out...
    – SnakeDoc
    Commented Oct 3, 2022 at 22:53

1 Answer 1

Reset to default
5

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.

Improve this answer
3
  • AFAIK there are less than 50 applications (virtually all .NET ones) that hit the server. Assuming that they do not mess with the connection pooling attributes, they could theoretically go to up 50 x 100 = 5000 connections. I think that DB connection starvation is far from being a real thing. However, if such a sleeping connection takes a significant amount of resources, that's another story.
    – Alexei
    Commented Oct 3, 2022 at 15:45
  • @Alexei - "significant amount of resources" is relative, does 100 MB of memory matter to a 1 TB server, probably not, but would to a 10 GB server. Since we have no idea what each connection has done or is holding, if anything, it's not possible to say. I would concur that 5,000 connections just hanging out for the sake of hanging out is not a good thing. Even in connection pooling there should be connection scavenging which seems to not occur here. You'd need to trace them to see if they are occasionally being used.
    – Sean Gallardy
    Commented Oct 3, 2022 at 15:53
  • This isn't to mention that having a bunch of sleeping connections also isn't free to the processor, or the OS, either. All of those have associated handles, locks, etc., and querying things like DMVs will all take a hit for each row needing to be "created".
    – Sean Gallardy
    Commented Oct 3, 2022 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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