SQL Server blocking Finding the head blocker

/*
Read-only live snapshot. Run while blocking is happening.
SQL Server 2019 and earlier: VIEW SERVER STATE for other sessions.
SQL Server 2022 and later: VIEW SERVER PERFORMANCE STATE.
*/
;WITH blocked_requests AS (
    SELECT
        session_id,
        blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
),

participants AS (
    SELECT session_id FROM blocked_requests
    UNION
    SELECT blocking_session_id
    FROM blocked_requests
    WHERE blocking_session_id > 0
),

oldest_transaction AS (
    SELECT
        st.session_id,
        MIN(at.transaction_begin_time) AS transaction_begin_time
    FROM sys.dm_tran_session_transactions AS st
    INNER JOIN sys.dm_tran_active_transactions AS at
        ON at.transaction_id = st.transaction_id
    GROUP BY st.session_id
)
SELECT
    s.session_id,
    r.blocking_session_id,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM blocked_requests AS b
            WHERE b.blocking_session_id = s.session_id
        )
        AND ISNULL(r.blocking_session_id, 0) <= 0
        THEN 'HEAD BLOCKER'
        WHEN EXISTS (
            SELECT 1
            FROM blocked_requests AS b
            WHERE b.blocking_session_id = s.session_id
        )
        AND r.blocking_session_id > 0
        THEN 'BLOCKED AND BLOCKING'
        WHEN r.blocking_session_id < 0
        THEN 'BLOCKED BY SPECIAL OWNER'
        WHEN ISNULL(r.blocking_session_id, 0) > 0
        THEN 'BLOCKED'
        ELSE 'BLOCKER'
    END AS blocking_role,
    (
        SELECT COUNT(*)
        FROM blocked_requests AS b
        WHERE b.blocking_session_id = s.session_id
    ) AS directly_blocked_session_count,
    COALESCE(r.status, s.status) AS session_status,
    r.command,
    r.wait_type,
    r.last_wait_type,
    r.wait_time AS wait_time_ms,
    r.wait_resource,
    CASE r.blocking_session_id
        WHEN -2 THEN 'Orphaned distributed transaction owns the resource'
        WHEN -3 THEN 'Deferred recovery transaction owns the resource'
        WHEN -4 THEN 'Latch owner could not be identified during a state transition'
        WHEN -5 THEN 'Latch owner is not tracked for this latch type'
    END AS special_blocking_owner,
    COALESCE(r.open_transaction_count, s.open_transaction_count) AS open_transaction_count,
    ot.transaction_begin_time,
    DATEDIFF(second, ot.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,
    DB_NAME(COALESCE(r.database_id, s.database_id)) AS database_name,
    s.host_name,
    s.program_name,
    s.login_name,
    CASE
        WHEN active_txt.text IS NULL OR r.statement_start_offset IS NULL THEN NULL
        ELSE SUBSTRING(
            active_txt.text,
            (r.statement_start_offset / 2) + 1,
            ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(active_txt.text)
                ELSE r.statement_end_offset
              END - r.statement_start_offset) / 2) + 1
        )
    END AS active_statement,
    active_txt.text AS active_batch_text,
    recent_txt.text AS most_recent_batch_text,
    ib.event_info AS last_submitted_command
FROM participants AS p
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = p.session_id
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections AS c
    ON c.session_id = s.session_id
LEFT JOIN oldest_transaction AS ot
    ON ot.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS active_txt
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS recent_txt
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, r.request_id) AS ib
WHERE s.session_id <> @@SPID
ORDER BY
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM blocked_requests AS b
            WHERE b.blocking_session_id = s.session_id
        )
        AND ISNULL(r.blocking_session_id, 0) <= 0
        THEN 0 ELSE 1
    END,
    r.wait_time DESC,
    s.session_id ASC;

/* Read-only detail for a session found in the first result. */
DECLARE @session_id smallint = 57; -- replace 57 with the head blocker

SELECT
    s.session_id,
    COALESCE(r.status, s.status) AS session_status,
    r.command,
    r.wait_type,
    r.last_wait_type,
    r.wait_time AS wait_time_ms,
    r.wait_resource,
    s.open_transaction_count,
    at.transaction_id,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,
    at.transaction_type,
    at.transaction_state,
    s.host_name,
    s.program_name,
    s.login_name,
    active_txt.text AS current_batch_text,
    recent_txt.text AS most_recent_batch_text,
    ib.event_info AS last_submitted_command,
    qp.query_plan AS current_query_plan
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections AS c
    ON c.session_id = s.session_id
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS active_txt
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS recent_txt
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, r.request_id) AS ib
OUTER APPLY sys.dm_exec_text_query_plan(
    r.plan_handle,
    r.statement_start_offset,
    r.statement_end_offset
) AS qp
WHERE s.session_id = @session_id;