(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)

LCK_M_SCH_M

(Back to main page…)

Description:

This wait type is when a thread is waiting to acquire a Schema Modification (also called Schema Modify) lock on a resource and there is at least one other lock in an incompatible mode granted on the resource to a different thread.

General locking information:

(Books Online description: “Occurs when a task is waiting to acquire a Schema Modify lock.”)

Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.

Added in SQL Server version:

Pre-2005/2005

Removed in SQL Server version:

N/A

Extended Events wait_type value:

The map_key value in sys.dm_xe_map_values is 2 in all versions through 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.

Other information:

General guidance around troubleshooting lock waits:

  • It is not possible to determine the lock resource from the sys.dm_os_wait_stats output. You can see the resource from sys.dm_os_waiting_tasks (using my script) or looking at the resource_description field of sys.dm_tran_locks where the request_status is ‘WAIT’.
  • You can use the blocked process report to get more detailed information on queries that are waiting for locks for a specified threshold (see here).
  • Look to see what is at the head of the blocking chain (i.e. the thread that’s holding the lock that’s blocking everyone) using a script (plenty of them available online – I don’t have a preferred one). What is that thread waiting for? Fixing that wait may help unravel the blocking. For example, a thread may be holding locks and committing a transaction, but there’s a synchronous mirror with a slow I/O subsystem so the mirror log write takes a long time, making the transaction commit take longer, and the locks take longer to be released, causing blocking.
  • Look for lock escalation, where an UPDATE transaction has escalated to a table X lock, causing widespread blocking.
  • Look for index operations causing table locks, and consider using online index operations (or if already using them, consider the WAIT_AT_LOW_PRIORITY feature in 2014+).
  • Look for code that specifies a TABLOCK (causes a table Shared lock) or TABLOCKX (causes a table Exclusive lock) hint.
  • Look for application code that will cause locks to be acquired and then waits for user input, or fails to commit a transaction for a long time.
  • Consider creating nonclustered indexes to remove row locks from the underlying heap/clustered index.
  • Consider using snapshot isolation or read committed snapshot isolation to allow readers to not take S/IS locks and reduce blocking.
  • Check the correct isolation level is being used as REPEATABLE_READ and SERIALIZABLE will hold S/IS locks until the end of a transaction.
  • Check for accidental use of the SERIALIZABLE isolation level, from using distributed transactions or incorrectly scoped .Net TransactionScope objects.

Specific guidance for LCK_M_SCH_M waits:

  • For a Schema Modification lock, the resource is a table.
  • Any other table lock will block a Schema Modification lock, and Schema Modification lock are from an index build/rebuild or table/schema modification.

There have been many people with high LCK_M_SCH_M waits because of problems during connection logins, especially after enabling Common Criteria compliance for a system with many login events for a single SQL Server login. See this blog post for details.

Known occurrences in SQL Server (list number matches call stack list):

  1. Waiting for a Schema Modification lock on a table (in this case, while adding a foreign-key constraint to a table)
  2. Waiting for a Schema Modification lock on a table (in this case, while performing an offline index rebuild operation)

And other similar call stacks.

Abbreviated call stacks (list number matches known occurrences list):

  1. SOS_Task::PostWait+90
    EventInternal<SuspendQueueSLock>::Wait+1f9
    LockOwner::Sleep+495
    lck_lockInternal+ead
    lck_lockPartitionedAll+1f3
    MDL::LockObjectLocal+42f
    CMEDCacheEntryFactory::AcquireLock+48
    CMEDCatalogObject::GetCachedObjectById+157
    CMEDCatalogObject::GetCachedObjectByName+6b
    CMEDProxySchema::GetObjectByName+6e
    IMetadataAccess::GetMultiNameObjectHelper+6dd
    CMEDAccess::GetMultiNameObject+c9
    E_CNSTFKEY::Execute+2a0
    E_CNSTCRT::Execute+1c3
    CStmtAlterTable::XretExecute+1df7
    CMsqlExecContext::ExecuteStmts<1,1>+400
    CMsqlExecContext::FExecute+a33
    CSQLSource::Execute+866
    process_request+73c
    process_commands+51c
    SOS_Task::Param::Execute+21e
    SOS_Scheduler::RunTask+a8
  2. SOS_Task::PostWait+9e
    EventInternal<SuspendQueueSLock>::Wait+1fb
    LockOwner::Sleep+485
    lck_lockInternal+10ed
    MDL::LockObjectLocal+dbe
    CMEDCacheEntryFactory::AcquireLock+61
    CMEDCatalogObject::GetCachedObjectById+e2
    CMEDCatalogObject::GetCachedObjectByName+81
    CMEDProxySchema::GetObjectByName+82
    IMetadataAccess::GetMultiNameObjectHelper+714
    CMEDAccess::GetMultiNameObject+df
    CStmtAlterIndex::XretExecute+559
    CMsqlExecContext::ExecuteStmts<1,1>+427
    CMsqlExecContext::FExecute+a43
    CSQLSource::Execute+86c
    process_request+a57
    process_commands+4a3
    SOS_Task::Param::Execute+21e
    SOS_Scheduler::RunTask+ab