LCK_M_SIU_ABORT_BLOCKERS Wait Type Explained

This wait type emerges from SQL Server's low priority lock mechanism introduced in SQL Server 2014. When ALTER TABLE or ALTER INDEX operations specify WAIT_AT_LOW_PRIORITY with ABORT_AFTER_WAIT=BLOCKERS, the operation initially requests a Shared with Intent Update (SIU) lock at low priority. The lock manager places these requests in a separate low priority queue to prevent DDL operations from blocking user queries immediately.

During the wait period, SQL Server's lock manager monitors for blocking sessions while the operation waits in the low priority queue. The SIU lock allows the operation to read data while indicating intent to perform updates, but it must wait behind existing incompatible locks. After the specified timeout expires, SQL Server's lock manager transitions from waiting to actively terminating the blocking sessions that hold incompatible locks.

The abort blockers mechanism works through the deadlock monitor, which runs every five seconds by default. When the timeout period elapses, the deadlock monitor identifies sessions holding locks incompatible with the waiting DDL operation and terminates them with error 1222. The DDL operation then acquires its required locks and proceeds.

SQL Server 2016 and later versions improved the precision of timeout calculations and reduced unnecessary wake-ups during the wait period. SQL Server 2019 enhanced the blocking detection logic to more accurately identify which sessions actually need termination. SQL Server 2022 added better integration with Query Store to track the impact of these abort operations.

-- Check current low priority DDL operations and their wait states
SELECT 
    s.session_id,
    r.wait_type,
    r.wait_time_ms,
    r.blocking_session_id,
    t.text AS sql_text,
    s.login_name,
    s.program_name
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.wait_type = 'LCK_M_SIU_ABORT_BLOCKERS';

-- Identify blocking sessions that may be terminated
SELECT 
    blocking.session_id AS blocking_spid,
    blocking.login_name AS blocking_user,
    blocked.session_id AS blocked_spid,
    blocked.wait_type,
    blocked.wait_time_ms,
    blocking_text.text AS blocking_query,
    blocked_text.text AS blocked_query
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_sessions blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_text
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
WHERE blocked.wait_type = 'LCK_M_SIU_ABORT_BLOCKERS';

-- Review recent DDL operations with abort blockers settings
SELECT 
    object_name,
    start_time,
    end_time,
    duration_ms,
    statement,
    error_number
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%ABORT_AFTER_WAIT%BLOCKERS%'
ORDER BY qs.last_execution_time DESC;

-- Monitor lock escalation and blocking patterns
SELECT 
    resource_type,
    resource_database_id,
    resource_description,
    request_mode,
    request_type,
    request_status,
    request_session_id
FROM sys.dm_tran_locks
WHERE resource_type IN ('OBJECT', 'HOBT', 'PAGE')
    AND request_session_id IN (
        SELECT session_id 
        FROM sys.dm_exec_requests 
        WHERE wait_type = 'LCK_M_SIU_ABORT_BLOCKERS'
    );

-- Monitor and log DDL operations before they start blocking
-- Run this before executing ALTER operations with ABORT_AFTER_WAIT
DECLARE @start_time DATETIME2 = GETDATE();

SELECT 
    'PRE-DDL CHECK' AS check_type,
    s.session_id,
    s.login_name,
    r.wait_type,
    r.wait_time_ms,
    r.blocking_session_id,
    t.text
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 s.session_id <> @@SPID
    AND r.wait_type IS NOT NULL;

-- Consider this approach for safer DDL execution
-- Test in development first to validate timeout values

-- Kill specific blocking sessions manually if DDL is critical
-- Use extreme caution, only after confirming the blocking session
-- WARNING: This terminates user connections, test thoroughly

DECLARE @blocking_spid INT = 0; -- Replace with actual SPID

IF @blocking_spid > 0
BEGIN
    PRINT 'Terminating session ' + CAST(@blocking_spid AS VARCHAR(10));
    -- Uncomment next line only after verification
    -- KILL @blocking_spid;
END
ELSE
BEGIN
    PRINT 'No blocking SPID specified. Review blocking sessions first.';
END

-- Alternative DDL approach using shorter timeouts and retry logic
-- This reduces the likelihood of long waits
DECLARE @retry_count INT = 0;
DECLARE @max_retries INT = 3;
DECLARE @success BIT = 0;

WHILE @retry_count < @max_retries AND @success = 0
BEGIN
    BEGIN TRY
        -- Replace with your actual ALTER statement
        -- ALTER TABLE your_table ADD your_column INT
        -- WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 30 SECONDS, ABORT_AFTER_WAIT = BLOCKERS));
        
        SET @success = 1;
        PRINT 'DDL operation completed successfully';
    END TRY
    BEGIN CATCH
        SET @retry_count = @retry_count + 1;
        PRINT 'Retry ' + CAST(@retry_count AS VARCHAR(2)) + ' failed: ' + ERROR_MESSAGE();
        WAITFOR DELAY '00:00:05'; -- Wait 5 seconds before retry
    END CATCH
END

Configure maintenance windows for DDL operations to minimize conflicts with user activity. Schedule ALTER TABLE and ALTER INDEX operations during low-usage periods when fewer sessions hold locks on target objects. Use shorter MAX_DURATION values (30-60 seconds) rather than extended timeouts to prevent operations from waiting indefinitely.

Implement connection monitoring to identify long-running transactions that frequently block DDL operations. Configure applications to use shorter transaction scopes and avoid holding locks during user interaction periods. Consider using READ_COMMITTED_SNAPSHOT isolation to reduce blocking scenarios.

Monitor sys.dm_os_wait_stats regularly to track LCK_M_SIU_ABORT_BLOCKERS occurrences and adjust DDL scheduling accordingly. Set up alerts when this wait type exceeds normal thresholds, indicating increased blocking patterns. Use Extended Events to capture detailed information about which sessions are being terminated and optimize application retry logic.

Establish DDL deployment standards that require WAIT_AT_LOW_PRIORITY for all schema changes in production environments. Document expected blocking sessions and coordinate with application teams to minimize transaction duration during deployment windows. Consider using online index rebuilds and partitioning strategies to reduce the scope of locking operations.