Category: Wait Types

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting BROKER_TRANSMISSION_TABLE is recorded when a thread waits on the process of writing inactive transmission objects, the in-memory structures tracking message transmissions per Service Broker dialog, out to a tempdb worktable to save memory. It…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: BACKUPIO and BACKUPBUFFER Wait TypesRelated pillar: Storage & Capacity DISKIO_SUSPEND occurs when a thread needs to issue I/O against a database whose I/O has been deliberately frozen. External snapshot backup tools (VSS-based agents, SAN and VM snapshot backups) freeze a database’s…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGELATCH_EX Wait TypeRelated pillar: Performance & Troubleshooting DROPTEMP is recorded between attempts to drop a temporary object after a previous attempt failed, classically because a deadlock occurred while dropping a temp table. The engine backs off and retries, and the wait…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Storage & Capacity FGCB_ADD_REMOVE is a wait on the File Group Control Block, the structure that tracks the files in a filegroup. A thread needs it in exclusive mode whenever the file list changes or a file…

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