Author: Peter Whyte

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX Wait TypeRelated pillar: Performance & Troubleshooting LATCH_DT is a wait for a latch in Destroy (DT) mode on an internal SQL Server structure that is not a data or index page. Destroy mode is the strongest latch mode, taken when…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX, TempDB Allocation ContentionRelated pillar: Performance & Troubleshooting LATCH_EX is a wait for an exclusive latch on an internal SQL Server structure that is not a data or index page. SQL Server uses latches to protect all kinds of in-memory structures.…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX Wait TypeRelated pillar: Performance & Troubleshooting LATCH_KP is a wait for a Keep (KP) mode latch on an internal SQL Server structure that is not a data or index page. Keep mode is the lightest latch: it pins a structure…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX Wait TypeRelated pillar: Performance & Troubleshooting LATCH_NL is the wait type for a Null (NL) mode latch on a non-page internal structure. NL is the no-op of latch modes, and this wait exists purely so the full set of modes…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX, TempDB Allocation ContentionRelated pillar: Performance & Troubleshooting LATCH_SH is the shared (read) counterpart to LATCH_EX. It is a wait for a shared latch on an internal SQL Server structure that is not a data or index page. Threads take a…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX Wait TypeRelated pillar: Performance & Troubleshooting LATCH_UP is a wait for an update-mode (UP) latch on an internal SQL Server structure that is not a data or index page. Update mode sits between shared and exclusive: the holder intends to…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOGRelated pillar: Storage & Capacity LOGBUFFER occurs when a session generating log records has to wait for free space in a log buffer. SQL Server stages log records in small in-memory log buffers (up to 60KB each) before flushing them to…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Backup & Recovery LOGMGR_FLUSH is a wait recorded when a thread generating a transaction log record has to pause until the current log flush completes. From SQL Server 2016 onward the engine runs multiple background log writer…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGEIOLATCH_SH Wait TypeRelated pillar: Storage & Capacity PAGEIOLATCH_UP is a wait for an update-mode latch on a page that is currently in an I/O request, usually a thread reading a non-table page (typically an allocation bitmap like PFS, GAM, or SGAM)…