LCK_M_RX_S_LOW_PRIORITY Wait Type Explained

This wait type emerges from SQL Server's low priority lock mechanism introduced in SQL Server 2014. When ALTER TABLE or ALTER INDEX operations specify WAIT_AT_LOW_PRIORITY, the lock manager assigns these operations a lower priority in the lock queue. The requesting thread waits for a shared range lock (RangeS-S) on an index key range, but yields precedence to normal priority transactions.

The lock manager maintains separate queues for different priority levels. Low priority operations enter a secondary queue that only processes when no normal priority requests exist for the same resource. The RX_S component indicates a range exclusive to shared lock conversion, where the operation needs to protect a range of index keys during schema modifications while allowing concurrent shared access.

SQL Server 2016 enhanced this mechanism by improving the lock escalation detection for low priority operations. SQL Server 2019 added better diagnostics in sys.dm_exec_requests, showing the low_priority_waiting flag. SQL Server 2022 refined the timeout handling to prevent indefinite waits when MAX_DURATION is specified.

The buffer pool and scheduler interactions remain standard, but the lock manager specifically delays these requests when higher priority operations compete for the same resources. This prevents schema modification operations from blocking critical user workloads while still allowing them to complete during quiet periods.

-- Current low priority lock waits with blocking details
SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS sql_text,
    r.command,
    CASE WHEN r.wait_type LIKE '%LOW_PRIORITY%' THEN 'Low Priority' ELSE 'Normal' END AS priority_level
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LCK_M_RX_S_LOW_PRIORITY'
ORDER BY r.wait_time DESC;

-- Lock details for low priority operations
SELECT 
    l.resource_type,
    l.resource_database_id,
    l.resource_associated_entity_id,
    l.resource_lock_partition,
    l.request_mode,
    l.request_type,
    l.request_status,
    s.session_id,
    s.login_name,
    s.program_name
FROM sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
WHERE EXISTS (
    SELECT 1 FROM sys.dm_exec_requests r 
    WHERE r.session_id = s.session_id 
    AND r.wait_type = 'LCK_M_RX_S_LOW_PRIORITY'
);

-- Historical wait statistics for low priority locks
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type LIKE '%LOW_PRIORITY%'
ORDER BY wait_time_ms DESC;

-- Currently running ALTER operations with low priority settings
SELECT 
    r.session_id,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    t.text,
    r.start_time,
    DATEDIFF(MINUTE, r.start_time, GETDATE()) AS runtime_minutes
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command IN ('ALTER TABLE', 'ALTER INDEX', 'CREATE INDEX')
AND (t.text LIKE '%WAIT_AT_LOW_PRIORITY%' OR r.wait_type LIKE '%LOW_PRIORITY%');

Increase MAX_DURATION for Low Priority Operations Extends the maximum wait time before the operation switches to normal priority or aborts.

-- Template for adjusting MAX_DURATION
-- Replace with your specific ALTER statement
ALTER INDEX [IX_YourIndex] ON [YourSchema].[YourTable] REBUILD
WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = SELF));

-- For ALTER TABLE operations
ALTER TABLE [YourSchema].[YourTable] ALTER COLUMN [YourColumn] VARCHAR(100)
WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS));

Test in development first. Longer MAX_DURATION values may delay schema changes during peak activity periods.

Switch to ABORT_AFTER_WAIT = BLOCKERS Forces blocking transactions to abort when MAX_DURATION expires, allowing the schema change to proceed.

-- Abort blocking transactions after wait period
ALTER INDEX [IX_YourIndex] ON [YourSchema].[YourTable] REBUILD
WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 2 MINUTES, ABORT_AFTER_WAIT = BLOCKERS));

Use with extreme caution in production. This will terminate user transactions that are blocking the operation.

Schedule Operations During Low Activity Periods Create a maintenance window approach using SQL Agent jobs with conditional logic.

-- Check current blocking before proceeding
DECLARE @BlockingCount INT;
SELECT @BlockingCount = COUNT(*) 
FROM sys.dm_exec_requests 
WHERE blocking_session_id > 0;

IF @BlockingCount < 5  -- Adjust threshold as needed
BEGIN
    ALTER INDEX [IX_YourIndex] ON [YourSchema].[YourTable] REBUILD
    WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF));
END
ELSE
BEGIN
    PRINT 'Deferred due to high blocking activity';
    -- Log for retry logic
END

Implement this pattern in SQL Agent jobs with retry logic for optimal maintenance scheduling.

Configure maintenance operations during established low-activity windows based on your application's usage patterns. Monitor sys.dm_exec_requests regularly to identify peak blocking periods and avoid scheduling schema changes during these times.

Implement a staging approach for large schema modifications. Break ALTER INDEX REBUILD operations into smaller segments using partitioning or process tables in batches during off-peak hours. This reduces the duration that low priority locks must wait for resources.

Set up monitoring for excessive low priority wait accumulation using extended events or custom alerts on sys.dm_os_wait_stats. Alert when LCK_M_RX_S_LOW_PRIORITY waits exceed your baseline thresholds, indicating either poor timing or insufficient maintenance windows.

Design application connection pooling to minimize long-running transactions that block schema modifications. Configure appropriate connection timeouts and implement transaction retry logic to reduce the window where user transactions conflict with maintenance operations.

Consider using online index operations (ONLINE = ON) where possible to reduce locking requirements, though low priority waits can still occur during the final lock escalation phase of online operations.