LCK_M_SCH_S
Published 28 March 2024
A transaction is waiting to acquire a schema stability lock. This lock is used when queries are being compiled and executed. It doesn’t block transactional locks such as exclusive locks (LCK_M_X), so other transactions can run concurrently while this lock is in place. It does block any data definition language (DDL) or data manipulation language (DML) operations if they’ve acquired a schema modification lock.
Lock waits commonly occur on busy servers where concurrent transactions demand the same resource, resulting in poor performance. A high number of locking waits may indicate blocking problems and should be investigated.
Investigating
Break long transactions down into shorter ones. See: Managing Long-Running Transactions (TechNet).
Check the isolation levels for your transactions and update the locking and row versioning behavior if necessary. See: Set Transaction Isolation Level (TechNet).
Check whether lock escalation is causing blocking problems and resolve if necessary. The Average wait time tells you whether you’re suffering from many short blocks or several long blocks. To troubleshoot blocking, see: How to resolve blocking problems that are caused by lock escalation in SQL Server (Microsoft Support).
Check the affected queries and the Top queries table. Tune queries so they run faster and require fewer locks. See: Query tuning (TechNet).
On the Analysis page, check these metrics for additional details on locking behavior:
Lock timeouts/sec
Lock timeouts/sec
Avg. lock wait time
On the Analysis page, check these metrics to see whether memory problems or I/O bottlenecks are causing locks to be held for longer than usual:
Machine: memory used
Memory pages/sec
Disk avg. read time
Disk avg. write time
Buffer cache hit ratio
Buffer page life expectancy
See also: Investigating I/O bottlenecks.
Check the sys.dm_trans_locks DMV for resources associated with lock requests. See: sys.dm_tran_locks (TechNet).
Consider using partitions to split single lock resource into multiple resources (only available if you’re using more than 16 CPUs). See: http://technet.microsoft.com/en-us/library/ms187504(v=sql.105).aspx.