Schema (Sch-S, Sch-M) Lock in SQL Server

Schema Locks are metadata-level locks used by SQL Server to protect object definitions and maintain metadata consistency. The two primary schema locks are Schema Stability (Sch-S) and Schema Modification (Sch-M). Sch-S is acquired whenever a query is compiled or executed and ensures that the table structure does not change while the query is using it. Multiple sessions can hold Sch-S locks simultaneously. Sch-M, on the other hand, is acquired during DDL operations such as ALTER TABLE, DROP TABLE, CREATE INDEX, and TRUNCATE TABLE. It is highly restrictive and blocks all other access to the object because the schema itself is being modified. A common misconception is that NOLOCK prevents all blocking, but even NOLOCK queries still require Sch-S locks and therefore can be blocked by an existing Sch-M lock. In production environments, schema locks are often responsible for deployment-related blocking incidents, index maintenance delays, and application outages during schema changes. Understanding Sch-S and Sch-M behavior is critical for diagnosing blocking chains, planning zero-downtime deployments, and managing high-concurrency SQL Server systems.

SQL Server uses Schema Locks to protect the structure (metadata) of database objects. There are two main schema locks:

Ø  Sch-S (Schema Stability Lock) → Acquired when SQL Server compiles or executes queries. It prevents schema changes while a query is using the object. This acquired whenever SQL Server

o   Compiles Query

o   Executes Query

o   Reads Metadata

o   Builds Execution Plan

Ø  Sch-M (Schema Modification Lock) → Acquired during DDL operations such as ALTER TABLE, DROP TABLE, CREATE INDEX, TRUNCATE TABLE, etc. It blocks all access to the object until the schema change completes. While Sch-M exists then No Reads, No Writes and No Metadata Access allowed. Due to that NOLOCK also do not work. This acquired during:

o   ALTER TABLE

o   DROP TABLE

o   TRUNCATE TABLE

o   CREATE INDEX

o   DROP INDEX

o   ALTER INDEX REBUILD

Schema means:

Ø  Table Structure

Ø  Column Definitions

Ø  Indexes

Ø  Constraints

Ø  Metadata

Ø  Statistics Metadata

CREATE TABLE Employee

(

    EmployeeID INT,

    EmployeeName VARCHAR(100),

    Salary MONEY

);

The table definition itself is the schema.

Why Do We Need Schema Locks

Imagine that in

Session 1: we are running the SELECT * FROM Employee; At the same time:

Session 2: some run the below alter script

ALTER TABLE Employee

DROP COLUMN Salary;

Without Schema Locks our Query starts reading Salary and Column gets dropped Query crashes

Metadata corruption risk. SQL Server prevents this using Schema Locks.

Common Sch-M Operations

Operation	Uses Sch-M
ALTER TABLE	Yes
DROP TABLE	Yes
CREATE INDEX	Yes
DROP INDEX	Yes
TRUNCATE TABLE	Yes
ALTER COLUMN	Yes
Partition Switch	Yes

 Common Sch-S Operations

Operation	Uses Sch-S
SELECT	Yes
INSERT	Yes
UPDATE	Yes
DELETE	Yes
Query Compilation	Yes

 Sch-S vs Sch-M

Feature	Sch-S	Sch-M
Purpose	Read Metadata	Modify Metadata
Multiple Sessions	Yes	No
Blocks Queries	No	Yes
Used By	SELECT/UPDATE/DELETE	ALTER/DROP/TRUNCATE
Compatible With Sch-S	Yes	No
Compatible With Sch-M	No	No