SQL Server Blocking and Deadlock Diagnostic Script Generator

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS wait_ms,
    DB_NAME(r.database_id) AS database_name,
    s.login_name,
    s.host_name,
    s.program_name,
    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 running_statement
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
  AND r.wait_time >= 5000
  AND DB_NAME(r.database_id) = 'ERP_PROD'
ORDER BY r.wait_time DESC;

SELECT DISTINCT
    s.session_id AS head_blocker_session,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.last_request_start_time,
    s.last_request_end_time,
    c.most_recent_sql_handle
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
WHERE s.session_id IN (
        SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)
  AND s.session_id NOT IN (
        SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0);

-- Last statement the head blocker ran:
-- SELECT text FROM sys.dm_exec_sql_text(<most_recent_sql_handle>);

SELECT
    st.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.last_request_end_time,
    DATEDIFF(MINUTE, s.last_request_end_time, GETDATE()) AS idle_minutes,
    at.transaction_begin_time
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id
JOIN sys.dm_tran_active_transactions AS at ON at.transaction_id = st.transaction_id
WHERE s.status = 'sleeping'
  AND DATEDIFF(MINUTE, s.last_request_end_time, GETDATE()) >= 10
ORDER BY idle_minutes DESC;

SELECT
    xed.value('@timestamp', 'datetime2') AS deadlock_time_utc,
    xed.query('.') AS deadlock_graph
FROM (
    SELECT CAST(target_data AS xml) AS target_xml
    FROM sys.dm_xe_session_targets AS xt
    JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
    WHERE xs.name = 'system_health'
      AND xt.target_name = 'ring_buffer'
) AS d
CROSS APPLY target_xml.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS q(xed)
WHERE xed.value('@timestamp', 'datetime2') >= DATEADD(HOUR, -24, SYSUTCDATETIME())
ORDER BY deadlock_time_utc DESC;