-- card_id: SQL-MS-05
-- classification: MS-LCK-01
-- title: SQL Server 2019/2022 blocking relationships (read-only, redacted)
-- engine: SQL Server
-- versions: 2019, 2022
-- risk: R2
-- purpose: Return at most 100 current blocked-to-blocker relationships for human diagnosis.
-- input: None; current sys.dm_exec_requests and sys.dm_exec_sessions snapshot only.
-- output: Explicit permission/empty status, or stable session fingerprints, wait bucket/type, request status, open transaction count and head-blocker-candidate flag.
-- permission: SQL Server 2019 VIEW SERVER STATE; SQL Server 2022 VIEW SERVER PERFORMANCE STATE.
-- resource_limit: Current snapshot, Top 100; use a client-side timeout of 5 seconds.
-- validation: Static contract and SQL Server 2019/2022 isolated runtime contract verified; production and customer environments are not verified.
-- prohibited: Do not expose SQL text, database/object/login/host/program/address data; do not KILL, commit, roll back, change configuration or grant permissions.

WITH permission_state AS (
    SELECT
        CASE
            WHEN TRY_CONVERT(
                int,
                SERVERPROPERTY('ProductMajorVersion')
            ) >= 16
             AND COALESCE(
                 HAS_PERMS_BY_NAME(
                     NULL,
                     NULL,
                     'VIEW SERVER PERFORMANCE STATE'
                 ),
                 0
             ) = 1
            THEN CAST(1 AS bit)
            WHEN TRY_CONVERT(
                int,
                SERVERPROPERTY('ProductMajorVersion')
            ) < 16
             AND COALESCE(
                 HAS_PERMS_BY_NAME(NULL, NULL, 'VIEW SERVER STATE'),
                 0
             ) = 1
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
        END AS can_observe_server
),
blocking_edges AS (
    SELECT
        request.session_id AS waiting_session_id,
        request.blocking_session_id,
        request.wait_type,
        request.wait_time,
        request.status AS request_status,
        request.open_transaction_count
    FROM sys.dm_exec_requests AS request
    INNER JOIN sys.dm_exec_sessions AS session_info
        ON session_info.session_id = request.session_id
    CROSS JOIN permission_state
    WHERE session_info.is_user_process = 1
      AND permission_state.can_observe_server = 1
      AND request.session_id <> @@SPID
      AND request.blocking_session_id > 0
      AND request.blocking_session_id <> request.session_id
),
classified_edges AS (
    SELECT
        edge.waiting_session_id,
        edge.blocking_session_id,
        edge.wait_type,
        edge.wait_time,
        edge.request_status,
        edge.open_transaction_count,
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM blocking_edges AS parent_edge
                WHERE parent_edge.waiting_session_id = edge.blocking_session_id
            ) THEN CAST(0 AS bit)
            ELSE CAST(1 AS bit)
        END AS blocker_is_head_candidate
    FROM blocking_edges AS edge
),
result_rows AS (
    SELECT
        CAST('ready' AS varchar(32)) AS observation_status,
        CONVERT(
            varchar(64),
            HASHBYTES('SHA2_256', CONCAT('sql-ms-05-v1:', waiting_session_id)),
            2
        ) AS waiting_session_fingerprint,
        CONVERT(
            varchar(64),
            HASHBYTES('SHA2_256', CONCAT('sql-ms-05-v1:', blocking_session_id)),
            2
        ) AS blocking_session_fingerprint,
        CAST(
            CASE
                WHEN wait_time < 1000 THEN 'under_1s'
                WHEN wait_time < 5000 THEN '1s_to_5s'
                WHEN wait_time < 30000 THEN '5s_to_30s'
                WHEN wait_time < 300000 THEN '30s_to_5m'
                ELSE 'over_5m'
            END
            AS varchar(32)
        ) AS wait_duration_bucket,
        CAST(wait_type AS nvarchar(60)) AS wait_type,
        CAST(request_status AS nvarchar(30)) AS request_status,
        open_transaction_count,
        blocker_is_head_candidate
    FROM classified_edges

    UNION ALL

    SELECT
        CAST(
            CASE
                WHEN permission_state.can_observe_server = 0
                    THEN 'permission_denied'
                ELSE 'ready_no_edges'
            END
            AS varchar(32)
        ) AS observation_status,
        CAST(NULL AS varchar(64)) AS waiting_session_fingerprint,
        CAST(NULL AS varchar(64)) AS blocking_session_fingerprint,
        CAST(NULL AS varchar(32)) AS wait_duration_bucket,
        CAST(NULL AS nvarchar(60)) AS wait_type,
        CAST(NULL AS nvarchar(30)) AS request_status,
        CAST(NULL AS int) AS open_transaction_count,
        CAST(NULL AS bit) AS blocker_is_head_candidate
    FROM permission_state
    WHERE permission_state.can_observe_server = 0
       OR NOT EXISTS (SELECT 1 FROM classified_edges)
)
SELECT TOP (100)
    observation_status,
    waiting_session_fingerprint,
    blocking_session_fingerprint,
    wait_duration_bucket,
    wait_type,
    request_status,
    open_transaction_count,
    blocker_is_head_candidate
FROM result_rows
ORDER BY
    CASE observation_status
        WHEN 'ready' THEN 0
        WHEN 'ready_no_edges' THEN 1
        ELSE 2
    END,
    waiting_session_fingerprint,
    blocking_session_fingerprint;
