SYSK 67: SQL – Who is Connected and What are They Running?

Want to know who the users that are connected and how many sessions do each of them have?  Then use the new sys.dm_exec_sessions view:
      SELECT login_name, count(session_id) as session_count FROM  sys.dm_exec_sessions GROUP BY login_name

Want to know who is running what at that exact instance?  Then run the statement below:
      SELECT st.text as [Command text], login_time, [host_name], 
      program_name, sys.dm_exec_requests.session_id, client_net_address,
      sys.dm_exec_requests.status, command, db_name(database_id) as DatabaseName
      INNER JOIN sys.dm_exec_connections on sys.dm_exec_requests.session_id = sys.dm_exec_connections.session_id
      INNER JOIN sys.dm_exec_sessions on sys.dm_exec_sessions.session_id = sys.dm_exec_requests.session_id
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
      sys.dm_exec_requests.session_id >= 51

Source: and

Comments (0)

Skip to main content