LCK_M_RX_X_LOW_PRIORITY Wait Type Explained

This wait type manifests exclusively with SQL Server 2014's introduction of the WAIT_AT_LOW_PRIORITY option for online index operations and ALTER TABLE statements. The lock manager implements a priority queuing mechanism where low priority requests yield to normal priority operations, preventing schema modification operations from blocking critical OLTP workloads.

The RX_X designation indicates a combination lock request: an exclusive lock on the current key value (X) and an exclusive range lock (RX) between the current and previous keys. This pattern occurs during index maintenance operations that need to modify both individual rows and the logical ordering between keys, such as online index rebuilds with WAIT_AT_LOW_PRIORITY.

The lock manager's priority queue maintains separate lists for normal and low priority requests. When a low priority operation encounters blocking, it enters this wait state rather than immediately competing with normal priority transactions. The operation periodically retries based on the MAX_DURATION and ABORT_AFTER_WAIT settings specified in the DDL statement.

SQL Server 2016 enhanced this mechanism with improved metadata visibility through sys.dm_exec_requests. SQL Server 2019 added better integration with Query Store for tracking these operations. SQL Server 2022 improved the retry logic to reduce unnecessary CPU overhead during extended waits.

-- Identify active low priority operations and their blocking chains
SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    t.text AS sql_text,
    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_RX_X_LOW_PRIORITY'
ORDER BY r.wait_time DESC;

-- Examine lock compatibility matrix for the affected resources
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,
    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 l.request_session_id IN (
    SELECT DISTINCT session_id 
    FROM sys.dm_exec_requests 
    WHERE wait_type = 'LCK_M_RX_X_LOW_PRIORITY'
)
ORDER BY l.resource_associated_entity_id, l.request_session_id;

-- Analyze index maintenance operations using WAIT_AT_LOW_PRIORITY
SELECT 
    ot.session_id,
    ot.task_address,
    ot.task_state,
    ot.exec_context_id,
    ot.pending_io_count,
    w.wait_duration_ms,
    w.wait_type,
    w.resource_description
FROM sys.dm_os_tasks ot
JOIN sys.dm_os_waiting_tasks w ON ot.task_address = w.waiting_task_address
WHERE w.wait_type = 'LCK_M_RX_X_LOW_PRIORITY';

-- Check for long-running transactions blocking low priority operations
SELECT 
    s.session_id,
    t.transaction_id,
    t.transaction_begin_time,
    DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) AS duration_minutes,
    s.last_request_start_time,
    st.text AS active_sql
FROM sys.dm_tran_session_transactions st_inner
JOIN sys.dm_tran_database_transactions t ON st_inner.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions s ON st_inner.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) st
WHERE t.database_id = DB_ID()
    AND DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) > 5
ORDER BY t.transaction_begin_time;

-- Monitor wait statistics accumulation for this wait type
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_RX_X_LOW_PRIORITY';

Terminate blocking long-running transactions

-- CAUTION: This will forcibly terminate active transactions
-- Verify the blocking session is safe to kill before executing
DECLARE @blocking_session_id INT;

SELECT TOP 1 @blocking_session_id = blocking_session_id
FROM sys.dm_exec_requests 
WHERE wait_type = 'LCK_M_RX_X_LOW_PRIORITY'
    AND blocking_session_id IS NOT NULL
    AND blocking_session_id != 0;

IF @blocking_session_id IS NOT NULL
BEGIN
    PRINT 'Killing session: ' + CAST(@blocking_session_id AS VARCHAR(10));
    -- KILL @blocking_session_id; -- Uncomment to execute
END

This script identifies and terminates the primary blocking session. Test thoroughly in development as this forcibly rolls back active transactions.

Modify low priority operation timeout settings

-- Increase MAX_DURATION for operations that consistently timeout
-- Example for online index rebuild with extended wait time
ALTER INDEX [IX_YourIndexName] ON [dbo].[YourTable] 
REBUILD 
WITH (
    ONLINE = ON,
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 10 MINUTES,
        ABORT_AFTER_WAIT = BLOCKERS
    )
);

This extends the maximum wait duration for low priority operations. Monitor system impact as longer waits can delay other maintenance operations.

Implement retry logic for failed operations

-- Retry pattern for operations that abort due to blocking
DECLARE @retry_count INT = 0;
DECLARE @max_retries INT = 3;
DECLARE @error_occurred BIT = 0;

WHILE @retry_count < @max_retries AND @error_occurred = 0
BEGIN
    BEGIN TRY
        ALTER INDEX [IX_YourIndexName] ON [dbo].[YourTable] 
        REBUILD 
        WITH (
            ONLINE = ON,
            WAIT_AT_LOW_PRIORITY (
                MAX_DURATION = 5 MINUTES,
                ABORT_AFTER_WAIT = SELF
            )
        );
        
        SET @error_occurred = 0; -- Success, exit loop
        BREAK;
    END TRY
    BEGIN CATCH
        SET @retry_count = @retry_count + 1;
        PRINT 'Retry attempt: ' + CAST(@retry_count AS VARCHAR(5));
        WAITFOR DELAY '00:02:00'; -- Wait 2 minutes between retries
        
        IF @retry_count >= @max_retries
            THROW; -- Re-raise the error after max retries
    END CATCH
END

This implements automatic retry logic for operations that abort due to blocking. Adjust retry intervals based on your workload patterns.

Schedule index maintenance operations during periods of minimal concurrent activity to reduce lock contention. Implement transaction scope discipline by keeping transactions short and avoiding manual transaction management in application code that runs during maintenance windows.

Configure appropriate WAIT_AT_LOW_PRIORITY settings based on your workload patterns. For systems with predictable quiet periods, use longer MAX_DURATION values with ABORT_AFTER_WAIT = BLOCKERS. For 24/7 OLTP systems, use shorter durations with ABORT_AFTER_WAIT = SELF to prevent maintenance operations from interfering with business operations.

Monitor transaction log growth patterns and implement proactive transaction log management to prevent scenarios where large transactions create extended blocking chains. Use sys.dm_tran_database_transactions to identify long-running transactions before they impact maintenance operations.

Establish baseline wait statistics for LCK_M_RX_X_LOW_PRIORITY waits during normal operations. Create alerts when wait times exceed historical norms by more than 200%, indicating potential blocking scenarios that require intervention.

Consider implementing readable secondary replicas for Always On Availability Groups to offload maintenance operations from primary replicas during peak usage periods, reducing the likelihood of lock contention scenarios.