3

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. docs.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. – Ali Razeghi Nov 27 '17 at 4:55
6

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,
     s.host_name,
     s.host_process_id,
     s.program_name,
     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 '17 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 - Team Monica Nov 27 '17 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 '17 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 - Team Monica Nov 28 '17 at 20:21
3

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 '17 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 '17 at 19:02
-1

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.

https://dba.stackexchange.com/a/66261/9031

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.