More than 15000 sleeping sessions in SQL Server 2014

Posted on

Question :

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?

Answer :

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.

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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *