If your server is sluggish, a job is taking longer than expected, or someone just says “the database is slow”, the first thing I want to know is who’s connected and what they’re running.
This script shows current active sessions: who’s logged in, the application they’re using, the statement and batch, how long it’s been running, and whether they’re being blocked.
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status,
r.start_time,
DATEDIFF(SECOND, r.start_time, GETDATE()) AS duration_seconds,
r.cpu_time,
r.reads,
r.writes,
r.blocking_session_id,
SUBSTRING(
t.text,
(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1
) AS current_statement,
t.text AS full_batch,
r.plan_handle
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1
ORDER BY r.start_time ASC| Column | What it tells you |
|---|---|
blocking_session_id | The ID of the session blocking this one |
duration_seconds | How long the current execution has been running |
current_statement | The specific statement being executed right now |
full_batch | The whole batch |
plan_handle | Pass to sys.dm_exec_query_plan() to pull the actual execution plan |
To include idle sessions with open transactions, update the WHERE clause:
WHERE s.is_user_process = 1
AND (r.session_id IS NOT NULL OR s.open_transaction_count > 0)If you want more detail without writing the query yourself, Adam Machanic’s sp_whoisactive does all of this and more. It’s a free stored procedure you install once and call with a single line. It surfaces wait types, tempdb usage, query plans and blocking chains in one result set, and it’s worth having on any server you support.