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

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting PREEMPTIVE_OS_WRITEFILEGATHER is recorded when a thread calls the Windows WriteFileGather function and waits for it to return. In DBA practice this wait has one dominant source: zero-filling file space. When a data or log…