Schema Locks and Online Index Maintenance

October 17, 2026

Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

Every query takes a schema stability (Sch-S) lock on the tables it uses, and anything that changes a table’s definition needs a schema modification (Sch-M) lock. Sch-M is the most restrictive lock in SQL Server: it is incompatible with every other lock, including Sch-S. This is why an index rebuild or an ALTER TABLE can suddenly stop a busy system.

The two schema locks

Sch-S (schema stability): taken by every query while it compiles and runs, so the table cannot change under it. Compatible with everything except Sch-M, including X locks. NOLOCK queries still take Sch-S.
Sch-M (schema modification): taken by ALTER TABLE, CREATE/DROP INDEX, offline index rebuilds, TRUNCATE TABLE, partition SWITCH, and briefly at the start and end of an online index rebuild. Incompatible with everything.

Why one rebuild can block everything

Locks are granted roughly first in, first out. When a request is waiting for Sch-M, new requests queue behind it even if they would be compatible with the locks already held.

\1. A long report holds Sch-S on dbo.Orders.

\2. An index rebuild asks for Sch-M and waits for the report.

\3. Every new query on dbo.Orders asks for Sch-S and waits behind the rebuild.

The rebuild has done no work, yet the table is effectively offline until the report finishes. An online rebuild has the same problem at its start and end, when it needs Sch-M for a moment.

Seeing it happen

-- Who holds and who waits for schema locks
SELECT tl.request_session_id AS session_id,
       OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id) AS object_name,
       tl.request_mode, tl.request_status,
       er.blocking_session_id, er.wait_type, er.wait_time / 1000 AS wait_seconds,
       LEFT(t.text, 200) AS sql_text
FROM sys.dm_tran_locks tl
LEFT JOIN sys.dm_exec_requests er ON er.session_id = tl.request_session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) t
WHERE tl.resource_type = N'OBJECT'
  AND (tl.request_mode IN (N'Sch-S', N'Sch-M') OR tl.request_status = N'WAIT')
  AND tl.resource_database_id = DB_ID()
ORDER BY tl.request_status, tl.request_session_id;

The change waits on LCK_M_SCH_M. The queries queued behind it usually wait on LCK_M_IS (or LCK_M_SCH_S while compiling), with the change’s session as their blocker.

WAIT_AT_LOW_PRIORITY

From SQL Server 2014, online index rebuilds and partition switches can wait in a separate low-priority queue, so they do not block other queries while they wait. You choose how long to wait and what to do if the time runs out:

ALTER INDEX PK_Orders ON dbo.Orders
REBUILD WITH (
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)
    )
);

ABORT_AFTER_WAIT = SELF: give up the rebuild and try again another time. The safe choice for scheduled maintenance.
ABORT_AFTER_WAIT = BLOCKERS: kill the sessions blocking the rebuild. Only with the application owner’s agreement.
ABORT_AFTER_WAIT = NONE: keep waiting, but at normal priority, so new queries queue behind it again.

The same option works for ALTER TABLE … SWITCH PARTITION. ONLINE = ON needs Enterprise edition (or Developer/Evaluation); on Standard edition the rebuild is always offline and holds Sch-M for its whole length.

Resumable index operations

SQL Server 2017 added resumable online rebuilds (and 2019 resumable online index creation). A rebuild can be paused at the end of a maintenance window and resumed the next night, and it survives a failover.

ALTER INDEX PK_Orders ON dbo.Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

While it runs, check progress from another session, and pause or resume it there:

SELECT name, percent_complete, state_desc, last_pause_time
FROM sys.index_resumable_operations;

-- Run one of these on its own while the rebuild is running or paused
-- ALTER INDEX PK_Orders ON dbo.Orders PAUSE;
-- ALTER INDEX PK_Orders ON dbo.Orders RESUME;
-- ALTER INDEX PK_Orders ON dbo.Orders ABORT;   -- give up

Good practice

Schedule index and schema changes when long reports and ETL are not running, and check for long-running queries first.
Use WAIT_AT_LOW_PRIORITY with ABORT_AFTER_WAIT = SELF for scheduled online rebuilds.
Rebuild less: reorganise, update statistics, or skip indexes under about 1,000 pages (Ola Hallengren’s IndexOptimize does this for you).
Deploy schema changes with a short lock timeout (SET LOCK_TIMEOUT 5000) and retry, rather than letting them queue behind a long query.

From my SQL Server DBA handbook.

Troubleshooting

Clive Roberts