LCK_M_SCH_M_ABORT_BLOCKERS

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

  1. Perform schema operations outside regular business hours to avoid blocking
  2. If possible, run low priority tasks outside regular business hours. Using abort blockers will cause interruption to users and could cause application issues
  3. Run trace/Profiler/Extended Event session in conjunction with Blocked Process Threshold to determine which queries are being blocked
  4. 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

See more

Consider using ApexSQL Monitor, a Windows and SQL Server monitoring tool able to track SQL Server wait types, including the individual query wait statistics

⇐ Back to index