High Number of Connections

Question

The below query tells me there is a total of 520 connections to our SQL Server database. Status of all is 'sleeping' except for 4 connections. The login_time goes back to 2 days ago on some connections.

select session_id,  login_time, host_name, program_name, status, cpu_time, total_elapsed_time
from sys.dm_exec_sessions
order by total_elapsed_time desc;

I run the below query to see that there are 61 different logins...

SELECT login_name, COUNT(session_id) AS [session_count] 
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY login_name;

Is this standard? Or should those conenctions that are sleeping be closed?

Thanks.

Answer 1

Connection pooling surely has that type of behavior, but the providers I'm familiar with wouldn't typically keep those connections active to reuse for days, or even hours. In fact, ADO.NET will eliminate the connection from the pool in about 4 to 8 minutes or inactivity.

Yes, it sounds like you have clients not properly closing and disposing of connections, which could be leading to more severe problems than just connection placeholders. But at the same time, you should not only be verifying login_time, but you also have to consider last_request_start_time to get an idea of activity of the session (or the lack thereof).

Is this standard?

It depends. For that duration, no that's not typical and surely warrants the investigation of how clients are managing connections to the data source.

should those conenctions that are sleeping be closed?

You would first need to determine that they shouldn't be currently active, and then you would need to confirm with the business and end users whether or not you can clean that up. But that would most likely be a very temporary fix, as if this is programmatic connections (think client applications) then it will probably come back once applications and/or end users reconnect.

Take a look at this blog post I wrote about connection pooling. It is relatively thorough and should give you a good idea on distinguishing between connection pooling, as well as poorly managed connections.