How to check current SQL Server pool size

Question

Is there a way to check the current connection pool size in SQL Server? I'm not talking about the maximum size of the connection pool, but the current size of the pool. Let them say that the maximum pool size is 100, and 49 openings are open, now it should show me 51 available or, possibly, 49 consumed.

So, is there such a request?

Answer 1

A significant part of this material is apparently outside of what is directly available from dmv. I am sure that someone more informed than me can get you better answers.

It is as close as possible.

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 lead to the union of your connections by login and from each host and application. This will give you an idea of ​​how your connections are being combined. If you know your maximum amount per hand, you can subtract the compounds from it, and this can give you the number of connections left in each pool.

Answer 2

I think that for this you need to use the NumberOfActiveConnections performance counter in ADO.Net (if this is an option for you). This article talks about this particular counter:

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

It is disabled by default, so you need to add some configuration to enable it. This is described in detail in the following link. The following link also contains sample code for reading the counter.

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

Hope this helps!