LCK_M_UIX_LOW_PRIORITY Wait Type Explained

This wait type manifests exclusively during ALTER TABLE and ALTER INDEX operations that specify the WAIT_AT_LOW_PRIORITY clause. The lock manager implements a priority-based queuing mechanism where low priority operations voluntarily yield to higher priority requests, even if they arrived later.

When ALTER TABLE or ALTER INDEX executes with low priority options, SQL Server's lock manager places the request in a separate low priority queue. The scheduler periodically checks if higher priority operations (normal DML, DDL without low priority flags) are waiting for conflicting locks. If conflicts exist, the low priority operation releases its position and re-queues, generating this wait type.

The internal mechanism uses a combination of the lock manager's hierarchical locking system and the scheduler's cooperative yielding model. SQL Server 2014 introduced this to prevent schema modification operations from blocking critical OLTP workloads during maintenance windows.

SQL Server 2016 enhanced the implementation by improving the frequency of priority checks, reducing unnecessary wait accumulation. SQL Server 2019 added better integration with Query Store to track the impact of low priority operations on overall workload performance. SQL Server 2022 introduced adaptive thresholds that dynamically adjust yielding behavior based on system load.

The UIX (Update Intent Exclusive) component indicates the operation needs to modify table structure while allowing concurrent reads until the final schema modification phase. This differs from standard exclusive locks that immediately block all access.

-- Identify active low priority operations and their wait details
SELECT 
    s.session_id,
    s.login_name,
    r.command,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS query_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_UIX_LOW_PRIORITY';

-- Check for blocking chains involving low priority operations
SELECT 
    blocked.session_id AS blocked_session,
    blocking.session_id AS blocking_session,
    blocked.wait_type,
    blocked.wait_resource,
    blocked.wait_time,
    blocking_text.text AS blocking_query,
    blocked_text.text AS blocked_query
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_text
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
WHERE blocked.wait_type = 'LCK_M_UIX_LOW_PRIORITY';

-- Historical analysis of low priority wait patterns
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_per_task_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'LCK_M_UIX_LOW_PRIORITY'
AND waiting_tasks_count > 0;

-- Identify tables with active schema modifications
SELECT 
    t.name AS table_name,
    i.name AS index_name,
    i.type_desc,
    s.name AS schema_name,
    OBJECT_ID AS object_id
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.indexes i ON t.object_id = i.object_id
WHERE EXISTS (
    SELECT 1 FROM sys.dm_tran_locks l 
    WHERE l.resource_associated_entity_id = t.object_id 
    AND l.request_mode IN ('UIX', 'X')
);

-- Check ALTER operations with WAIT_AT_LOW_PRIORITY in plan cache
SELECT 
    cp.plan_handle,
    cp.usecounts,
    st.text,
    cp.cacheobjtype,
    cp.objtype,
    cp.size_in_bytes
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE '%WAIT_AT_LOW_PRIORITY%'
AND st.text LIKE '%ALTER%';

Adjust low priority timeout settings for ALTER operations

-- Modify existing ALTER operations to use more aggressive timeout
-- Replace MAX_DURATION with appropriate value based on maintenance window
ALTER INDEX [your_index_name] ON [your_table_name] REBUILD
WITH (
    ONLINE = ON,
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 5 MINUTES,  -- Increase from default 0
        ABORT_AFTER_WAIT = BLOCKERS  -- Kill blocking sessions after timeout
    )
);
-- TEST IN DEVELOPMENT: Verify blocking sessions are acceptable to terminate
-- IMPACT: May terminate user sessions that exceed the timeout period

Cancel low priority operations experiencing excessive waits

-- Kill sessions with prolonged LCK_M_UIX_LOW_PRIORITY waits
DECLARE @session_id INT;
DECLARE @wait_time_threshold_ms INT = 300000; -- 5 minutes

SELECT TOP 1 @session_id = r.session_id
FROM sys.dm_exec_requests r
WHERE r.wait_type = 'LCK_M_UIX_LOW_PRIORITY'
AND r.wait_time > @wait_time_threshold_ms
ORDER BY r.wait_time DESC;

IF @session_id IS NOT NULL
BEGIN
    PRINT 'Killing session ' + CAST(@session_id AS VARCHAR(10)) + ' due to excessive wait time';
    -- Uncomment the next line after testing
    -- KILL @session_id;
END;
-- TEST IN DEVELOPMENT: Ensure cancellation doesn't corrupt ongoing operations
-- IMPACT: Terminates ALTER operation, may need to restart maintenance task

Switch to standard priority for time-sensitive operations

-- Remove WAIT_AT_LOW_PRIORITY for critical maintenance operations
-- Use during dedicated maintenance windows where blocking is acceptable
ALTER INDEX [your_index_name] ON [your_table_name] REBUILD
WITH (
    ONLINE = ON
    -- Removed WAIT_AT_LOW_PRIORITY clause
);
-- CAVEAT: This will block user operations until completion
-- IMPACT: Faster completion but potential user impact during business hours

Implement batch processing for large tables

-- Break large ALTER operations into smaller chunks to reduce blocking
-- Example: Rebuild indexes on partitioned tables one partition at a time
DECLARE @partition_number INT = 1;
DECLARE @max_partitions INT;

SELECT @max_partitions = COUNT(DISTINCT partition_number) 
FROM sys.partitions 
WHERE object_id = OBJECT_ID('your_partitioned_table');

WHILE @partition_number <= @max_partitions
BEGIN
    ALTER INDEX [your_index_name] ON [your_partitioned_table] REBUILD 
    PARTITION = @partition_number
    WITH (
        ONLINE = ON,
        WAIT_AT_LOW_PRIORITY (
            MAX_DURATION = 2 MINUTES,
            ABORT_AFTER_WAIT = SELF
        )
    );
    
    SET @partition_number = @partition_number + 1;
    WAITFOR DELAY '00:00:30';  -- Pause between partitions
END;
-- TEST IN DEVELOPMENT: Verify partition-level rebuilds meet performance requirements
-- IMPACT: Longer total runtime but reduced blocking impact per operation

Schedule ALTER operations with WAIT_AT_LOW_PRIORITY during maintenance windows when concurrent user activity is minimal. Monitor sys.dm_os_wait_stats regularly to establish baseline wait patterns for low priority operations.

Configure MAX_DURATION values based on actual maintenance window durations rather than using defaults. Set ABORT_AFTER_WAIT to BLOCKERS only when terminating blocking sessions is acceptable within your SLA requirements.

Implement partition-level maintenance for large tables to minimize lock duration and reduce the likelihood of extended low priority waits. Use SQL Server Agent jobs with appropriate retry logic to handle operations that timeout due to priority conflicts.

Consider using resumable online index operations (SQL Server 2017+) for large tables, allowing operations to pause and resume rather than waiting indefinitely. Monitor Query Store data to identify patterns where low priority operations consistently experience long waits, indicating need for different scheduling or approach.

Establish monitoring alerts when LCK_M_UIX_LOW_PRIORITY waits exceed predetermined thresholds, typically 10-15 minutes depending on maintenance window constraints. This prevents operations from running indefinitely during high-activity periods.