LCK_M_SCH_M_LOW_PRIORITY Wait Type Explained

SQL Server's lock manager handles LCK_M_SCH_M_LOW_PRIORITY waits through the low priority locking mechanism introduced in SQL Server 2014. When you specify LOW_PRIORITY options with ALTER TABLE or ALTER INDEX, the lock manager places the schema modification request in a special queue rather than immediately attempting to acquire the SCH-M lock.

The lock manager evaluates three specific conditions before granting the low priority SCH-M lock: MAX_DURATION (maximum wait time), ABORT_AFTER_WAIT (action when timeout occurs), and whether blocking sessions exceed the threshold. During this evaluation period, concurrent SELECT, INSERT, UPDATE, and DELETE operations continue executing against the table, maintaining production availability.

Schema modification locks are exclusive locks that block all other operations. The low priority mechanism prevents ALTER operations from immediately blocking active queries by deferring lock acquisition. The lock manager monitors sys.dm_exec_requests and sys.dm_tran_locks to track blocking sessions and determines when it's safe to proceed with the schema change.

In SQL Server 2016 and later, Microsoft enhanced the low priority algorithm to better handle edge cases with long-running transactions. SQL Server 2019 improved memory management for tracking low priority requests in high-concurrency scenarios. SQL Server 2022 added telemetry for better monitoring of low priority operations through extended events.

-- Check current low priority schema modification waits
SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.command,
    t.text,
    r.percent_complete
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_LOW_PRIORITY';

-- Identify blocking sessions preventing schema modification
SELECT 
    l.request_session_id AS blocked_session,
    l.blocking_session_id,
    l.resource_database_id,
    l.resource_associated_entity_id,
    o.name AS object_name,
    l.resource_type,
    l.request_mode,
    r.command AS blocking_command
FROM sys.dm_tran_locks l
JOIN sys.objects o ON l.resource_associated_entity_id = o.object_id
LEFT JOIN sys.dm_exec_requests r ON l.blocking_session_id = r.session_id
WHERE l.request_session_id IN (
    SELECT session_id 
    FROM sys.dm_exec_requests 
    WHERE wait_type = 'LCK_M_SCH_M_LOW_PRIORITY'
);

-- Monitor low priority ALTER operation progress and settings
SELECT 
    r.session_id,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    r.wait_time / 1000.0 AS wait_seconds,
    t.text AS full_statement
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_LOW_PRIORITY'
   OR t.text LIKE '%LOW_PRIORITY%';

-- Check for long-running transactions that may block schema changes
SELECT 
    s.session_id,
    t.transaction_id,
    t.transaction_begin_time,
    DATEDIFF(minute, t.transaction_begin_time, GETDATE()) AS duration_minutes,
    s.status,
    r.command,
    st.text
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions s ON t.transaction_id = s.transaction_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE t.transaction_begin_time < DATEADD(minute, -5, GETDATE())
ORDER BY t.transaction_begin_time;

-- Extended events session for tracking low priority operations
SELECT 
    event_data.value('(event/@name)[1]', 'VARCHAR(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS timestamp,
    event_data.value('(event/data[@name="database_id"]/value)[1]', 'INT') AS database_id,
    event_data.value('(event/data[@name="object_id"]/value)[1]', 'INT') AS object_id,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'BIGINT') AS duration_microseconds
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_sessions s
    JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
    WHERE s.name = 'system_health'
) AS xml_data
CROSS APPLY target_data.nodes('//RingBufferTarget/event') AS XEventData(event_data)
WHERE event_data.value('(event/@name)[1]', 'VARCHAR(50)') LIKE '%lock%'
   OR event_data.value('(event/data[@name="wait_type"]/text)[1]', 'VARCHAR(100)') = 'LCK_M_SCH_M_LOW_PRIORITY';

Kill specific blocking sessions after verification

-- CAUTION: Verify the blocking session before killing
-- This terminates sessions that are blocking low priority schema modifications
DECLARE @BlockingSessionId INT;
DECLARE @BlockedSession INT;

SELECT TOP 1 
    @BlockingSessionId = blocking_session_id,
    @BlockedSession = request_session_id
FROM sys.dm_tran_locks l
WHERE l.request_session_id IN (
    SELECT session_id 
    FROM sys.dm_exec_requests 
    WHERE wait_type = 'LCK_M_SCH_M_LOW_PRIORITY'
)
AND blocking_session_id > 0;

-- Review what the blocking session is doing before killing
SELECT session_id, status, command, last_request_start_time
FROM sys.dm_exec_sessions 
WHERE session_id = @BlockingSessionId;

-- Uncomment to execute the kill after verification
-- KILL @BlockingSessionId;

Modify ALTER statement to use different low priority settings

-- Increase MAX_DURATION if the current timeout is too aggressive
-- Example: Change from 1 minute to 5 minutes with different abort behavior
/*
Original problematic statement might look like:
ALTER INDEX IX_YourIndex ON dbo.YourTable REBUILD 
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = NONE)));

Modified version with longer timeout and kill blockers:
*/
ALTER INDEX IX_YourIndex ON dbo.YourTable REBUILD 
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
-- Test this configuration in development first
-- ABORT_AFTER_WAIT = BLOCKERS will kill blocking sessions
-- ABORT_AFTER_WAIT = SELF will cancel the ALTER operation
-- ABORT_AFTER_WAIT = NONE will wait indefinitely

Cancel the low priority operation gracefully

-- Cancel the waiting schema modification operation
DECLARE @SessionId INT;

SELECT @SessionId = session_id
FROM sys.dm_exec_requests
WHERE wait_type = 'LCK_M_SCH_M_LOW_PRIORITY';

IF @SessionId IS NOT NULL
BEGIN
    -- Cancel the request gracefully
    KILL @SessionId;
    
    PRINT 'Cancelled low priority schema modification session: ' + CAST(@SessionId AS VARCHAR(10));
    
    -- Verify cancellation
    SELECT session_id, status, command
    FROM sys.dm_exec_requests
    WHERE session_id = @SessionId;
END
ELSE
    PRINT 'No active low priority schema modification operations found';

Set session-level lock timeout for schema operations

-- Set lock timeout for the session performing schema modifications
-- This prevents indefinite waits when not using low priority options
SET LOCK_TIMEOUT 300000; -- 5 minutes in milliseconds

-- Your ALTER statement here
-- ALTER TABLE YourTable ADD YourColumn INT;

-- Reset to default (indefinite wait)
SET LOCK_TIMEOUT -1;

-- Note: This affects all lock waits in the session, not just schema locks
-- Use this when you cannot modify the ALTER statement to include LOW_PRIORITY

Configure ALTER INDEX and ALTER TABLE operations with appropriate LOW_PRIORITY settings during maintenance windows. Set MAX_DURATION to reasonable values (typically 2-10 minutes) based on your workload patterns and use ABORT_AFTER_WAIT = BLOCKERS for automated maintenance, ABORT_AFTER_WAIT = SELF for interactive sessions.

Implement transaction log monitoring to identify long-running transactions that frequently block schema modifications. Create alerts when transactions exceed 30 minutes duration, as these are primary causes of schema lock waits. Use SQL Server Agent alerts on wait statistics to detect when LCK_M_SCH_M_LOW_PRIORITY waits exceed normal thresholds.

Schedule schema modifications during low-activity periods identified through baseline performance monitoring. Implement read-only routing for Always On availability groups to redirect read workloads away from primary replicas during maintenance operations. Consider using online index operations with resumable options in SQL Server 2017+ to handle interruptions gracefully.

Establish monitoring for blocking chains using sp_WhoIsActive or custom scripts that track sys.dm_os_waiting_tasks. Create extended events sessions to capture detailed information about lock waits and blocking patterns. Monitor the ring buffer and system health session for lock escalation events that may indicate suboptimal indexing strategies causing excessive locking.