0

I want to get a proper or near accurate count for the number of connections in a sql server instance. I have been using different queries from internet and i see that some people use dmvs ,some people use sys.sysprocesses etc.

I know that there is a connection_id,session_id and request_id. But which one gives a correct estimate of the number of users(may be i should call no. of sessions or no of connection) connected to a sql server instance.

Here are few queries which i collected from different forums/sites etc.

SELECT DB_NAME(eS.database_id) AS the_database , COUNT(eS.session_id) AS total_database_connections FROM sys.dm_exec_sessions eS GROUP BY DB_NAME(eS.database_id) ORDER BY 1, 2;

SELECT DB_NAME(sP.dbid) AS the_database , COUNT(sP.spid) AS total_database_connections FROM sys.sysprocesses sP GROUP BY DB_NAME(sP.dbid) ORDER BY 1;

SELECT DB_NAME(ST.dbid) AS the_database , COUNT(eC.connection_id) AS total_database_connections FROM sys.dm_exec_connections eC CROSS APPLY sys.dm_exec_sql_text (eC.most_recent_sql_handle) ST LEFT JOIN sys.dm_exec_sessions eS ON eC.most_recent_session_id = eS.session_id GROUP BY DB_NAME(ST.dbid) ORDER BY 1;

SELECT oPC.cntr_value AS connection_count FROM sys.dm_os_performance_counters oPC WHERE ( oPC.[object_name] = 'SQLServer:General Statistics' AND oPC.counter_name = 'User Connections' ) ORDER BY 1;

These queries are not returning same values. I see that one query is counting connection_id.

  • If you only need the total number of user connections, you could use the last query in your question or one of these queries: SELECT COUNT(*) FROM sys.dm_exec_connections; or SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1;. – Dan Guzman Jan 11 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.