Description
This wait occurs when a request is waiting to acquire a Schema Modify lock. This mechanism protects a schema from modification by other requests. This lock typically occurs during long schema changes for example: adding additional non-NULL columns, change column data types, rebuilding indexes offline, etc. Some values are expected but if values are excessive please see suggested solutions below
Resolved by
DBAs and Developers
Suggested solutions
- Perform schema operations outside regular business hours to avoid blocking
- Run trace/Profiler/Extended Event session in conjunction with Blocked Process Threshold to determine which queries are being blocked
- If available rebuild indexes online
Additional research
Locking in Microsoft SQL Server (Part 13 – Schema locks)
Identify the Cause of SQL Server Blocking