Description
This wait occurs when a request is waiting to acquire a Schema Modify lock with the low priority options added in SQL Server 2014. 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. The low priority operations must be set to abort blockers for this wait type to accumulate
Resolved by
DBAs and Developers
Suggested solutions
- Perform schema operations outside regular business hours to avoid blocking
- If possible, run low priority tasks outside regular business hours. Using abort blockers will cause interruption to users and could cause application issues
- 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
Exploring Low Priority Lock Wait Options
Locking in Microsoft SQL Server (Part 13 – Schema locks)
Identify the Cause of SQL Server Blocking