LCK_M_SCH_M Wait Type Explained

The SQL Server lock manager enforces schema stability through a two-tier locking mechanism. Schema Shared (Sch-S) locks protect against structural changes during query execution, while Schema Modification (Sch-M) locks provide exclusive access for DDL operations. When a DDL statement requests an Sch-M lock, it must wait for all existing Sch-S locks to release.

The lock manager queues LCK_M_SCH_M requests in FIFO order within the lock hash table. Each active SELECT, INSERT, UPDATE, or DELETE statement holds an Sch-S lock for the duration of its execution. Long-running queries, especially those with READ UNCOMMITTED or snapshot isolation levels, can block DDL operations indefinitely because they maintain Sch-S locks throughout their execution.

SQL Server 2016 introduced improvements to lock escalation that reduced some schema lock contention, but the fundamental blocking behavior remains unchanged through SQL Server 2022. Sch-M locks are incompatible with every other lock mode, including all intent locks (IS, IU, IX, SIU, SIX), making them particularly prone to blocking scenarios.

Under snapshot isolation, readers still acquire Sch-S locks to ensure schema stability. Snapshot isolation skips shared data locks via row versioning, but schema stability locks are still required to prevent structural changes during query execution. SQL Server 2019 enhanced the metadata consistency checks during DDL operations, sometimes extending the duration that Sch-M locks are held.

-- Identify current schema lock blocking chains
SELECT 
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocking.wait_type AS blocking_wait_type,
    blocked.wait_type AS blocked_wait_type,
    blocking.wait_resource AS blocking_resource,
    blocked.wait_resource AS blocked_resource,
    blocking_text.text AS blocking_sql,
    blocked_text.text AS blocked_sql
FROM sys.dm_exec_requests blocking
INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.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_SCH_M';

-- Show current lock details for schema modification waits
SELECT 
    l.request_session_id,
    l.resource_type,
    l.resource_database_id,
    l.resource_associated_entity_id,
    OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) AS object_name,
    l.request_mode,
    l.request_type,
    l.request_status,
    r.wait_type,
    r.wait_time,
    r.command,
    t.text AS sql_text
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LCK_M_SCH_M'
   OR l.request_mode = 'Sch-M';

-- Historical schema lock wait analysis from Query Store
SELECT TOP 20
    qsq.query_id,
    qst.query_sql_text,
    qsws.wait_category_desc,
    qsws.total_query_wait_time_ms,
    qsws.avg_query_wait_time_ms,
    qsws.max_query_wait_time_ms,
    qsrs.count_executions
FROM sys.query_store_wait_stats qsws
INNER JOIN sys.query_store_runtime_stats qsrs ON qsws.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
    AND qsws.plan_id = qsrs.plan_id
INNER JOIN sys.query_store_plan qsp ON qsrs.plan_id = qsp.plan_id
INNER JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
INNER JOIN sys.query_store_query_text qst ON qsq.query_text_id = qst.query_text_id
WHERE qsws.wait_category_desc = 'Lock'
  AND qst.query_sql_text LIKE '%ALTER%'
ORDER BY qsws.total_query_wait_time_ms DESC;

-- Find long-running transactions that may be holding schema locks
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    t.transaction_begin_time,
    DATEDIFF(minute, t.transaction_begin_time, GETDATE()) AS transaction_duration_minutes,
    r.command,
    r.wait_type,
    txt.text AS current_sql
FROM sys.dm_tran_active_transactions t
INNER JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) txt
WHERE t.transaction_begin_time < DATEADD(minute, -5, GETDATE())
ORDER BY transaction_duration_minutes DESC;

-- Monitor schema lock waits in real-time
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id,
    s.login_name,
    s.program_name,
    s.host_name,
    r.command,
    r.percent_complete,
    t.text AS sql_text
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 LIKE 'LCK_M_SCH%'
ORDER BY r.wait_time DESC;

Kill blocking sessions holding schema locks This script identifies and terminates sessions that are blocking DDL operations. Use with extreme caution in production environments.

-- WARNING: Test in development first. This will terminate active sessions.
DECLARE @BlockingSessionId INT;
DECLARE @Sql NVARCHAR(100);

SELECT @BlockingSessionId = blocking.session_id
FROM sys.dm_exec_requests blocking
INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id
WHERE blocked.wait_type = 'LCK_M_SCH_M'
  AND blocking.session_id > 50  -- Avoid system processes
  AND blocking.open_transaction_count > 0;

IF @BlockingSessionId IS NOT NULL
BEGIN
    SET @Sql = 'KILL ' + CAST(@BlockingSessionId AS NVARCHAR(10));
    PRINT 'Executing: ' + @Sql;
    -- EXEC sp_executesql @Sql;  -- Uncomment to execute
END
ELSE
    PRINT 'No blocking sessions found for LCK_M_SCH_M waits';

Force transaction rollback for long-running DDL blocks Rollback uncommitted transactions that may be preventing schema modifications.

-- Rollback long-running transactions blocking schema changes
-- WARNING: This will lose uncommitted work. Verify with application teams first.
DECLARE @SessionId INT, @Sql NVARCHAR(100);

DECLARE blocking_cursor CURSOR FOR
SELECT DISTINCT s.session_id
FROM sys.dm_tran_active_transactions t
INNER JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
WHERE t.transaction_begin_time < DATEADD(minute, -30, GETDATE())
  AND s.session_id IN (
    SELECT blocking_session_id 
    FROM sys.dm_exec_requests 
    WHERE wait_type = 'LCK_M_SCH_M'
  );

OPEN blocking_cursor;
FETCH NEXT FROM blocking_cursor INTO @SessionId;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = 'KILL ' + CAST(@SessionId AS NVARCHAR(10));
    PRINT 'Rolling back session: ' + CAST(@SessionId AS NVARCHAR(10));
    -- EXEC sp_executesql @Sql;  -- Uncomment to execute
    FETCH NEXT FROM blocking_cursor INTO @SessionId;
END

CLOSE blocking_cursor;
DEALLOCATE blocking_cursor;

Set session lock timeout for DDL operations Configure reasonable timeouts to prevent indefinite waits during schema modifications.

-- Apply lock timeout to current session before running DDL
-- This prevents indefinite blocking on schema modification locks
SET LOCK_TIMEOUT 30000;  -- 30 seconds

-- Example DDL operation with timeout protection
BEGIN TRY
    ALTER TABLE YourTable ADD NewColumn INT NULL;
    PRINT 'Schema modification completed successfully';
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1222  -- Lock request timeout
    BEGIN
        PRINT 'Schema modification timed out due to blocking';
        -- Log the incident or retry logic here
    END
    ELSE
    BEGIN
        PRINT 'Schema modification failed: ' + ERROR_MESSAGE();
        THROW;
    END
END CATCH

-- Reset lock timeout to default
SET LOCK_TIMEOUT -1;

Schedule DDL operations during maintenance windows when user activity is minimal. Implement application-level connection pooling with shorter connection timeouts to reduce the likelihood of long-running transactions holding schema locks.

Configure Read Committed Snapshot Isolation (RCSI) at the database level to reduce reader-writer contention. RCSI eliminates most shared locks for readers, significantly reducing the chance that SELECT statements will block DDL operations.

Monitor for long-running transactions proactively using SQL Server Agent jobs. Set up alerts when transactions exceed reasonable duration thresholds, typically 15-30 minutes depending on your workload patterns. Implement query timeout settings in application connection strings to prevent runaway queries.

Use online index operations where possible (Enterprise Edition) to minimize schema lock duration. Online operations maintain table availability during most of the index creation process, acquiring exclusive locks only briefly at the beginning and end of the operation.

Design batch processing jobs to commit transactions frequently rather than processing large datasets in single transactions. Break large DDL operations into smaller chunks when feasible, and consider using database snapshots for reporting queries during major schema changes.

Establish monitoring for blocking chains using Extended Events or custom monitoring solutions that capture lock waits exceeding defined thresholds. Create runbooks for common schema lock scenarios to enable rapid response during production incidents.