Author: Peter Whyte

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: BACKUPIO and BACKUPBUFFER Wait TypesRelated pillar: Backup & Recovery RESTORE_MSDA_THREAD_BARRIER is recorded during restores from multiple backup devices (striped backups). Threads restoring parts of a single data block from the multiple streams synchronise at a barrier: all must finish their piece…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting RTDATA_LIST is recorded when a thread waits for access to the data structure holding performance metrics (RunTimeData) for natively-compiled stored procedures, In-Memory OLTP’s compiled-to-machine-code procedures. Both reporting execution statistics and maintaining the structure (cleanup,…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting SESSION_WAIT_STATS_CHILDREN is recorded when a thread waits to synchronise updates to the per-session wait statistics behind sys.dm_exec_session_wait_stats, the DMV that gives you sys.dm_os_wait_stats-style numbers scoped to one session. Parallel queries mean multiple…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting SHUTDOWN is recorded when a SHUTDOWN statement is waiting for active connections to exit, specifically when the shutdown request is blocked behind queries being run by connections with sysadmin-level (sa) access. A graceful shutdown…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting SLEEP_BPOOL_FLUSH is recorded when the checkpoint process deliberately pauses issuing new writes because it detects it is saturating the I/O subsystem (around 20ms average write latency during normal operation, 100ms during shutdown). Checkpoint backs…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: PAGEIOLATCH_SH Wait TypeRelated pillar: Performance & Troubleshooting Three sleep waits share the theme of “no free memory right now, nap and retry”, and this page covers them together: Individually benign; collectively a memory-pressure gauge. Are They a Problem? Occasional traces, no.…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting Three sleep waits measure phases of instance startup, and this page covers them together: They fire once per startup and then go quiet until the next one. Are They a Problem? Not as waits;…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting SNI_CRITICAL_SECTION is recorded when a thread waits for a critical section inside the SQL Server Network Interface (SNI), the layer that encapsulates TDS packets and talks to clients over the configured transports. Processing communication…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting SNI_TASK_COMPLETION is recorded when a thread waits for tasks to finish during a NUMA node state change, specifically around creating a new SNI network listener after a NUMA node comes online. SQL Server distributes…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting SOS_DISPATCHER_MUTEX is recorded when a thread waits for the critical section inside the generic dispatcher-pool management code, including when a pool is being adjusted (grown or shrunk). Dispatcher pools are the special-purpose thread pools…