Category: Wait Types

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

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX Wait TypeRelated pillar: Performance & Troubleshooting PAGELATCH_DT is a wait for a Destroy (DT) mode latch on a data file page that is in memory and not part of an I/O request. Destroy mode is taken when a buffer is…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX Wait TypeRelated pillar: Performance & Troubleshooting PAGELATCH_KP is a wait for a Keep (KP) mode latch on a data file page in memory (not in an I/O request). Keep mode pins a page so it cannot be destroyed while the…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX Wait TypeRelated pillar: Performance & Troubleshooting PAGELATCH_NL is the wait type for a Null (NL) mode latch on an in-memory data file page. Null mode is a non-acquiring placeholder, and the wait type exists so the page latch family covers…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX, TempDB Allocation ContentionRelated pillar: Performance & Troubleshooting PAGELATCH_SH is a wait for a shared (read) latch on a page that is already in memory. Latches are lightweight locks SQL Server uses to protect in-memory structures while they are being read…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX Wait TypeRelated pillar: Performance & Troubleshooting PAGELATCH_UP is a wait for an update-mode (UP) latch on a data file page that is already in memory. Update mode is used when a thread needs to modify a page structure that others…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability PARALLEL_REDO_DRAIN_WORKER is recorded when the main parallel redo thread for a database (on an AG replica, or during crash recovery on a standalone instance) waits for the other redo threads to finish all outstanding log…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting PREEMPTIVE_OS_LOGONUSER is recorded when a thread calls the Windows LogonUser function, which authenticates a set of credentials and produces a logon token. SQL Server needs it wherever it must become another Windows identity: Agent…