ActiveRequestsAndBlocking.sql

/*
Purpose:
- Show active user requests, the statement currently executing, and blocking relationships.

Safety:
- Read-only. Query text and plans can contain sensitive literals; handle the output accordingly.

Requirements:
- SQL Server 2012 or later.
- VIEW SERVER STATE, or the equivalent server performance-state permission on newer versions.

Customization:
- Set @OnlyBlockedOrBlocking to 1 for an incident-focused view.
*/

SET NOCOUNT ON;

DECLARE @OnlyBlockedOrBlocking BIT = 0;

SELECT
    r.session_id,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time AS wait_time_ms,
    r.wait_resource,
    r.cpu_time AS cpu_time_ms,
    r.total_elapsed_time AS elapsed_time_ms,
    r.reads,
    r.writes,
    r.logical_reads,
    r.percent_complete,
    CASE
        WHEN r.total_elapsed_time > 0
            THEN CAST(r.cpu_time * 100.0 / r.total_elapsed_time AS DECIMAL(6, 2))
        ELSE 0
    END AS cpu_percent_of_elapsed,
    SUBSTRING(
        txt.text,
        (r.statement_start_offset / 2) + 1,
        (
            (CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(txt.text)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2
        ) + 1
    ) AS active_statement,
    txt.text AS batch_text,
    qp.query_plan
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
  AND (
      @OnlyBlockedOrBlocking = 0
      OR r.blocking_session_id > 0
      OR EXISTS (
          SELECT 1
          FROM sys.dm_exec_requests AS blocked
          WHERE blocked.blocking_session_id = r.session_id
      )
  )
ORDER BY
    CASE WHEN r.blocking_session_id = 0 THEN 0 ELSE 1 END,
    r.total_elapsed_time DESC;