LCK_M_IS_LOW_PRIORITY Wait Type Explained

SQL Server's lock manager implements a priority queue system for lock requests introduced in SQL Server 2014. When ALTER INDEX REBUILD ONLINE or ALTER TABLE operations specify WAIT_AT_LOW_PRIORITY, the lock manager places these requests at the back of the lock wait queue behind normal priority requests.

The lock manager maintains separate queues for different lock priority levels. Low priority requests only acquire locks when no normal or high priority requests are waiting. This mechanism prevents maintenance operations from blocking user transactions, but creates scenarios where maintenance operations wait indefinitely if the system has continuous normal priority activity.

Intent Shared locks are acquired during schema modification operations to ensure compatibility with concurrent read operations. The low priority variant means the requesting session will yield CPU cycles and remain in a waiting state until all higher priority lock requests are satisfied.

SQL Server 2016 and later versions improved the lock manager's priority handling by implementing more granular timeout mechanisms and better integration with the Resource Governor. SQL Server 2019 introduced enhanced telemetry for tracking low priority lock waits through extended events.

-- Check active low priority lock waits with blocking chain
SELECT 
    s.session_id,
    s.blocking_session_id,
    r.wait_type,
    r.wait_time_ms,
    r.resource_description,
    t.text AS sql_text,
    s.program_name
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_IS_LOW_PRIORITY';

-- Identify objects involved in low priority lock contention
SELECT 
    l.resource_database_id,
    DB_NAME(l.resource_database_id) AS database_name,
    l.resource_associated_entity_id,
    OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) AS object_name,
    l.request_mode,
    l.request_status,
    s.session_id,
    s.program_name
FROM sys.dm_tran_locks l
INNER 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_IS_LOW_PRIORITY'
);

-- Check ALTER INDEX operations with WAIT_AT_LOW_PRIORITY settings
SELECT 
    s.session_id,
    r.percent_complete,
    r.estimated_completion_time,
    r.command,
    t.text AS current_statement,
    r.wait_type,
    r.wait_time_ms
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 LIKE '%INDEX%'
AND (r.wait_type = 'LCK_M_IS_LOW_PRIORITY' OR t.text LIKE '%WAIT_AT_LOW_PRIORITY%');

-- Monitor lock wait statistics for low priority operations
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'LCK_M_IS_LOW_PRIORITY';

Adjust WAIT_AT_LOW_PRIORITY timeout for stalled operations

-- Kill long-running low priority operation and restart with higher timeout
-- TEST IN DEV: Verify index rebuild completion strategy
DECLARE @sql NVARCHAR(MAX);
DECLARE @session_id INT;

-- Find the stalled session
SELECT @session_id = session_id 
FROM sys.dm_exec_requests 
WHERE wait_type = 'LCK_M_IS_LOW_PRIORITY' 
AND wait_time_ms > 300000; -- 5 minutes

-- Kill the session (comment out in production until verified)
-- KILL @session_id;

-- Restart with modified WAIT_AT_LOW_PRIORITY settings
SET @sql = 'ALTER INDEX [your_index_name] ON [schema].[table_name] 
REBUILD WITH (ONLINE = ON, 
WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, 
ABORT_AFTER_WAIT = BLOCKERS));';

-- EXEC sp_executesql @sql;
PRINT @sql; -- Review before execution

Implement maintenance window for operations

-- Schedule low priority operations during low activity periods
-- Requires SQL Agent job or custom scheduling logic
IF DATEPART(HOUR, GETDATE()) BETWEEN 2 AND 5  -- 2 AM to 5 AM
BEGIN
    ALTER INDEX [your_index_name] ON [schema].[table_name] 
    REBUILD WITH (ONLINE = ON, 
    WAIT_AT_LOW_PRIORITY (MAX_DURATION = 30 MINUTES, 
    ABORT_AFTER_WAIT = BLOCKERS));
END
ELSE
BEGIN
    PRINT 'Maintenance window not active. Index rebuild skipped.';
END

Force completion with SELF termination

-- Use ABORT_AFTER_WAIT = SELF to terminate low priority operation cleanly
-- WARNING: This will roll back the operation, not complete it
ALTER INDEX [problematic_index] ON [schema].[table_name] 
REBUILD WITH (ONLINE = ON, 
WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, 
ABORT_AFTER_WAIT = SELF));
-- Expected impact: Operation terminates, changes rolled back, no blocking

Configure maintenance operations with appropriate MAX_DURATION values based on historical blocking patterns. Set MAX_DURATION to 15-30 minutes for most environments, using ABORT_AFTER_WAIT = BLOCKERS to terminate blocking transactions rather than the maintenance operation.

Schedule index maintenance during identified low-activity windows using SQL Agent jobs with activity-based conditional logic. Monitor sys.dm_exec_requests for concurrent user activity before starting maintenance operations.

Implement Resource Governor workload groups for maintenance operations to ensure they receive adequate CPU time when competing with user workloads. Create separate resource pools for maintenance activities with minimum CPU guarantees.

Configure extended events sessions to capture LCK_M_IS_LOW_PRIORITY waits exceeding defined thresholds. Set up automated alerts when wait times exceed 10 minutes to identify problematic maintenance operations before they cause application timeouts.

Consider using resumable index operations in SQL Server 2017+ for large tables, allowing operations to pause during high activity periods and resume automatically. This eliminates the need for low priority waits by providing native pause/resume capability.