How to display active connections and running queries in SQL Server?

Posted by on Nov 6, 2010 in SQL Server, Transact-SQL | 0 comments

This query shows all queries executed at the moment, the session id, status, used cpu time and the execution duration

SELECT st.text, r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

The following query shows the number of connections a user has with a database

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

Leave a Reply

Your email address will not be published. Required fields are marked *

Captcha: * Time limit is exhausted. Please reload CAPTCHA.