CODEWITHSUNDEEP
sql-server-2012database-connectionconnection-pooling
Frank Martin
Frank Martin

Reputation: 3451

How to check current pool size of SQL Server

Is there a way to check the current connection pool size in SQL Server? I am not talking about the max connection pool size, but the current pool size. Let's say the max pool size is 100 and there are 49 connections open, it should now show me either 51 available or perhaps 49 consumed.

So, is there such a query?

Upvotes: 19

Views: 41515

Answers (2)

Mark Madej
Mark Madej

Reputation: 1922

I think to accomplish this, you would want to use the NumberOfActiveConnections performance counter in ADO.Net (if that is an option for you). This article talks about that specific counter:

http://msdn.microsoft.com/en-us/library/ms254503(v=vs.110).aspx

It's off by default, so you'll have to add some configuration to enable it. That is detailed in the following link. The following link also has some sample code to read the counter.

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/performance-counters?redirectedfrom=MSDN#activating-off-by-default-counters

Hope this helps!

Upvotes: 6

jwhaley58
jwhaley58

Reputation: 1033

So much of this stuff seems to be outside of what is directly accessible from dmv's. I'm sure someone more informed than myself can get you better answers.

This is as close as I could get. 

SELECT  des.program_name
      , des.login_name
      , des.host_name
      , COUNT(des.session_id) [Connections]
FROM    sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections DEC
        ON des.session_id = DEC.session_id
WHERE   des.is_user_process = 1
        AND des.status != 'running'
GROUP BY des.program_name
      , des.login_name
      , des.host_name
HAVING  COUNT(des.session_id) > 2
ORDER BY COUNT(des.session_id) DESC

This will aggregate your connections by login and from each host and app. This will give you an idea of how your connections are currently being pooled. If you know your max amount off hand, you can subtract the connections from it and it could give you the number of connections remaining in each pool.

Upvotes: 22

Related Questions