LCK_M_SCH_S_LOW_PRIORITY Wait Type Explained

This wait type surfaces when SQL Server's lock manager processes ALTER statements configured with LOW_PRIORITY semantics. The lock manager places these operations at the end of the lock request queue, allowing normal-priority operations to proceed first. The LOW_PRIORITY mechanism was introduced in SQL Server 2014 specifically for online index operations and ALTER TABLE statements to reduce blocking impact on OLTP workloads.

The wait manifests when the operation requires a Schema Modification (SCH-M) lock but finds existing incompatible locks like shared table locks, row locks with certain isolation levels, or other schema locks. The lock manager honors the LOW_PRIORITY directive by continuously checking for lock availability rather than blocking higher-priority operations.

In SQL Server 2016 and later, the resumable online index rebuild feature interacts with this wait type differently. Resumable operations can pause and resume around this wait, whereas non-resumable operations must wait continuously. SQL Server 2019 enhanced the lock manager's priority queue handling, reducing false positives in wait statistics for LOW_PRIORITY operations.

The internal scheduler treats these waits as resource waits rather than signal waits, meaning they don't consume CPU cycles while waiting. The lock manager samples lock availability at regular intervals defined by the MAX_DURATION and ABORT_AFTER_WAIT parameters specified in the DDL statement.

-- Active schema lock requests and their priority levels
SELECT 
    r.session_id,
    r.request_id,
    r.blocking_session_id,
    t.resource_type,
    t.resource_database_id,
    t.resource_associated_entity_id,
    t.request_mode,
    t.request_status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.last_wait_type
FROM sys.dm_tran_locks t
INNER JOIN sys.dm_exec_requests r ON t.request_session_id = r.session_id
WHERE t.resource_type = 'OBJECT'
    AND r.wait_type LIKE 'LCK_M_SCH%'
ORDER BY r.wait_time DESC;

-- Schema lock chains and blocking relationships
WITH BlockingChain AS (
    SELECT DISTINCT
        r1.session_id as blocked_session,
        r1.blocking_session_id as blocking_session,
        r1.wait_type,
        r1.wait_time,
        r1.command as blocked_command,
        r2.command as blocking_command
    FROM sys.dm_exec_requests r1
    LEFT JOIN sys.dm_exec_requests r2 ON r1.blocking_session_id = r2.session_id
    WHERE r1.wait_type LIKE 'LCK_M_SCH%'
)
SELECT *,
    CASE WHEN blocking_session IS NULL THEN 'HEAD BLOCKER' 
         ELSE 'BLOCKED' END as chain_position
FROM BlockingChain
ORDER BY wait_time DESC;

-- Current LOW_PRIORITY operations and their parameters
SELECT 
    s.session_id,
    r.command,
    r.percent_complete,
    r.wait_type,
    r.wait_time,
    r.estimated_completion_time,
    t.text as sql_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 r.wait_type = 'LCK_M_SCH_S_LOW_PRIORITY'
    OR t.text LIKE '%LOW_PRIORITY%'
ORDER BY r.wait_time DESC;

-- Historical pattern analysis for schema lock waits
SELECT 
    wait_type,
    AVG(wait_time_ms) as avg_wait_ms,
    MAX(wait_time_ms) as max_wait_ms,
    SUM(signal_wait_time_ms) as total_signal_wait_ms,
    waiting_tasks_count,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) as pct_of_waits
FROM sys.dm_os_wait_stats 
WHERE wait_type LIKE 'LCK_M_SCH%'
    AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

Identify and kill blocking sessions for urgent LOW_PRIORITY operations

-- WARNING: Test in development first. This kills active sessions.
DECLARE @BlockingSPID INT;
DECLARE blocking_cursor CURSOR FOR
    SELECT DISTINCT r1.blocking_session_id
    FROM sys.dm_exec_requests r1
    WHERE r1.wait_type = 'LCK_M_SCH_S_LOW_PRIORITY'
        AND r1.blocking_session_id IS NOT NULL
        AND r1.wait_time > 300000; -- 5 minutes

OPEN blocking_cursor;
FETCH NEXT FROM blocking_cursor INTO @BlockingSPID;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Killing session ' + CAST(@BlockingSPID AS VARCHAR(10));
    EXEC('KILL ' + @BlockingSPID);
    FETCH NEXT FROM blocking_cursor INTO @BlockingSPID;
END;

CLOSE blocking_cursor;
DEALLOCATE blocking_cursor;

Convert existing operations to use appropriate LOW_PRIORITY settings

-- Template for adding LOW_PRIORITY to problematic ALTER INDEX operations
-- Modify parameters based on your maintenance window requirements
/*
ALTER INDEX [your_index_name] ON [schema].[table_name] REBUILD
WITH (
    ONLINE = ON,
    MAXDOP = 2, -- Adjust based on system capacity
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 30 MINUTES,
        ABORT_AFTER_WAIT = BLOCKERS
    )
);
*/

-- Example for resumable online index rebuilds (SQL 2017+)
/*
ALTER INDEX [your_index_name] ON [schema].[table_name] REBUILD
WITH (
    ONLINE = ON,
    RESUMABLE = ON,
    MAX_DURATION = 60 MINUTES,
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 5 MINUTES,
        ABORT_AFTER_WAIT = SELF
    )
);
*/

Emergency session termination for deadlocked LOW_PRIORITY operations

-- Use when LOW_PRIORITY operations are stuck indefinitely
-- This forces completion by clearing blocking locks
SET DEADLOCK_PRIORITY LOW; -- Ensure this session yields to others

-- Find sessions holding schema locks on specific objects
SELECT 
    'KILL ' + CAST(tl.request_session_id AS VARCHAR(10)) + ';' as kill_command,
    tl.request_session_id,
    tl.resource_database_id,
    tl.resource_associated_entity_id,
    OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id) as object_name
FROM sys.dm_tran_locks tl
WHERE tl.resource_type = 'OBJECT'
    AND tl.request_mode IN ('S', 'IS', 'IX')
    AND EXISTS (
        SELECT 1 FROM sys.dm_exec_requests r 
        WHERE r.wait_type = 'LCK_M_SCH_S_LOW_PRIORITY'
            AND r.wait_time > 600000 -- 10 minutes
    );
-- Execute the generated KILL statements manually after review

Configure maintenance operations with appropriate LOW_PRIORITY parameters based on your service level agreements. Set MAX_DURATION to match your maintenance windows and use ABORT_AFTER_WAIT = BLOCKERS for critical operations that must complete.

Implement resumable online index operations in SQL Server 2017 and later to allow automatic pause and resume around blocking scenarios. Monitor sys.dm_exec_requests during maintenance windows to identify operations approaching their MAX_DURATION limits.

Schedule intensive DDL operations during low-activity periods and coordinate with application teams to minimize long-running transactions that hold schema locks. Use Resource Governor workload groups to limit concurrent schema modification operations and prevent resource contention.

Enable Query Store and Extended Events to track blocking patterns around schema modifications. Create alerts for LCK_M_SCH_S_LOW_PRIORITY waits exceeding your defined thresholds, typically 30-60 seconds for OLTP environments.

Configure applications to use appropriate transaction isolation levels and minimize transaction duration. Avoid holding transactions open across user input or network calls, as these create the schema locks that LOW_PRIORITY operations must wait for.