Author: Peter Whyte

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting LCK_M_SCH_S is a wait to acquire a Schema Stability (SCH-S) lock. Every query takes SCH-S locks on the objects it touches, during compilation and execution, to guarantee the table’s structure does…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting LCK_M_SCH_M is a wait to acquire a Schema Modification (SCH-M) lock. DDL needs this lock to change an object’s structure: ALTER TABLE, offline index rebuilds, partition switches, truncates, and drops all…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting LCK_M_RX_X is a wait to acquire an Exclusive Range lock: an exclusive lock on a key value plus an exclusive lock on the range between it and the previous key. Like…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting LCK_M_RS_U is a wait to acquire an Update lock on a key together with an Update Range lock on the gap between it and the previous key. It belongs to the…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting LCK_M_RS_S is a wait to acquire a Range Shared lock: a shared lock on a key value plus a shared lock on the range between it and the previous key. Range…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting LCK_M_RIn_NL is a wait to acquire an Insert Range lock with a NULL lock on the key itself. It appears when a session under SERIALIZABLE isolation wants to insert a new…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting Every lock mode in SQL Server has two extra wait type variants: LCK_M_<mode>_LOW_PRIORITY and LCK_M_<mode>_ABORT_BLOCKERS, 46 wait types in all (LCK_M_S_LOW_PRIORITY, LCK_M_SCH_M_ABORT_BLOCKERS, and so on). They exist for the WAIT_AT_LOW_PRIORITY option…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting LCK_M_IX is a wait to acquire an Intent Exclusive (IX) lock. When SQL Server modifies a row, it first stamps IX locks on the page and table above it, signalling that…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting LCK_M_IS is a wait to acquire an Intent Shared (IS) lock. Before SQL Server takes a shared lock on a row or page, it places IS locks on the table and…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGEIOLATCH_SH Wait TypeRelated pillar: Performance & Troubleshooting LAZYWRITER_SLEEP is recorded by the lazy writer thread while it sleeps between checks of the buffer pool free list. Roughly once a second it wakes, checks whether memory pressure requires freeing buffers, does whatever…