LCK_M_SIU Wait Type Explained

The SIU lock combines shared access with update intent, allowing a transaction to read data while signaling its intention to potentially modify it later. SQL Server's lock manager issues LCK_M_SIU waits when the lock compatibility matrix prevents acquisition due to existing Exclusive (X), Intent Exclusive (IX), or Update (U) locks on the same resource.

This wait occurs most frequently when transactions use SERIALIZABLE isolation level or explicit UPDLOCK hints. The lock manager maintains these waits in the lock wait list, with sessions queued in FIFO order. Unlike simple shared locks, SIU locks cannot coexist with update-intent locks from other transactions, creating serialization points that reduce concurrency.

SQL Server 2016 introduced lock memory optimizations that slightly reduced SIU lock overhead, while SQL Server 2019's intelligent query processing can sometimes avoid these locks through adaptive query plans. SQL Server 2022's parameter sensitive plan optimization may generate different execution plans that require fewer SIU locks depending on parameter values.

The lock escalation threshold (5,000 locks by default) can convert page-level SIU locks to table-level locks, potentially increasing wait duration. Lock partitioning, available on systems with 16+ CPUs, reduces SIU lock contention by distributing locks across multiple hash tables.

-- Current LCK_M_SIU waits with blocking details
SELECT 
    ws.session_id,
    ws.wait_duration_ms,
    ws.blocking_session_id,
    r.command,
    r.status,
    t.text AS current_statement,
    db_name(r.database_id) AS database_name
FROM sys.dm_os_waiting_tasks ws
INNER JOIN sys.dm_exec_requests r ON ws.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE ws.wait_type = 'LCK_M_SIU';

-- Lock details for SIU contention
SELECT 
    l.request_session_id,
    l.resource_type,
    l.resource_database_id,
    db_name(l.resource_database_id) AS database_name,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_status,
    o.name AS object_name,
    i.name AS index_name
FROM sys.dm_tran_locks l
LEFT JOIN sys.objects o ON l.resource_associated_entity_id = o.object_id
LEFT JOIN sys.indexes i ON o.object_id = i.object_id AND l.resource_subtype = i.index_id
WHERE l.request_mode = 'SIU' OR l.request_mode IN ('X', 'IX', 'U')
ORDER BY l.resource_database_id, l.resource_associated_entity_id;

-- Historical SIU wait statistics
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_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'LCK_M_SIU';

-- Sessions using isolation levels that generate SIU locks
SELECT 
    s.session_id,
    s.login_name,
    s.program_name,
    s.host_name,
    r.transaction_isolation_level,
    CASE r.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'ReadUncommitted'
        WHEN 2 THEN 'ReadCommitted'
        WHEN 3 THEN 'RepeatableRead'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END AS isolation_level_name
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE r.transaction_isolation_level IN (3, 4)
OR EXISTS (
    SELECT 1 FROM sys.dm_tran_locks l 
    WHERE l.request_session_id = s.session_id 
    AND l.request_mode = 'SIU'
);

-- Lock escalation candidates causing SIU waits
SELECT 
    p.object_id,
    o.name AS table_name,
    p.index_id,
    i.name AS index_name,
    p.lock_escalation_desc,
    COUNT(*) AS lock_count
FROM sys.dm_tran_locks l
INNER JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE l.request_mode = 'SIU'
AND l.resource_type = 'PAGE'
GROUP BY p.object_id, o.name, p.index_id, i.name, p.lock_escalation_desc
HAVING COUNT(*) > 1000
ORDER BY lock_count DESC;

Identify and terminate long-running transactions causing SIU contention

-- Find sessions holding locks that are blocking SIU requests
DECLARE @blocking_spid INT;

SELECT TOP 1 @blocking_spid = ws.blocking_session_id
FROM sys.dm_os_waiting_tasks ws
WHERE ws.wait_type = 'LCK_M_SIU'
AND ws.blocking_session_id IS NOT NULL
ORDER BY ws.wait_duration_ms DESC;

-- Review the blocking session details before killing
SELECT 
    s.session_id,
    s.login_name,
    s.program_name,
    s.last_request_start_time,
    r.start_time,
    r.status,
    t.text
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(ISNULL(r.sql_handle, s.last_request_sql_handle)) t
WHERE s.session_id = @blocking_spid;

-- Uncomment to kill after verification
-- KILL @blocking_spid;

Disable lock escalation for frequently contended tables

-- Identify tables with high SIU lock counts and disable escalation
-- TEST IN DEVELOPMENT FIRST - impacts memory usage
SELECT DISTINCT
    'ALTER TABLE ' + SCHEMA_NAME(o.schema_id) + '.' + o.name + 
    ' SET (LOCK_ESCALATION = DISABLE);' AS disable_escalation_sql
FROM sys.dm_tran_locks l
INNER JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
INNER JOIN sys.objects o ON p.object_id = o.object_id
WHERE l.request_mode = 'SIU'
AND o.type = 'U'
GROUP BY o.schema_id, o.name, o.object_id
HAVING COUNT(*) > 100;

-- Execute the generated statements after testing

Create filtered indexes to reduce lock scope

-- Generate filtered index suggestions for hot SIU lock resources
-- Reduces lock granularity by creating more selective access paths
WITH lock_resources AS (
    SELECT 
        l.resource_associated_entity_id,
        o.name AS table_name,
        o.schema_id,
        COUNT(*) as siu_locks
    FROM sys.dm_tran_locks l
    INNER JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
    INNER JOIN sys.objects o ON p.object_id = o.object_id
    WHERE l.request_mode = 'SIU'
    AND l.resource_type IN ('PAGE', 'KEY')
    GROUP BY l.resource_associated_entity_id, o.name, o.schema_id
    HAVING COUNT(*) > 50
)
SELECT 
    'CREATE NONCLUSTERED INDEX IX_' + lr.table_name + '_Filtered' +
    ' ON ' + SCHEMA_NAME(lr.schema_id) + '.' + lr.table_name +
    ' (column_list_here) WHERE (filter_condition_here);' AS suggested_index
FROM lock_resources lr
ORDER BY lr.siu_locks DESC;

-- Customize column_list and filter_condition based on query patterns

Adjust isolation level for problematic sessions

-- Script to change isolation level for current session
-- Use when SERIALIZABLE is unnecessarily strict
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- or
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; -- if snapshot isolation is enabled

-- Check if snapshot isolation is enabled
SELECT 
    name,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
FROM sys.databases 
WHERE database_id = DB_ID();

-- Enable snapshot isolation if needed (requires exclusive access)
-- ALTER DATABASE [your_database] SET ALLOW_SNAPSHOT_ISOLATION ON;
-- ALTER DATABASE [your_database] SET READ_COMMITTED_SNAPSHOT ON;

Configure appropriate isolation levels application-wide rather than relying on default SERIALIZABLE behavior. Enable snapshot isolation where read consistency is required without blocking writers. This eliminates most SIU lock contention for read-heavy workloads.

Implement proper indexing strategies that support query predicates with selective, non-clustered indexes. Missing indexes force table scans that acquire SIU locks on entire tables. Query Store helps identify parameter-sensitive queries that benefit from multiple execution plans.

Set lock escalation to TABLE only for small lookup tables and disable escalation for large transaction tables where concurrent access is critical. Monitor lock memory usage with sys.dm_os_memory_clerks to ensure sufficient memory prevents premature escalation.

Use query hints (NOLOCK, READUNCOMMITTED) judiciously for reporting queries where dirty reads are acceptable. Implement read-only routing for Always On availability groups to offload read workloads from the primary replica.

Configure trace flag 1211 to disable lock escalation globally if memory permits, though this increases lock manager overhead. SQL Server 2019+ automatic plan correction can prevent regression to plans that generate excessive SIU locks.

Monitor lock timeouts and deadlocks through extended events, focusing on sessions that consistently request SIU locks. Partition large tables to reduce lock scope and enable parallel DML operations across partition boundaries.