Blocking Chains - Diagnosis & Resolution

SQL Server's lock manager creates blocking chains when multiple sessions request incompatible locks on the same resource. The lock manager maintains a lock hash table where each resource (page, row, key, table) has an entry containing granted and waiting lock requests. When a session requests a lock incompatible with existing grants, it enters the wait queue for that resource.

The blocking chain forms when Session 1 holds an exclusive lock, Session 2 waits for a shared lock on the same resource while holding locks on other resources, and Session 3 waits for locks held by Session 2. Each session consumes a worker thread from the scheduler, reducing available threads for new requests. The lock manager's deadlock monitor runs every 5 seconds but only terminates circular dependencies, not linear blocking chains.

SQL Server 2016 introduced sys.dm_exec_session_wait_stats providing session-level wait statistics. SQL Server 2017 added sys.dm_exec_query_statistics_xml for real-time query execution plans. SQL Server 2019's Intelligent Query Processing can reduce blocking through batch mode on rowstore, but doesn't prevent application-level blocking issues.

Lock escalation thresholds (5000 locks per object) can reduce blocking by converting row locks to table locks, but this increases blocking scope.

-- Identify current blocking chains with session details
SELECT 
    r.session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    s.program_name,
    s.host_name,
    s.login_name,
    t.text AS sql_text,
    r.cpu_time,
    r.total_elapsed_time
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.blocking_session_id;

-- Build complete blocking chain hierarchy with lead blocker
WITH BlockingChain AS (
    SELECT 
        session_id,
        blocking_session_id,
        wait_type,
        wait_time,
        wait_resource,
        1 as Level
    FROM sys.dm_exec_requests 
    WHERE blocking_session_id <> 0
    
    UNION ALL
    
    SELECT 
        r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        r.wait_resource,
        bc.Level + 1
    FROM sys.dm_exec_requests r
    INNER JOIN BlockingChain bc ON r.session_id = bc.blocking_session_id
    WHERE r.blocking_session_id <> 0
)
SELECT 
    bc.*,
    s.program_name,
    s.login_name,
    t.text as sql_text
FROM BlockingChain bc
INNER JOIN sys.dm_exec_sessions s ON bc.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections c ON bc.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
ORDER BY Level, session_id;

-- Analyze lock waits by database and object
SELECT 
    DB_NAME(resource_database_id) AS database_name,
    resource_type,
    resource_subtype,
    request_mode,
    request_type,
    request_status,
    COUNT(*) as wait_count,
    SUM(CASE WHEN request_status = 'WAIT' THEN 1 ELSE 0 END) as waiting_requests
FROM sys.dm_tran_locks
WHERE resource_database_id > 4  -- Exclude system databases
GROUP BY resource_database_id, resource_type, resource_subtype, 
         request_mode, request_type, request_status
HAVING SUM(CASE WHEN request_status = 'WAIT' THEN 1 ELSE 0 END) > 0
ORDER BY waiting_requests DESC;

-- Show transaction details for blocking sessions
SELECT 
    s.session_id,
    dt.transaction_id,
    dt.transaction_begin_time,
    DATEDIFF(second, dt.transaction_begin_time, GETDATE()) as transaction_duration_seconds,
    dt.transaction_type,
    dt.transaction_state,
    s.open_transaction_count,
    s.transaction_isolation_level,
    s.lock_timeout
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_tran_session_transactions st ON s.session_id = st.session_id
INNER JOIN sys.dm_tran_database_transactions dt ON st.transaction_id = dt.transaction_id
WHERE s.session_id IN (
    SELECT DISTINCT blocking_session_id 
    FROM sys.dm_exec_requests 
    WHERE blocking_session_id <> 0
);

-- Monitor blocking wait statistics trends
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS percent_total_waits
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('LCK_M_S', 'LCK_M_X', 'LCK_M_IX', 'LCK_M_IS', 'LCK_M_U')
    AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

Kill lead blocker session (emergency only) Terminates the session at the root of the blocking chain. Use only when the blocking session is unresponsive and business impact is severe.

-- Find and kill lead blocker - USE WITH EXTREME CAUTION
DECLARE @LeadBlocker INT;

SELECT TOP 1 @LeadBlocker = r.blocking_session_id
FROM sys.dm_exec_requests r
WHERE r.blocking_session_id <> 0
    AND NOT EXISTS (
        SELECT 1 FROM sys.dm_exec_requests r2 
        WHERE r2.session_id = r.blocking_session_id
    );

-- Verify the session before killing
SELECT 
    s.session_id,
    s.program_name,
    s.host_name,
    s.login_name,
    t.text
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id = @LeadBlocker;

-- Uncomment to execute - TEST IN DEV FIRST
-- KILL @LeadBlocker;

Set query timeout for problematic application Prevents runaway queries from holding locks indefinitely by setting connection-level timeout.

-- Set query timeout at connection level
-- Execute this from the application connection, not SSMS
SET LOCK_TIMEOUT 30000; -- 30 seconds

-- Verify current setting
SELECT @@LOCK_TIMEOUT as current_lock_timeout_ms;

-- For existing sessions, you cannot change timeout
-- Application must reconnect with new timeout settings

Enable snapshot isolation to reduce blocking Snapshot isolation uses row versioning to provide consistent reads without shared locks, eliminating reader-writer blocking.

-- Enable snapshot isolation - requires brief exclusive database lock
-- Schedule during maintenance window
USE master;
GO

-- Check current snapshot isolation settings
SELECT 
    name,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
FROM sys.databases 
WHERE name = 'YourDatabaseName';

-- Enable snapshot isolation (allows SNAPSHOT isolation level)
ALTER DATABASE YourDatabaseName 
SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Enable read committed snapshot (makes READ COMMITTED use snapshots)
-- This eliminates most blocking scenarios automatically
ALTER DATABASE YourDatabaseName 
SET READ_COMMITTED_SNAPSHOT ON;

-- Monitor version store space usage after enabling
SELECT 
    SUM(version_store_reserved_page_count) * 8 / 1024 as VersionStoreMB,
    SUM(reserved_page_count) * 8 / 1024 as TempDbTotalMB
FROM sys.dm_db_file_space_usage;

Configure lock escalation thresholds Reduces lock memory consumption and potential blocking by escalating to table locks sooner on specific tables.

-- Disable lock escalation on frequently accessed small tables
-- This prevents table-level blocking but increases lock memory usage
ALTER TABLE dbo.YourFrequentlyAccessedTable
SET (LOCK_ESCALATION = DISABLE);

-- Enable lock escalation to partition level for partitioned tables
ALTER TABLE dbo.YourPartitionedTable
SET (LOCK_ESCALATION = AUTO);

-- Check current lock escalation settings
SELECT 
    SCHEMA_NAME(schema_id) as schema_name,
    name as table_name,
    lock_escalation_desc
FROM sys.tables
WHERE lock_escalation_desc <> 'TABLE'
ORDER BY schema_name, name;

Configure database for READ_COMMITTED_SNAPSHOT ON to eliminate most reader-writer blocking scenarios. This uses tempdb version store instead of shared locks for read operations.

Implement proper transaction scope in applications. Keep transactions short and commit immediately after completing logical units of work. Avoid user interaction within transaction boundaries.

Set appropriate lock timeout values at the application connection level (15-30 seconds for OLTP workloads). This prevents indefinite waiting and provides application-level error handling opportunities.

Monitor blocking chains with automated alerts when wait times exceed thresholds (typically 30 seconds). Use Extended Events session targeting lock waits rather than Profiler for production monitoring.

Design database schema to minimize lock contention. Use appropriate indexes to reduce lock duration, implement proper partitioning strategies for large tables, and consider columnstore indexes for read-heavy analytical workloads.

Configure max degree of parallelism and cost threshold for parallelism appropriately. Parallel queries hold locks longer and can create complex blocking scenarios. Set MAXDOP to number of physical cores per NUMA node for OLTP systems.

Review application queries for unnecessary locking hints like HOLDLOCK or SERIALIZABLE. Most applications should use default READ COMMITTED isolation level with snapshot isolation enabled at database level.