LCK_M_SCH_S

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.  

 

The SQL Server Database Engine uses locking to manage the way multiple users access the same data at the same time. Every transaction must first request a lock on the resource it wants to modify. This lock stops other transactions from modifying the same resource in a way that might cause conflict. The lock is only released once the transaction no longer needs it. For more information, including details of lock type compatibility, see: SQL Server Transaction Locking and Row Versioning Guide (TechNet).

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

  • 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.


 


Didn't find what you were looking for?