LCK_M_RS_S_LOW_PRIORITY Wait Type Explained

This wait type materializes exclusively during online index rebuilds, reorganizations, or ALTER TABLE operations configured with the WAIT_AT_LOW_PRIORITY option, introduced in SQL Server 2014. The lock manager implements a priority-based queuing system where low priority operations yield CPU scheduling and lock acquisition to normal priority transactions.

When an online operation requests a shared range lock (RS_S) on a key range, the lock manager evaluates existing and queued lock requests. If higher priority sessions hold conflicting locks or are queued ahead, the low priority session enters this wait state. The range lock protects against phantom reads during the online operation's serializable isolation level requirements.

The internal scheduler honors the MAX_DURATION and ABORT_AFTER_WAIT settings specified in the WAIT_AT_LOW_PRIORITY clause. SQL Server 2016 enhanced the priority mechanism to better handle lock escalation scenarios, while SQL Server 2019 improved the efficiency of range lock acquisition during heavy concurrent workloads.

The wait occurs specifically during the metadata lock acquisition phase of online operations, not during the actual data movement phase. This distinguishes it from standard locking waits because the session voluntarily yields rather than being blocked by an incompatible lock holder.

-- Identify active low priority operations and their wait times
SELECT 
    s.session_id,
    r.command,
    r.percent_complete,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    t.text AS current_sql,
    s.login_name,
    s.program_name
FROM sys.dm_exec_requests r
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_RS_S_LOW_PRIORITY'
   OR r.last_wait_type = 'LCK_M_RS_S_LOW_PRIORITY';

-- Analyze blocking chains affecting low priority operations
SELECT 
    blocked.session_id AS blocked_session,
    blocked.wait_type,
    blocked.wait_resource,
    blocking.session_id AS blocking_session,
    blocking_sql.text AS blocking_statement,
    blocked_sql.text AS blocked_statement
FROM sys.dm_exec_requests blocked
LEFT JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_sql
OUTER APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_sql
WHERE blocked.wait_type = 'LCK_M_RS_S_LOW_PRIORITY';

-- Monitor lock waits and timeout settings for low priority operations
SELECT 
    o.name AS table_name,
    i.name AS index_name,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.last_user_seek,
    p.rows AS row_count
FROM sys.objects o
JOIN sys.indexes i ON o.object_id = i.object_id
JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE EXISTS (
    SELECT 1 FROM sys.dm_exec_requests r
    WHERE r.wait_type = 'LCK_M_RS_S_LOW_PRIORITY'
    AND r.wait_resource LIKE '%' + CAST(o.object_id AS VARCHAR(20)) + '%'
);

-- Check current ALTER INDEX operations with WAIT_AT_LOW_PRIORITY settings
SELECT 
    r.session_id,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    r.total_elapsed_time,
    t.text,
    CASE 
        WHEN t.text LIKE '%ABORT_AFTER_WAIT%' THEN 'ABORT_AFTER_WAIT configured'
        WHEN t.text LIKE '%MAX_DURATION%' THEN 'MAX_DURATION configured'
        ELSE 'Default low priority settings'
    END AS priority_config
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command LIKE '%ALTER%INDEX%'
   OR r.command LIKE '%ALTER%TABLE%';

Adjust WAIT_AT_LOW_PRIORITY timeout values

-- Increase MAX_DURATION for better completion rates during busy periods
-- Replace with your actual table and index names
ALTER INDEX [IX_YourIndex] ON [dbo].[YourTable]
REBUILD 
WITH (
    ONLINE = ON,
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 10 MINUTES,
        ABORT_AFTER_WAIT = SELF
    )
);
-- CAVEAT: Test timeout values in development to balance completion vs. blocking
-- Higher MAX_DURATION increases chance of completion but extends potential blocking

Kill blocking sessions for critical maintenance windows

-- Identify and optionally terminate sessions blocking low priority operations
DECLARE @BlockingSessionId INT;

SELECT @BlockingSessionId = blocking_session_id
FROM sys.dm_exec_requests 
WHERE wait_type = 'LCK_M_RS_S_LOW_PRIORITY'
AND blocking_session_id > 0;

IF @BlockingSessionId IS NOT NULL
BEGIN
    -- Uncomment the following line only after verifying the session can be safely killed
    -- KILL @BlockingSessionId;
    PRINT 'Blocking session ID: ' + CAST(@BlockingSessionId AS VARCHAR(10));
END
-- WARNING: Only kill sessions after confirming they are not critical business processes
-- Always coordinate with application teams before terminating user sessions

Switch to offline maintenance during heavy activity periods

-- Alternative offline rebuild when low priority waits are excessive
-- Monitor wait times first, then execute during maintenance window
BEGIN TRANSACTION;

ALTER INDEX [IX_YourIndex] ON [dbo].[YourTable]
REBUILD 
WITH (
    ONLINE = OFF,
    FILLFACTOR = 90,
    SORT_IN_TEMPDB = ON
);

-- Verify index integrity after rebuild
DBCC CHECKDB('[YourDatabase]') WITH NO_INFOMSGS, PHYSICAL_ONLY;

COMMIT TRANSACTION;
-- IMPACT: Causes blocking during rebuild but eliminates low priority wait issues
-- Schedule during maintenance windows with minimal user activity

Configure WAIT_AT_LOW_PRIORITY parameters based on your workload patterns. Set MAX_DURATION to 2-5 minutes during business hours and 15-30 minutes during maintenance windows. Use ABORT_AFTER_WAIT = SELF for non-critical maintenance to prevent cascading blocks.

Schedule online index operations during periods of lower transaction volume. Monitor sys.dm_db_index_usage_stats to identify optimal timing when user activity drops. Implement automated scripts that check for active transactions before starting maintenance operations.

Establish connection pooling limits and query timeouts in applications to prevent long-running transactions that block maintenance operations. Configure Resource Governor to limit concurrent maintenance operations during business hours, preventing multiple low priority operations from competing for the same resources.

Create monitoring alerts when LCK_M_RS_S_LOW_PRIORITY waits exceed 5 minutes, indicating potential scheduling conflicts. Implement gradual index maintenance strategies that spread operations across multiple maintenance windows rather than rebuilding large indexes in single operations.