Sql-server – Grouping SQL Server Connections By Connection Pool

connection-poolingsql server

In SQL Server 2014 is there a way to group connections by the connection pool that belong to? We are trying to find out if a specific application is leaking connections and hitting the default max pool size of 100.

In this doc on Connection Pooling it states that each pool is defined by the exact connection string used to establish the connection. However, I am unable to find anything in the sys table that allows me to see that.

The closest I have groups by loginname and program_name:

select  rtrim(p.loginame) 
    ,p.program_name
    ,count(*)
from    sys.sysprocesses p   
group by p.loginame, p.program_name
order by count(*) desc

Thanks in advance!

Best Answer

You can get a little closer if you add host process id but I don't think you'll be able to get an actual connection pool count 100% from server data because many connection string attributes are processed on the client and never sent to the server. This might be close enough for your needs, though.

SELECT
      s.login_name
    , s.host_name
    , s.program_name
    , s.host_process_id
    , COUNT(*) AS connection_count
FROM sys.dm_exec_sessions AS s
GROUP BY
      s.login_name
    , s.host_name
    , s.program_name
    , s.host_process_id;

A client-side exception like "timeout period elapsed prior to obtaining a connection from the pool" should be raised when the pool size is reached an another connection cannot be acquired within the connection timeout limit.

Related Question