LCK_M_BU_LOW_PRIORITY Wait Type Explained

LCK_M_BU_LOW_PRIORITY specifically occurs when a bulk operation (such as BULK INSERT with TABLOCK) needs to acquire a Bulk Update (BU) lock but the request uses the low priority wait mechanism. SQL Server 2014 introduced low priority wait options to prevent blocking issues during maintenance operations by allowing certain requests to wait with reduced priority.

This wait is distinct from the Sch-M variant. Online index rebuilds with WAIT_AT_LOW_PRIORITY primarily deal with Schema Modification (Sch-M) locks and produce LCK_M_SCH_M_LOW_PRIORITY waits. The BU low priority variant is specific to acquiring a BU lock at low priority while bulk loading.

The lock manager uses a priority queue system where low priority requests yield CPU scheduler time to normal priority operations. This differs from standard lock waits because the session voluntarily surrenders its position in the lock queue rather than blocking indefinitely. The ABORT_AFTER_WAIT setting determines whether the operation kills blockers, kills itself, or continues waiting.

SQL Server 2016 enhanced this behavior by improving the lock escalation algorithms during online operations. SQL Server 2019 added better telemetry through extended events for tracking low priority wait patterns. SQL Server 2022 refined the scheduler integration to reduce unnecessary context switches during these waits.

-- Check active low priority DDL operations and their wait times
SELECT 
    r.session_id,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    t.text,
    r.percent_complete
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LCK_M_BU_LOW_PRIORITY'
ORDER BY r.wait_time DESC;

-- Identify blocking transactions preventing DDL completion
SELECT 
    blocking.session_id AS blocking_spid,
    blocked.session_id AS blocked_spid,
    blocking_text.text AS blocking_query,
    blocked_text.text AS blocked_query,
    blocking.login_name,
    blocking.program_name,
    blocked.wait_time / 1000.0 AS wait_seconds
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_sessions 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_BU_LOW_PRIORITY';

-- Check WAIT_AT_LOW_PRIORITY configuration for running operations
SELECT 
    r.session_id,
    r.command,
    r.percent_complete,
    SUBSTRING(t.text, 1, 200) AS query_text,
    CASE 
        WHEN CHARINDEX('ABORT_AFTER_WAIT = NONE', UPPER(t.text)) > 0 THEN 'NONE'
        WHEN CHARINDEX('ABORT_AFTER_WAIT = SELF', UPPER(t.text)) > 0 THEN 'SELF'
        WHEN CHARINDEX('ABORT_AFTER_WAIT = BLOCKERS', UPPER(t.text)) > 0 THEN 'BLOCKERS'
        ELSE 'NOT_SPECIFIED'
    END AS abort_after_wait_option
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command LIKE '%INDEX%' OR r.command LIKE '%ALTER%'
    AND UPPER(t.text) LIKE '%WAIT_AT_LOW_PRIORITY%';

-- Historical analysis of low priority wait patterns
SELECT 
    DATEADD(HOUR, DATEDIFF(HOUR, 0, end_time), 0) AS hour_bucket,
    COUNT(*) AS wait_occurrences,
    AVG(duration) AS avg_duration_ms,
    MAX(duration) AS max_duration_ms,
    AVG(CAST(cpu_time AS BIGINT)) AS avg_cpu_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
WHERE UPPER(t.text) LIKE '%WAIT_AT_LOW_PRIORITY%'
    AND qs.last_execution_time > DATEADD(DAY, -7, GETDATE())
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, last_execution_time), 0)
ORDER BY hour_bucket DESC;

Terminate blocking sessions for critical maintenance

-- Kill sessions blocking low priority DDL operations
-- WARNING: Test in development first, will terminate user connections
DECLARE @BlockingSpid INT;
DECLARE blocking_cursor CURSOR FOR
SELECT DISTINCT r.blocking_session_id
FROM sys.dm_exec_requests r
WHERE r.wait_type = 'LCK_M_BU_LOW_PRIORITY'
    AND r.blocking_session_id > 0
    AND r.blocking_session_id != @@SPID;

OPEN blocking_cursor;
FETCH NEXT FROM blocking_cursor INTO @BlockingSpid;

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

CLOSE blocking_cursor;
DEALLOCATE blocking_cursor;

Modify existing DDL operation to use BLOCKERS option

-- Cancel current operation and restart with ABORT_AFTER_WAIT = BLOCKERS
-- This will automatically kill blocking sessions after the timeout
ALTER INDEX [IX_YourIndex] ON [YourTable] REBUILD 
WITH (
    ONLINE = ON,
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 5 MINUTES,
        ABORT_AFTER_WAIT = BLOCKERS
    )
);

Reschedule maintenance during low activity periods

-- Check current activity levels before proceeding with maintenance
DECLARE @ActiveConnections INT;
SELECT @ActiveConnections = COUNT(*)
FROM sys.dm_exec_sessions 
WHERE is_user_process = 1 
    AND status = 'running'
    AND session_id != @@SPID;

IF @ActiveConnections > 10
BEGIN
    PRINT 'High activity detected (' + CAST(@ActiveConnections AS VARCHAR(10)) + ' sessions). Consider rescheduling maintenance.';
    -- Optionally exit or wait
    WAITFOR DELAY '00:05:00'; -- Wait 5 minutes
END
ELSE
BEGIN
    PRINT 'Proceeding with maintenance during low activity period.';
    -- Execute your DDL operations here
END

Schedule maintenance operations during established maintenance windows when transaction log activity is minimal. Configure WAIT_AT_LOW_PRIORITY with ABORT_AFTER_WAIT = BLOCKERS for automated blocking session termination, but validate that killed sessions can safely reconnect.

Monitor long-running transactions before maintenance windows using sys.dm_tran_active_transactions. Transactions holding shared locks on metadata will block schema modification locks regardless of priority settings. Implement connection pooling timeouts that align with your MAX_DURATION settings to prevent indefinite waits.

Use SQL Server Agent alerts to notify administrators when LCK_M_BU_LOW_PRIORITY waits exceed 15 minutes. This indicates either insufficient maintenance windows or unexpected blocking patterns requiring intervention.

Consider implementing online index rebuilds in smaller batches using partition-level operations on large tables. This reduces the scope of locking and minimizes the impact of any blocking scenarios. Enable query store to identify queries that frequently hold locks during maintenance windows.