/*
SQL Server blocking diagnostics
Source: https://kerteszmihaly.com/sql-server/blocking

Both queries are read-only. Run them while blocking is happening.
SQL Server 2019 and earlier generally require VIEW SERVER STATE to inspect other sessions.
SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE.

Negative blocking_session_id values have special meanings:
-2 orphaned distributed transaction; -3 deferred recovery transaction;
-4 temporarily unidentified latch owner; -5 untracked latch owner.
*/

-- Query 1: capture the current blocking chain and likely 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;

GO

-- Query 2: replace session ID 57 with the head blocker returned above.
/* 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;