SQL DBA Blog: Home

REPLICA_WRITES is recorded while a task waits for page writes to a database snapshot or a DBCC replica to complete. Both targets are the same machinery: user-created database snapshots receive copy-on-write pages, and DBCC CHECKDB runs against a hidden internal snapshot that works identically. If this wait is on your board, snapshot files are absorbing…

read more

REQUEST_DISPENSER_PAUSE is recorded while a task waits for all outstanding I/O against a database to complete, so that I/O can be frozen for an external snapshot backup. It is the setup phase of the freeze covered by DISKIO_SUSPEND: first drain everything in flight (this wait, one second at a time until the drain completes), then…

read more

RESERVED_MEMORY_ALLOCATION_EXT is recorded when a thread switches to preemptive mode while allocating memory that was already reserved for its query, in other words, drawing down its execution memory grant. The preemptive switch spares the allocation code from checking scheduling quantum exhaustion, the same mechanism as MEMORY_ALLOCATION_EXT, just against granted rather than general memory. Every sort,…

read more

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 inserts idle time into the runnable queue so the pool cannot exceed its percentage even on an otherwise idle server. This…

read more

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 be inside that code. If the resource acquisition then fails, the thread releases the mutex and records the real wait, RESOURCE_SEMAPHORE,…

read more

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 fast lane is congested: a small query could not get its modest grant immediately. The engine caps the damage: if the…

read more

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 before the group advances to the next block. The wait is that rendezvous. Striped restores live at this barrier constantly, in small doses.…

read more

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, new entries) pass through it. No native procedures, no meaningful RTDATA_LIST. Is It a Problem? Usually not, with a version-specific exception:…

read more

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 child tasks feeding one session’s tally, hence the synchronisation and the name. Wait statistics about maintaining wait statistics: agreeably meta. Is It a…

read more