LCK_M_S_LOW_PRIORITY Wait Type Explained

The low priority locking mechanism was introduced in SQL Server 2014 to address blocking scenarios during online index rebuilds and table alterations. When you specify WAIT_AT_LOW_PRIORITY with MAX_DURATION and ABORT_AFTER_WAIT options, SQL Server's lock manager implements a two-phase acquisition strategy.

During the first phase, the operation attempts normal lock acquisition. If blocked, it enters the second phase where it waits at low priority for the specified MAX_DURATION. The lock manager places these requests in a separate low priority queue within the lock compatibility matrix, allowing existing and new higher priority requests to jump ahead.

The wait type surfaces when the requesting session enters this low priority wait state. SQL Server 2016 enhanced this behavior by improving the lock escalation detection during low priority waits. SQL Server 2019 added better integration with Query Store for tracking these operations, while SQL Server 2022 improved the internal scheduling efficiency for low priority lock requests.

The lock manager coordinates with the scheduler to periodically check if the low priority request can proceed. If the MAX_DURATION expires, the ABORT_AFTER_WAIT setting determines whether to kill blockers (BLOCKERS), kill the low priority session itself (SELF), or exit without action (NONE).

-- Current low priority lock waits with operation details
SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS current_statement,
    r.command,
    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_S_LOW_PRIORITY';

-- Lock details for low priority operations
SELECT 
    l.resource_type,
    l.resource_database_id,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_type,
    l.request_status,
    l.request_session_id,
    OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) AS object_name
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id
WHERE r.wait_type = 'LCK_M_S_LOW_PRIORITY';

-- Historical low priority wait statistics
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'LCK_M_S_LOW_PRIORITY';

-- Active transactions blocking low priority requests
SELECT 
    s.session_id,
    s.login_name,
    s.program_name,
    s.host_name,
    t.transaction_begin_time,
    t.transaction_type,
    t.transaction_state,
    r.command,
    r.status
FROM sys.dm_tran_active_transactions t
INNER JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.session_id IN (
    SELECT DISTINCT blocking_session_id 
    FROM sys.dm_exec_requests 
    WHERE wait_type = 'LCK_M_S_LOW_PRIORITY' 
    AND blocking_session_id IS NOT NULL
);

Adjust Low Priority Settings for Current Operations

-- Monitor and potentially cancel long-running low priority operations
-- Review current operations first, then decide on action
DECLARE @session_id INT;
SELECT @session_id = session_id 
FROM sys.dm_exec_requests 
WHERE wait_type = 'LCK_M_S_LOW_PRIORITY' 
AND wait_time > 300000; -- 5 minutes

IF @session_id IS NOT NULL
BEGIN
    PRINT 'Killing session ' + CAST(@session_id AS VARCHAR(10)) + ' due to excessive low priority wait';
    -- KILL @session_id; -- Uncomment after verification
END

Test the session identification logic in dev first. Killing sessions terminates user work.

Kill Blocking Transactions for Critical Operations

-- Identify and terminate specific blocking sessions
-- Use only when low priority operation is critical and blockers are non-essential
SELECT 
    'KILL ' + CAST(blocking_session_id AS VARCHAR(10)) + ';' AS kill_command,
    s.login_name,
    s.program_name,
    r.wait_time
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.blocking_session_id = s.session_id
WHERE r.wait_type = 'LCK_M_S_LOW_PRIORITY'
AND r.wait_time > 600000 -- 10 minutes
AND s.session_id > 50; -- Avoid system sessions

-- Execute the generated KILL commands manually after review

Always verify what the blocking sessions are doing before killing them. Document the business justification.

Implement Retry Logic for Failed Low Priority Operations

-- Template for rebuilding indexes with progressive timeout increases
DECLARE @max_duration INT = 5; -- Start with 5 minutes
DECLARE @retry_count INT = 0;
DECLARE @max_retries INT = 3;

WHILE @retry_count < @max_retries
BEGIN
    BEGIN TRY
        EXEC('ALTER INDEX IX_YourIndex ON dbo.YourTable REBUILD 
              WITH (ONLINE = ON, 
                    WAIT_AT_LOW_PRIORITY (MAX_DURATION = ' + CAST(@max_duration AS VARCHAR(10)) + ' MINUTES, 
                                         ABORT_AFTER_WAIT = SELF))');
        BREAK; -- Success, exit loop
    END TRY
    BEGIN CATCH
        SET @retry_count = @retry_count + 1;
        SET @max_duration = @max_duration * 2; -- Double the wait time
        WAITFOR DELAY '00:02:00'; -- Wait 2 minutes before retry
        
        IF @retry_count >= @max_retries
            THROW; -- Re-throw the last error
    END CATCH
END

Test the retry intervals and maximum duration values in dev. Adjust based on your maintenance window constraints.

Configure online index operations during low-activity periods and use appropriate WAIT_AT_LOW_PRIORITY settings. Set MAX_DURATION based on your maintenance window length, typically 10-30 minutes for most environments. Use ABORT_AFTER_WAIT = BLOCKERS only when you can tolerate killing user sessions.

Monitor transaction log usage patterns to identify long-running transactions that commonly block maintenance operations. Implement application-level transaction boundaries to minimize lock duration. Consider using snapshot isolation levels for read operations that might conflict with maintenance tasks.

Establish automated monitoring for operations using low priority locks. Alert when wait times exceed 50% of the configured MAX_DURATION. Create runbooks for handling blocking scenarios during critical maintenance windows.

For highly concurrent OLTP systems, schedule index maintenance during dedicated maintenance windows or use partition switching strategies instead of online rebuilds. Configure Resource Governor to limit the impact of maintenance operations on production workloads.