LCK_M_S_ABORT_BLOCKERS Wait Type Explained

This wait type exists only in SQL Server 2014 and later, introduced alongside the low priority wait options for DDL operations. When you execute ALTER TABLE or ALTER INDEX with WAIT_AT_LOW_PRIORITY and ABORT_AFTER_WAIT=BLOCKERS, SQL Server's lock manager places the DDL operation in a special queue with reduced priority.

The lock manager first attempts to acquire necessary locks at low priority, yielding to other transactions. During this phase, sessions show LCK_M_S_ABORT_BLOCKERS waits while waiting for shared locks. After the MAX_DURATION timeout expires, the lock manager elevates the operation's priority and terminates blocking transactions based on the specified abort action.

SQL Server 2016 improved the lock escalation logic for these operations, reducing unnecessary blocking. SQL Server 2019 enhanced the scheduler's handling of low priority operations, providing better resource distribution. In SQL Server 2022, the query processor became more efficient at detecting when ABORT_BLOCKERS operations can proceed without terminating blockers.

The wait specifically occurs in the lock manager's acquisition phase, not during the actual abort process. The scheduler maintains separate queues for low priority operations, and the lock compatibility matrix treats these requests differently from standard shared lock requests.

-- Identify current low priority DDL operations and their blocking chains
SELECT 
    r.session_id,
    r.blocking_session_id,
    t.text,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    s.login_name,
    s.program_name
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.wait_type = 'LCK_M_S_ABORT_BLOCKERS'
ORDER BY r.wait_time DESC;

-- Check for blocking transactions that may be terminated
SELECT 
    blocking.session_id AS blocking_spid,
    blocked.session_id AS blocked_spid,
    blocking_text.text AS blocking_query,
    blocked_text.text AS blocked_query,
    blocking.last_request_start_time,
    blocking.transaction_isolation_level
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_text
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
WHERE blocked.wait_type = 'LCK_M_S_ABORT_BLOCKERS';

-- Monitor lock details for affected resources
SELECT 
    tl.resource_type,
    tl.resource_database_id,
    tl.resource_associated_entity_id,
    tl.request_mode,
    tl.request_type,
    tl.request_status,
    tl.request_session_id,
    OBJECT_NAME(p.object_id, tl.resource_database_id) AS object_name
FROM sys.dm_tran_locks tl
LEFT JOIN sys.partitions p ON tl.resource_associated_entity_id = p.hobt_id
WHERE tl.request_session_id IN (
    SELECT session_id 
    FROM sys.dm_exec_requests 
    WHERE wait_type = 'LCK_M_S_ABORT_BLOCKERS'
);

-- Historical analysis of DDL operations with abort blockers
SELECT 
    object_name,
    statement_type,
    start_time,
    end_time,
    DATEDIFF(second, start_time, end_time) AS duration_seconds,
    cpu_time,
    logical_reads
FROM sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) st
WHERE st.text LIKE '%ABORT_AFTER_WAIT%BLOCKERS%'
ORDER BY start_time DESC;

Identify and terminate specific blocking transactions

-- Use with extreme caution - terminates active sessions
-- Test the session identification logic first
DECLARE @BlockingSpid INT;

SELECT @BlockingSpid = blocking_session_id
FROM sys.dm_exec_requests
WHERE wait_type = 'LCK_M_S_ABORT_BLOCKERS'
  AND blocking_session_id > 0;

IF @BlockingSpid IS NOT NULL
BEGIN
    PRINT 'Terminating blocking session: ' + CAST(@BlockingSpid AS VARCHAR(10));
    -- KILL @BlockingSpid; -- Uncomment after verification
END;

Warning: Only use in development first. Verify the blocking session before execution.

Adjust DDL operation timeout parameters

-- Increase MAX_DURATION for complex DDL operations
-- Example for ALTER INDEX with extended wait time
ALTER INDEX IX_YourIndex ON YourTable
REBUILD
WITH (
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 10 MINUTES,  -- Increased from default
        ABORT_AFTER_WAIT = BLOCKERS
    )
);

Impact: Reduces probability of LCK_M_S_ABORT_BLOCKERS waits by allowing more time for natural completion.

Convert to SELF termination strategy

-- Change abort behavior to terminate the DDL operation instead
ALTER INDEX IX_YourIndex ON YourTable
REBUILD
WITH (
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 5 MINUTES,
        ABORT_AFTER_WAIT = SELF  -- Changed from BLOCKERS
    )
);

Impact: DDL operation will abort itself rather than waiting indefinitely or killing other sessions.

Schedule DDL operations during low activity periods to minimize blocking scenarios. Implement connection pooling with proper timeout configurations to prevent long-running transactions from holding locks unnecessarily.

Configure MAX_DURATION values based on historical operation times plus 50% buffer. Monitor sys.dm_os_wait_stats regularly to establish baselines for LCK_M_S_ABORT_BLOCKERS waits during maintenance windows.

Use transaction isolation level READ_COMMITTED_SNAPSHOT to reduce shared lock duration for read operations. This eliminates many reader-writer blocking scenarios that trigger abort behavior.

Implement query timeout policies for applications, especially reporting queries that may hold shared locks for extended periods. Consider partitioning large tables to reduce lock scope during maintenance operations.

Set up alerts for sessions showing LCK_M_S_ABORT_BLOCKERS waits exceeding expected thresholds, typically 2-3 times the configured MAX_DURATION value.