LCK_M_IX_LOW_PRIORITY Wait Type Explained

The lock manager creates this wait type when DDL operations request Intent Exclusive locks with low priority semantics. When ALTER TABLE or ALTER INDEX specifies WAIT_AT_LOW_PRIORITY, SQL Server places the request at the end of the lock queue instead of standard lock escalation behavior.

The lock compatibility matrix shows IX locks conflict with existing Shared (S) and Exclusive (X) locks. Under normal circumstances, DDL operations would terminate conflicting queries after the lock timeout period. With WAIT_AT_LOW_PRIORITY, the lock manager implements queue inversion, allowing existing queries to complete before granting the IX lock.

SQL Server 2014 introduced this functionality specifically for online index rebuilds and table alterations in high-availability scenarios. The scheduler maintains separate queues for normal and low-priority lock requests. When a low-priority IX lock request encounters conflicts, it enters a suspended state and registers this wait type.

The buffer pool continues serving read operations while the DDL waits. Lock escalation behavior differs significantly because low-priority requests never force lock promotion or query termination. This prevents maintenance operations from disrupting application queries but can extend maintenance windows indefinitely if long-running transactions persist.

Version differences are minimal since introduction in SQL Server 2014. SQL Server 2016 and later versions improved lock queue management efficiency but core mechanics remain unchanged. The wait type duration depends entirely on conflicting transaction completion times rather than system resources.

-- Current sessions experiencing LCK_M_IX_LOW_PRIORITY waits
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    s.program_name,
    s.host_name,
    t.text AS current_statement
FROM sys.dm_exec_requests r
INNER 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_IX_LOW_PRIORITY';

-- Lock requests and blocking chains for low priority operations
SELECT 
    tl.request_session_id AS waiting_spid,
    tl.resource_type,
    tl.resource_description,
    tl.request_mode,
    tl.request_type,
    bl.session_id AS blocking_spid,
    bt.text AS blocking_statement
FROM sys.dm_tran_locks tl
LEFT JOIN sys.dm_exec_requests bl ON tl.lock_owner_address = bl.task_address
OUTER APPLY sys.dm_exec_sql_text(bl.sql_handle) bt
WHERE tl.request_session_id IN (
    SELECT session_id FROM sys.dm_exec_requests 
    WHERE wait_type = 'LCK_M_IX_LOW_PRIORITY'
);

-- Historical wait statistics for low priority lock waits
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_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'LCK_M_IX_LOW_PRIORITY';

-- Active transactions that might be blocking low priority DDL
SELECT 
    s.session_id,
    s.program_name,
    s.host_name,
    s.login_name,
    t.transaction_begin_time,
    DATEDIFF(second, t.transaction_begin_time, GETDATE()) AS duration_seconds,
    dt.database_id,
    DB_NAME(dt.database_id) AS database_name
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
INNER JOIN sys.dm_tran_database_transactions dt ON t.transaction_id = dt.transaction_id
WHERE t.transaction_begin_time < DATEADD(minute, -5, GETDATE())
ORDER BY duration_seconds DESC;

-- Current ALTER operations using WAIT_AT_LOW_PRIORITY
SELECT 
    r.session_id,
    s.program_name,
    r.percent_complete,
    r.estimated_completion_time,
    t.text AS ddl_statement
FROM sys.dm_exec_requests r
INNER 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.command IN ('ALTER INDEX', 'ALTER TABLE')
    AND t.text LIKE '%WAIT_AT_LOW_PRIORITY%';

Kill long-running blocking transactions This script terminates transactions that have been running longer than specified thresholds and are potentially blocking low-priority DDL operations.

-- WARNING: Test thoroughly before production use
-- This will terminate active user sessions
DECLARE @max_duration_minutes INT = 30;
DECLARE @kill_sql NVARCHAR(100);

DECLARE kill_cursor CURSOR FOR
SELECT 'KILL ' + CAST(s.session_id AS VARCHAR(10))
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
WHERE DATEDIFF(minute, t.transaction_begin_time, GETDATE()) > @max_duration_minutes
    AND s.session_id > 50  -- Exclude system sessions
    AND s.session_id != @@SPID; -- Don't kill current session

OPEN kill_cursor;
FETCH NEXT FROM kill_cursor INTO @kill_sql;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Executing: ' + @kill_sql;
    EXEC sp_executesql @kill_sql;
    FETCH NEXT FROM kill_cursor INTO @kill_sql;
END;
CLOSE kill_cursor;
DEALLOCATE kill_cursor;

Modify existing ALTER operation to use timeout Cancels the current low-priority operation and restarts with MAX_DURATION timeout to prevent indefinite waits.

-- First, identify the blocking ALTER operation
-- Then cancel and restart with timeout
-- Replace with actual session_id and DDL command
KILL 123; -- Session running the ALTER with WAIT_AT_LOW_PRIORITY

-- Example restart with timeout (customize for your specific ALTER)
ALTER INDEX IX_YourIndex ON YourTable REBUILD
WITH (ONLINE = ON, WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = BLOCKERS));

Set database to single user mode temporarily Forces completion of maintenance operations by eliminating competing sessions. Use only during maintenance windows.

-- WARNING: This disconnects all users from the database
-- Only use during scheduled maintenance windows
USE master;
GO

-- Replace 'YourDatabase' with actual database name
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Run your maintenance operation here
-- ALTER INDEX ... or ALTER TABLE ...

-- Return to multi-user mode
ALTER DATABASE YourDatabase SET MULTI_USER;
GO

Create monitoring alert for excessive wait times Sets up automated detection when low-priority lock waits exceed acceptable thresholds.

-- Create alert for LCK_M_IX_LOW_PRIORITY waits exceeding 15 minutes
EXEC msdb.dbo.sp_add_alert 
    @name = N'Low Priority Lock Wait Alert',
    @message_id = 0,
    @severity = 0,
    @notification_message = N'Low priority DDL operation has been waiting over 15 minutes',
    @category_name = N'[Uncategorized]',
    @wmi_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
    @wmi_query = N'SELECT * FROM WAIT_INFO WHERE WaitType = "LCK_M_IX_LOW_PRIORITY" AND Duration > 900000';

Schedule DDL operations during maintenance windows when fewer concurrent queries run. Use MAX_DURATION with ABORT_AFTER_WAIT = BLOCKERS to prevent indefinite waits during business hours. The BLOCKERS option terminates conflicting sessions after the timeout period rather than queuing indefinitely.

Configure transaction log backup frequency to prevent long-running transactions from accumulating. Large transactions extend lock hold times and delay low-priority operations. Monitor sys.dm_tran_active_transactions regularly for transactions exceeding reasonable duration thresholds.

Implement proper indexing strategies to reduce lock contention. Poorly designed queries that perform table scans hold Shared locks longer, blocking Intent Exclusive requests. Use covering indexes and query optimization to minimize lock duration.

Consider using RESUMABLE = ON for supported ALTER INDEX operations in SQL Server 2017 and later. Resumable operations can pause and restart, reducing the impact of blocking sessions. This approach works better than WAIT_AT_LOW_PRIORITY for long-running index maintenance.

Set up monitoring for wait statistics trending. Baseline normal LCK_M_IX_LOW_PRIORITY wait times and alert when they exceed acceptable thresholds. Include this wait type in performance monitoring dashboards alongside other critical locking waits.

Use Resource Governor to limit query execution time for specific workloads that frequently cause blocking. Configure REQUEST_MAX_CPU_TIME_SEC and REQUEST_MAX_MEMORY_GRANT_PERCENT to prevent runaway queries from holding locks indefinitely.