LCK_M_U_LOW_PRIORITY Wait Type Explained

The low priority locking mechanism fundamentally changes how the Lock Manager handles lock acquisition for DDL operations. Traditional ALTER operations acquire Schema Modification (Sch-M) locks immediately, blocking all concurrent access. The WAIT_AT_LOW_PRIORITY option instructs the Lock Manager to use a different acquisition strategy where the DDL operation periodically yields control to allow other operations to complete.

When SQL Server encounters a WAIT_AT_LOW_PRIORITY request, it enters a cycle where it attempts lock acquisition, waits for a specified duration, then either retries or aborts based on the configured abort action. During the wait phase, the requesting session shows LCK_M_U_LOW_PRIORITY waits. The Lock Manager maintains separate queues for normal and low priority requests, processing normal priority requests first.

The Update lock component (U) indicates the operation needs to examine existing data before potentially upgrading to an exclusive lock. This pattern is common in ALTER TABLE operations that must scan existing data to validate constraints or perform data transformations.

SQL Server 2016 improved the low priority mechanism's interaction with Resource Governor, allowing better control over which sessions can use low priority waits. SQL Server 2019 enhanced the lock escalation behavior to better handle mixed workloads with both normal and low priority operations.

-- Identify active low priority lock waits with blocking details
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    t.text AS sql_text,
    r.command,
    s.login_name,
    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_U_LOW_PRIORITY';

-- Examine lock requests and their priorities
SELECT 
    tl.resource_type,
    tl.resource_database_id,
    tl.resource_associated_entity_id,
    tl.request_mode,
    tl.request_type,
    tl.request_status,
    tl.request_session_id,
    es.login_name,
    OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id) AS object_name
FROM sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions es ON tl.request_session_id = es.session_id
WHERE tl.request_session_id IN (
    SELECT session_id 
    FROM sys.dm_exec_requests 
    WHERE wait_type = 'LCK_M_U_LOW_PRIORITY'
);

-- Monitor ALTER operations with WAIT_AT_LOW_PRIORITY currently running
SELECT 
    r.session_id,
    r.start_time,
    r.total_elapsed_time,
    r.percent_complete,
    t.text AS current_statement,
    r.estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE t.text LIKE '%WAIT_AT_LOW_PRIORITY%'
   OR r.wait_type = 'LCK_M_U_LOW_PRIORITY';

-- Check historical wait statistics for low priority locking patterns
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_U_LOW_PRIORITY'
   OR wait_type LIKE 'LCK_M_%_LOW_PRIORITY';

Terminate stuck low priority operations

-- Kill sessions with excessive low priority waits
-- WARNING: Test impact before running in production
DECLARE @session_id INT;
DECLARE session_cursor CURSOR FOR
SELECT r.session_id
FROM sys.dm_exec_requests r
WHERE r.wait_type = 'LCK_M_U_LOW_PRIORITY'
  AND r.wait_time > 300000; -- 5 minutes

OPEN session_cursor;
FETCH NEXT FROM session_cursor INTO @session_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Killing session ' + CAST(@session_id AS VARCHAR(10));
    EXEC('KILL ' + @session_id);
    FETCH NEXT FROM session_cursor INTO @session_id;
END;

CLOSE session_cursor;
DEALLOCATE session_cursor;

Adjust WAIT_AT_LOW_PRIORITY parameters for running operations

-- Cancel and restart with more aggressive settings
-- This requires identifying and manually restarting the operation
-- Example for ALTER INDEX with adjusted parameters:

-- First, identify the current operation
SELECT r.session_id, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LCK_M_U_LOW_PRIORITY';

-- Cancel the operation (replace 123 with actual session_id)
-- KILL 123;

-- Restart with shorter wait time and kill blockers action
-- ALTER INDEX IX_YourIndex ON YourTable REBUILD
-- WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));

Monitor and alert on excessive low priority waits

-- Create monitoring job to detect problematic patterns
IF OBJECT_ID('tempdb..#LowPriorityAlert') IS NOT NULL DROP TABLE #LowPriorityAlert;

SELECT 
    r.session_id,
    r.wait_time,
    s.login_name,
    t.text,
    GETDATE() AS alert_time
INTO #LowPriorityAlert
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_U_LOW_PRIORITY'
  AND r.wait_time > 600000; -- 10 minutes

-- Send alert if any sessions found
IF EXISTS (SELECT 1 FROM #LowPriorityAlert)
BEGIN
    -- Integrate with your alerting system
    PRINT 'Alert: Long running low priority operations detected';
    SELECT * FROM #LowPriorityAlert;
END;

Configure WAIT_AT_LOW_PRIORITY parameters based on workload characteristics. Use MAX_DURATION values between 1-5 minutes for OLTP systems, longer for data warehouse environments. Set ABORT_AFTER_WAIT to BLOCKERS for operations that must complete, or SELF for operations that can be deferred.

Schedule DDL operations during maintenance windows when possible to avoid conflicts with user queries. For operations that must run during business hours, monitor active connections before starting and consider using connection throttling.

Implement query monitoring to identify long running queries that might block low priority operations. Configure Resource Governor to limit concurrent DDL operations and prevent resource contention that exacerbates locking conflicts.

Establish baseline measurements for typical ALTER operation completion times without WAIT_AT_LOW_PRIORITY to set appropriate MAX_DURATION values. Monitor sys.dm_os_wait_stats regularly to identify trends in low priority wait accumulation that indicate workload pattern changes requiring parameter adjustments.