SQL Server 2014 – Handling Over 15000 Sleeping Sessions

cpuperformanceperformance-tuningsql serversql server 2014

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?

Best 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.

Related Question