Information About Waits Stats in SQL Server LOCK related Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to LOCK related waits.

LCK_M_BU
Occurs when a task is waiting to acquire a Bulk Update (BU) lock.

LCK_M_BU_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Bulk Update (BU) lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_BU_LOW_PRIORITY
Occurs when a task is waiting to acquire a Bulk Update (BU) lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_IS
Occurs when a task is waiting to acquire an Intent Shared (IS) lock.

LCK_M_IS_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Intent Shared (IS) lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_IS_LOW_PRIORITY
Occurs when a task is waiting to acquire an Intent Shared (IS) lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_IU
Occurs when a task is waiting to acquire an Intent Update (IU) lock.

LCK_M_IU_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Intent Update (IU) lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_IU_LOW_PRIORITY
Occurs when a task is waiting to acquire an Intent Update (IU) lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_IX
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock.

LCK_M_IX_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_IX_LOW_PRIORITY
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_NL
Occurs when a task is waiting to acquire a NULL lock on the current key value, and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock.

LCK_M_RIn_NL_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a NULL lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. A NULL lock on the key is an instant release lock. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_NL_LOW_PRIORITY
Occurs when a task is waiting to acquire a NULL lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. A NULL lock on the key is an instant release lock. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_S
Occurs when a task is waiting to acquire a shared lock on the current key value, and an Insert Range lock between the current and previous key.

LCK_M_RIn_S_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a shared lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_S_LOW_PRIORITY
Occurs when a task is waiting to acquire a shared lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_U
Task is waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key.

LCK_M_RIn_U_ABORT_BLOCKERS
Task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_U_LOW_PRIORITY
Task is waiting to acquire an Update lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_X
Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key.

LCK_M_RIn_X_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RIn_X_LOW_PRIORITY
Occurs when a task is waiting to acquire an Exclusive lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RS_S
Occurs when a task is waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous key.

LCK_M_RS_S_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Shared lock with Abort Blockers on the current key value, and a Shared Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RS_S_LOW_PRIORITY
Occurs when a task is waiting to acquire a Shared lock with Low Priority on the current key value, and a Shared Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RS_U
Occurs when a task is waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key.

LCK_M_RS_U_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Update Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RS_U_LOW_PRIORITY
Occurs when a task is waiting to acquire an Update lock with Low Priority on the current key value, and an Update Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RX_S
Occurs when a task is waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key.

LCK_M_RX_S_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Shared lock with Abort Blockers on the current key value, and an Exclusive Range with Abort Blockers lock between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RX_S_LOW_PRIORITY
Occurs when a task is waiting to acquire a Shared lock with Low Priority on the current key value, and an Exclusive Range with Low Priority lock between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RX_U
Occurs when a task is waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key.

LCK_M_RX_U_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Exclusive range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RX_U_LOW_PRIORITY
Occurs when a task is waiting to acquire an Update lock with Low Priority on the current key value, and an Exclusive range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RX_X
Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key.

LCK_M_RX_X_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers on the current key value, and an Exclusive Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_RX_X_LOW_PRIORITY
Occurs when a task is waiting to acquire an Exclusive lock with Low Priority on the current key value, and an Exclusive Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_S
Occurs when a task is waiting to acquire a Shared lock.

LCK_M_S_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Shared lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_S_LOW_PRIORITY
Occurs when a task is waiting to acquire a Shared lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SCH_M
Occurs when a task is waiting to acquire a Schema Modify lock.

LCK_M_SCH_M_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Schema Modify lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SCH_M_LOW_PRIORITY
Occurs when a task is waiting to acquire a Schema Modify lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SCH_S
Occurs when a task is waiting to acquire a Schema Share lock.

LCK_M_SCH_S_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Schema Share lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SCH_S_LOW_PRIORITY
Occurs when a task is waiting to acquire a Schema Share lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SIU
Occurs when a task is waiting to acquire a Shared With Intent Update lock.

LCK_M_SIU_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Shared With Intent Update lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SIU_LOW_PRIORITY
Occurs when a task is waiting to acquire a Shared With Intent Update lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SIX
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock.

LCK_M_SIX_ABORT_BLOCKERS
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_SIX_LOW_PRIORITY
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_U
Occurs when a task is waiting to acquire an Update lock.

LCK_M_U_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Update lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_U_LOW_PRIORITY
Occurs when a task is waiting to acquire an Update lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_UIX
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock.

LCK_M_UIX_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_UIX_LOW_PRIORITY
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_X
Occurs when a task is waiting to acquire an Exclusive lock.

LCK_M_X_ABORT_BLOCKERS
Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

LCK_M_X_LOW_PRIORITY
Occurs when a task is waiting to acquire an Exclusive lock with Low Priority. (Related to the low priority wait option of ALTERR TABLE and ALTER INDEX.)
Applies to: SQL Server 2014 through SQL Server 2014.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

Your email address will not be published. Required fields are marked *