SQL Server connection count issue

Question

Hello everyone,

I am using SQL Server 2008 Enterprise + .Net 3.5 + C# + ADO.Net. I am using the following SQL statement to monitor connection number, is it correct? If yes, my confusion is, one connection from ADO.Net client maps to only one connection in the following statement? Or one ADO.Net connection could maps to multiple connections here?

SELECT  *  FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:General Statistics'

(Monitor User Connections row)

thanks in advance, George

Answer 1

Would this work for your needs? I'm confused if you're trying to count the number of connections. Your question seems to say no, where your comment implies yes to me.

Sp_who2 'Active'

Answer 2

By default, the underlying SQL Server driver code uses a connection pool. You'll find that the number of physical connections "owned" by your application will grow over time to the current limit, but this is different from the number that are "in use".

This avoids renegotiating security, etc. on each link, speeding up your application's database access.

As mentioned by @sgmarshall, use the sp_who2 stored procedure to determine what each of the connections are currently doing.

Answer 3

Use SELECT * FROM sys.dm_exec_connections to find all the connections. The client_net_address has the client address so you can track down the origin of connections.

Use SELECT * FROM sys.dm_exec_sessions to find all the sessions (sessions in general map 1 to 1 with connections unless MARS is used). The program_name column will contain the value of the application name you passed in in the connection string and allows you to identify your own connections.

Use SELECT * FROM sys.dm_exec_requests to see all the current executing batches (requests).

The performance counter would only give you one value, the number of current connections:

SELECT  cntr_value
FROM sys.dm_os_performance_counters 
WHERE object_name = 'SQLServer:General Statistics'
  and counter_name = 'User Connections'