SQL DBA Blog: Home

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 locks only exist under SERIALIZABLE isolation, where SQL Server must prevent phantom rows from appearing in a range a transaction has read. Seeing this…

read more

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 key: SQL Server locks the range between the neighbouring keys to keep the insert phantom-safe, while the NULL component on the key is an…

read more

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 on online index operations and partition switches (SQL Server 2014 onward), which lets DDL wait politely instead of queuing the whole world behind its…

read more

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 exclusive locks exist lower down. This wait means a writer cannot even place that intent marker, because another session holds an incompatible lock at…

read more

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 page above it, marking its intention so other sessions know readers are active lower down. This wait means even that lightweight intent marker is…

read more

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 little work exists, and goes back to sleep. Each sleeping second lands in this wait, per lazy writer thread (large NUMA…

read more

KSOURCE_WAKEUP is recorded by SQL Server’s background control task while it waits to be told, by the Windows Service Control Manager, to shut the instance down. One thread (typically SPID 7 or thereabouts) holds this wait for the entire life of the instance. Its accounting quirk: sys.dm_os_wait_stats shows it with near-zero time because the wait…

read more

IO_QUEUE_LIMIT is a wait recorded when a session cannot issue another asynchronous I/O because its queue of outstanding async I/Os is already at its limit. The engine caps how much async I/O a single task can have in flight; when the cap is hit, the task waits here until some of the in-flight I/O completes.…

read more

IMPPROV_IOWAIT is recorded when a thread has issued an asynchronous read against a bulk load source file and is waiting for the read to complete. It belongs to BULK INSERT, OPENROWSET(BULK…), BCP, and similar import paths: the engine reads the source data file, and time spent waiting on those reads lands here. It is effectively…

read more