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 RESOURCE_GOVERNOR_IDLE is recorded when a query is forced to sit idle because of a Resource Governor CPU cap (CAP_CPU_PERCENT). A cap is a hard ceiling: where the softer MAX_CPU_PERCENT only bites under contention, CAP_CPU_PERCENT…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting RESOURCE_SEMAPHORE_MUTEX is a wait for the mutex protecting the critical section where queries acquire the resources they need to compile or execute, memory grants and thread reservations. Only one thread at a time can…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting RESOURCE_SEMAPHORE_SMALL_QUERY is a wait on the small-query memory grant semaphore. SQL Server reserves a separate slice of grant memory for cheap queries so they never queue behind memory-hungry monsters. This wait means even that…

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