The CPU utilization is going to 100% regularly, and when I check SP_who2, it is showing around 20000 sessions, most of them are sleeping (also utilizing the CPU).
I think the CPU load is related to the sleeping sessions

The application name is showing as Microsoft JDBC.

Most of the sessions are showing 'SOS_SCHEDULER_YIELD as last_wait_type. After restarting the Windows Server, the CPU utilzation has come down, but the session count is increasing.

The server has 32 cores.

The application team is telling they are using some 'Connection Pool' for the the connection management.

How can I investigate or solve the number of sleeping sessions?

  • 20000 sessions/spids is way above most installations I've seen. Can you run a select count(*) from sys.dm_exec_connections? As you can see from the docs, even a 256 CPU core installation has a default of 8576 threads. learn.microsoft.com/en-us/sql/database-engine/configure-windows/…. Maybe SP_who2 is bugged. Also check your max number of cpu threads for us real quick? The link will tell you how. Nov 27, 2017 at 4:55

3 Answers 3


The application probably is leaking connections. I have no idea what they mean by

The application team is telling they are using some 'Connection Pool' for the the connection management.

But it sounds as they are trying to implement their own connection pooling system.

Generally connection leaking happens when you have calls to .open() without calling .close() on the same connection.

Since this is generally a client issue I think the only thing you can do from the database side (short of killing them every once in a while but I wouldn't suggest doing that) is use the information from sys.dm_exec_sessions to find the application leaking the connections and then talk to your developers or vendor so they can look for the offending code.

For example this query (taken from here:

select count(*) as sessions,
     db_name(s.database_id) as database_name
from sys.dm_exec_sessions s
where is_user_process = 1
group by host_name, host_process_id, program_name, database_id
order by count(*) desc;

will show you the number of connections per host per process. This should be enough to identify the offending application.

You could also create a job logging this information over time to chart out if the connections are really always increasing so you can "prove" to your application team there really is a leak.

  • 1
    Basically CPU utilization is running continuously at 100%, and I am finding it hard to prove that it is not an SQL Server issue. Even the sleeping processes are using the CPU.
    – Singh
    Nov 27, 2017 at 10:07
  • 1
    I'm not saying the 100% cpu is caused by the sleeping connections but the question was what those sleeping connections were. With the information given it's hard to tell if one is causing the other
    – Tom V
    Nov 27, 2017 at 11:10
  • The top most wait type of the server is showing as 'SOS_SCHEDULER_YIELD'. Does this means there is a CPU shortage and we need to add more CPU?
    – Singh
    Nov 28, 2017 at 3:55
  • 1
    I don't know, you could also need to tune your queries using a lot of CPU. It's up to you to determine which queries cause the load and maybe post a new question on how to optimize them.
    – Tom V
    Nov 28, 2017 at 20:21
  • @singh SOS_Scheduluer_yield does mean you have CPU pressure, but I wouldn't automatically add more CPU to solve this issue as the offending connectings will likely incrementally increase to consume the available CPU again. I am facing a similar situation and I believe the only solution is to work with the developer to resolve it. You can run a trace to see all your batch processes and the CPU duration to get an idea what is going on.
    – Asher
    Mar 18, 2020 at 7:47

The application team is telling they are using some 'Connection Pool' for the the connection management.

There are 3 possible things happening here.

  • One, they roll out their own app pool and it just is broken. It keeps handing out new connections but not closing them.
  • Second, someone was so smart to tell the pool to open 15000 connections and then use them. Yes, sometimes people put up ridiculous default values.
  • Third, they do not close connections properly. And the app pool has no upper limit set. So it keeps creating new connections as if it does not exist. Because they are never closed, so they are never returned to the pool.

There is no logical setup where having an app pool on a server that size having 150000 idle connections makes sense.

  • The connections are around 5000 (from sys.dm_exec_connections) but sessions are around 20000 (from sp_who2).
    – Singh
    Nov 28, 2017 at 3:34
  • 1
    Even that sounds out of whack for active connections. Must be a very special app to use that many.
    – TomTom
    Dec 3, 2017 at 19:02

Maybe you need to do some extra investigation on what is running on your server and be able to add more information to what you already have. I recommend using sp_whoisactive and First Responder Kit to get more info on what is running. Check a answer I gave to a somewhat similar situation.


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.