Category: Wait Types

⏳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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting 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…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting HTREPARTITION completes the batch-mode hash table wait family. When a parallel batch-mode hash join or aggregation discovers its shared hash table will not fit as built, it repartitions the table’s input to…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting HTREINIT is part of the batch-mode hash table wait family. Parallel batch-mode hash joins and aggregations share one hash table across threads (since SQL Server 2014), and some join strategies process their…