SQL Server Connection Count Error
I am using SQL Server 2008 Enterprise + .Net 3.5 + C # + ADO.Net. I am using the following SQL statement to monitor the connection number, is it correct? If so, my confusion is one connection to ADO.Net client cards for only one connection in the following statement? Or a single ADO.Net connection can display multiple connections here?
SELECT * FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:General Statistics'
(the line "Connecting monitor users")
thanks in advance george
source to share
Use SELECT * FROM
sys.dm_exec_connections
to find all connections. client_net_address
has a client address so you can track the origin of connections.
Use SELECT * FROM
sys.dm_exec_sessions
to find all sessions (shared map sessions 1 through 1 with connections if using MARS ). The column program_name
will contain the value of the application name you passed in the connection string and allows you to identify your own connections.
Use SELECT * FROM
sys.dm_exec_requests
to view all current batches (requests).
The performance counter will 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'
source to share
By default, connection pooling is used in the underlying SQL Server driver code. You will find that the number of physical connections "owned" by your application will increase over time to the current limit, but this is different from the number that is "in use".
This avoids overestimating security, etc. each link, speeding up access to the application database.
As @sgmarshall pointed out, use a stored procedure sp_who2
to determine what each of the connections is currently doing.
source to share