LCK_M_SCH_M_ABORT_BLOCKERS Wait Type Explained

This wait type emerges from SQL Server's low priority lock mechanism, specifically when ALTER TABLE or ALTER INDEX operations specify WAIT_AT_LOW_PRIORITY with ABORT_AFTER_WAIT=BLOCKERS. The lock manager places these DDL requests in a special low priority queue to prevent them from blocking concurrent SELECT, INSERT, UPDATE, and DELETE operations.

When the DDL operation cannot immediately acquire the required Schema Modification (Sch-M) lock due to existing shared locks or active transactions, SQL Server enters this wait state. The operation waits for the specified MAX_DURATION period while monitoring for blocking sessions. If blockers persist beyond the timeout, SQL Server terminates the blocking sessions rather than the DDL operation itself.

The internal scheduler treats these requests differently from standard Sch-M lock requests. Standard DDL operations queue behind existing locks in FIFO order, potentially blocking all subsequent operations. Low priority DDL operations yield to ongoing DML operations, only acquiring locks when no conflicts exist or when the timeout expires and blockers are terminated.

SQL Server 2016 enhanced this mechanism with improved deadlock detection for low priority operations. SQL Server 2019 added better integration with Query Store to track the performance impact of terminated sessions. SQL Server 2022 provides more granular wait statistics specifically for low priority lock scenarios.

-- Check current low priority DDL operations and their wait times
SELECT 
    s.session_id,
    s.login_name,
    r.command,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS sql_text,
    r.percent_complete
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_SCH_M_ABORT_BLOCKERS';

-- Identify sessions holding locks that could be blocking low priority DDL
SELECT DISTINCT
    l.request_session_id AS blocking_session,
    s.login_name,
    s.program_name,
    l.resource_type,
    l.resource_database_id,
    l.resource_associated_entity_id,
    OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) AS object_name,
    l.request_mode,
    l.request_status
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
WHERE l.resource_type IN ('OBJECT', 'PAGE', 'KEY', 'RID')
    AND l.request_status = 'GRANT'
    AND EXISTS (
        SELECT 1 FROM sys.dm_exec_requests r 
        WHERE r.wait_type = 'LCK_M_SCH_M_ABORT_BLOCKERS'
            AND r.wait_resource LIKE '%' + CAST(l.resource_database_id AS VARCHAR) + '%'
    );

-- Monitor low priority lock timeouts and aborted sessions
SELECT 
    event_time,
    database_name,
    object_name,
    statement,
    duration_ms,
    cpu_time,
    logical_reads,
    wait_stats
FROM sys.fn_xe_file_target_read_file('low_priority_lock_*.xel', NULL, NULL, NULL)
WHERE object_name IN ('lock_timeout', 'lock_abort_blocker');

-- Analyze historical patterns of schema lock waits
SELECT 
    DATEPART(hour, r.start_time) AS hour_of_day,
    COUNT(*) AS wait_occurrences,
    AVG(r.total_elapsed_time) AS avg_duration_ms,
    MAX(r.total_elapsed_time) AS max_duration_ms
FROM sys.dm_exec_requests r
WHERE r.wait_type = 'LCK_M_SCH_M_ABORT_BLOCKERS'
    AND r.start_time >= DATEADD(day, -7, GETDATE())
GROUP BY DATEPART(hour, r.start_time)
ORDER BY hour_of_day;

Terminate blocking sessions immediately This script identifies and kills sessions preventing low priority DDL operations from proceeding.

-- WARNING: This terminates active user sessions. Test thoroughly.
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + 'KILL ' + CAST(blocking_session_id AS VARCHAR) + '; '
FROM (
    SELECT DISTINCT l.request_session_id AS blocking_session_id
    FROM sys.dm_tran_locks l
    WHERE EXISTS (
        SELECT 1 FROM sys.dm_exec_requests r 
        WHERE r.wait_type = 'LCK_M_SCH_M_ABORT_BLOCKERS'
    )
    AND l.request_status = 'GRANT'
    AND l.resource_type IN ('OBJECT', 'PAGE', 'KEY')
) blockers;

PRINT 'Sessions to terminate: ' + @sql;
-- Uncomment to execute: EXEC sp_executesql @sql;

Test in development first. Monitor for application errors after execution.

Adjust low priority lock timeout Increases the MAX_DURATION for currently waiting operations by resubmitting with longer timeout.

-- Query currently waiting DDL operations
SELECT 
    r.session_id,
    t.text,
    r.wait_time,
    r.command
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LCK_M_SCH_M_ABORT_BLOCKERS';

-- Example: Resubmit ALTER INDEX with longer timeout
-- ALTER INDEX IX_YourIndex ON YourTable REBUILD 
-- WITH (ONLINE = ON, WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = BLOCKERS));

Cancel the existing operation first, then resubmit with appropriate timeout based on maintenance window.

Convert to standard priority operation Removes low priority restrictions to allow normal lock escalation behavior.

-- Cancel low priority operation and resubmit as standard DDL
-- First, identify the exact DDL command from diagnostic queries above
-- Then resubmit without WAIT_AT_LOW_PRIORITY clause

-- Example transformation:
-- FROM: ALTER INDEX IX_Example ON TableName REBUILD WITH (WAIT_AT_LOW_PRIORITY (...))
-- TO:   ALTER INDEX IX_Example ON TableName REBUILD WITH (ONLINE = ON);

This may cause blocking but ensures DDL completion. Schedule during low activity periods.

Configure appropriate MAX_DURATION values based on your maintenance windows and typical transaction lengths. Monitor average transaction duration using sys.dm_tran_active_transactions and set timeouts to 2-3 times the 95th percentile transaction duration.

Implement connection pooling timeouts that align with your low priority lock durations. Applications should handle connection terminations gracefully when ABORT_AFTER_WAIT=BLOCKERS kills their sessions.

Schedule DDL operations during periods of minimal concurrent activity. Use Query Store or Extended Events to identify low-activity time windows for each database. Avoid running low priority DDL during peak transaction processing hours.

Establish monitoring alerts for LCK_M_SCH_M_ABORT_BLOCKERS waits exceeding 50% of your configured MAX_DURATION. This provides early warning before timeout and session termination occurs.

Consider using ABORT_AFTER_WAIT=SELF for operations where DDL completion is less critical than maintaining user session availability. This setting terminates the DDL operation rather than blocking sessions when timeouts occur.