SQL Find Blocking Generator — SQL Server Blocking Analysis

Examples

SELECT
    r.session_id                          AS blocked_session,
    r.blocking_session_id                 AS blocking_session,
    r.wait_type,
    r.wait_time / 1000.0                  AS wait_seconds,
    r.status,
    DB_NAME(r.database_id)                AS database_name,
    SUBSTRING(bt.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(bt.text)
            ELSE r.statement_end_offset END
         - r.statement_start_offset)/2)+1) AS blocked_sql,
    SUBSTRING(bk.text, 1, 500)            AS blocking_sql
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)  AS bt
LEFT JOIN sys.dm_exec_requests blk
    ON blk.session_id = r.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(blk.sql_handle) AS bk
WHERE r.blocking_session_id > 0
  AND r.wait_time > 5000  -- 5 seconds
ORDER BY r.wait_time DESC;

WITH BlockingChain AS (
    SELECT
        session_id,
        blocking_session_id,
        wait_time,
        CAST(session_id AS VARCHAR(MAX)) AS chain
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
    UNION ALL
    SELECT
        r.session_id,
        r.blocking_session_id,
        r.wait_time,
        bc.chain + ' → ' + CAST(r.session_id AS VARCHAR(10))
    FROM sys.dm_exec_requests r
    JOIN BlockingChain bc ON r.session_id = bc.blocking_session_id
)
SELECT DISTINCT
    blocking_session_id AS head_blocker,
    COUNT(*) OVER (PARTITION BY blocking_session_id) AS sessions_blocked,
    MAX(wait_time) OVER (PARTITION BY blocking_session_id) / 1000.0 AS max_wait_seconds
FROM BlockingChain
ORDER BY sessions_blocked DESC
OPTION (MAXRECURSION 20);

What This Tool Does

The Find Blocking Generator produces T-SQL queries that query SQL Server's dynamic management views (DMVs) to identify blocking sessions — sessions that are holding locks preventing other sessions from proceeding. The generated query shows the blocked session, the blocking session, wait time, wait type, database, and the SQL text of both sessions.

Three patterns are available: a standard blocking report, a recursive blocking chain query that identifies the root head blocker in multi-level chains, and a summary that counts how many sessions each head blocker is holding up.

How to Use It

• Set a minimum wait time to filter out normal short waits (5–10 seconds is typical)
• Choose whether to include SQL text — yes for diagnosis, no for a quick overview
• Choose the pattern: standard for most cases, chain when you need to trace multi-level blocking
• Click Generate SQL, copy, and run in SSMS or Azure Data Studio against the affected server
• Identify the head blocker session ID from the output
• Run KILL session_id; if needed, after investigating what the blocking session is doing

Understanding the Output Columns

How to Resolve Blocking

Once you identify the head blocker:

• Check what it is doing — is it a long-running transaction? An uncommitted transaction?
• Check for missing COMMIT/ROLLBACK — open transactions that never commit hold locks indefinitely
• Check for missing indexes — table scans hold locks longer than index seeks
• Kill if necessary — KILL session_id; — but understand why it was blocking first
• Long-term fix — optimize the blocking query, add indexes, use READ COMMITTED SNAPSHOT isolation, or restructure transaction scope

Privacy & Security

All SQL generation runs in your browser. No server names, query text, or diagnostic data is transmitted anywhere. The generated queries read from DMVs only — they do not modify any data. Viewing DMVs requires VIEW SERVER STATE permission.

Common Problems & Fixes

SQL Server Admin Toolkit

Blocking analysis is one of several SQL Server admin tasks:

• Format diagnostic queries for documentation and review
• Deduplicate blocking results if the query returns duplicate session rows
• Convert blocking start times from Unix timestamps to readable dates

Related SQL Tools