LCK_M_X_LOW_PRIORITY Wait Type Explained

SQL Server's lock manager introduced prioritized locking in 2014 to support online operations that previously required exclusive table locks. When ALTER INDEX REBUILD ONLINE or ALTER TABLE operations specify WAIT_AT_LOW_PRIORITY, the lock manager places these requests in a separate low priority queue within the lock waiter list.

The lock manager processes lock requests using a two-tier system. Normal priority requests (including user queries, DML operations, and standard DDL) receive immediate consideration when compatible locks become available. Low priority requests only acquire locks when no normal priority waiters exist in the queue for the same resource.

During online index operations, SQL Server attempts to acquire a brief exclusive lock at the beginning and end of the operation. The low priority mechanism allows these operations to wait without blocking incoming user queries. The scheduler continues processing other work while the low priority request remains queued.

Lock escalation behavior remains unchanged for low priority requests. If the operation exceeds memory thresholds, SQL Server will still escalate to table-level locks, but the exclusive table lock request maintains its low priority status.

In SQL Server 2016 and later, the query optimizer gained better awareness of low priority operations during plan compilation. SQL Server 2019 improved the efficiency of lock queue management for scenarios with many concurrent low priority operations.

-- Current low priority lock waits with blocking details
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time_ms,
    r.blocking_session_id,
    t.text AS sql_text,
    r.wait_resource,
    DB_NAME(r.database_id) AS database_name
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LCK_M_X_LOW_PRIORITY'
ORDER BY r.wait_time_ms DESC;

-- Lock waiter queue showing priority conflicts
SELECT 
    l.resource_type,
    l.resource_database_id,
    DB_NAME(l.resource_database_id) AS database_name,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_type,
    l.request_status,
    l.request_session_id,
    r.wait_type,
    r.wait_time_ms
FROM sys.dm_tran_locks l
JOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id
WHERE l.resource_type IN ('OBJECT', 'HOBT', 'PAGE')
    AND (r.wait_type = 'LCK_M_X_LOW_PRIORITY' OR l.request_session_id IN 
        (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE wait_type = 'LCK_M_X_LOW_PRIORITY'))
ORDER BY l.resource_database_id, l.resource_associated_entity_id;

-- Active online operations that may cause low priority waits
SELECT 
    r.session_id,
    r.percent_complete,
    r.estimated_completion_time,
    r.command,
    t.text,
    r.start_time,
    DATEDIFF(minute, r.start_time, GETDATE()) AS minutes_running
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command LIKE '%INDEX%'
   OR t.text LIKE '%WAIT_AT_LOW_PRIORITY%'
ORDER BY r.start_time;

-- Historical wait statistics for low priority locking
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / waiting_tasks_count AS avg_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'LCK_M_X_LOW_PRIORITY'
    AND waiting_tasks_count > 0;

Terminate stuck low priority operations

-- Kill sessions with excessive low priority waits
-- TEST IN DEV FIRST - This will terminate active sessions
DECLARE @session_id INT;
DECLARE @sql NVARCHAR(100);

SELECT @session_id = session_id 
FROM sys.dm_exec_requests 
WHERE wait_type = 'LCK_M_X_LOW_PRIORITY' 
    AND wait_time_ms > 600000; -- 10 minutes

IF @session_id IS NOT NULL
BEGIN
    SET @sql = 'KILL ' + CAST(@session_id AS NVARCHAR(10));
    PRINT 'Executing: ' + @sql;
    -- EXEC sp_executesql @sql; -- Uncomment to execute
END;

Modify online operation timeout settings

-- Restart online index rebuild with shorter timeout
-- Replace table_name and index_name with actual values
ALTER INDEX [index_name] ON [schema].[table_name] 
REBUILD 
WITH (
    ONLINE = ON,
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 5 MINUTES,
        ABORT_AFTER_WAIT = BLOCKERS
    )
);

Switch to offline operation during maintenance window

-- Emergency offline rebuild to complete operation immediately
-- Use only during maintenance windows
ALTER INDEX [index_name] ON [schema].[table_name] 
REBUILD 
WITH (
    ONLINE = OFF,
    MAXDOP = 4,
    SORT_IN_TEMPDB = ON
);

Schedule online index maintenance during periods of lower concurrent activity to reduce lock contention. Configure MAX_DURATION and ABORT_AFTER_WAIT options appropriately for your maintenance windows. Values between 5-15 minutes typically work well for most environments.

Monitor index fragmentation proactively using maintenance plans that target specific fragmentation thresholds rather than rebuilding all indexes regardless of need. This reduces unnecessary low priority lock requests.

Implement query timeout monitoring to identify long-running queries that extend lock hold times. Sessions holding shared locks for extended periods will delay low priority exclusive lock acquisition.

Consider partitioning large tables to enable partition-level online operations, which reduce the scope of required locks and decrease wait times for low priority requests.

Use Resource Governor to limit the impact of maintenance operations on production workloads. Create separate resource pools for maintenance tasks with appropriate CPU and memory limits.

Configure alerts for LCK_M_X_LOW_PRIORITY waits exceeding reasonable thresholds (typically 10-15 minutes) to identify problematic operations before they impact availability.