CODEWITHSUNDEEP
c#.netsqlsql-serversql-server-2008
George2
George2

Reputation: 45771

SQL Server connection count issue

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

Upvotes: 2

Views: 5418

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294247

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'

Upvotes: 4

devstuff
devstuff

Reputation: 8387

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.

Upvotes: 1

user179700
user179700

Reputation: 522

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'

Upvotes: 2

Related Questions