Author: Peter Whyte

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting SOS_MEMORY_TOPLEVELBLOCKALLOCATOR is recorded when a thread waits for the critical section inside the memory allocator that steals memory from the buffer pool, the path used when components need large memory blocks that come out…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting SOS_PHYS_PAGE_CACHE is a wait on the mutex a thread must hold before allocating physical memory pages from Windows or returning them. It shows up on instances using locked pages in memory, that is, when…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting SOS_SYNC_TASK_ENQUEUE_EVENT is recorded when a task is started synchronously: the starting thread enqueues the task on an SOS node and waits for it to actually begin running (or for the enqueue to time out).…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting SOS_WORK_DISPATCHER is recorded by a SQLOS worker thread that has become idle and is waiting to be handed something to do. The wait starts when the thread runs out of work and ends when…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting SOS_WORKER_MIGRATION appears to track a worker (thread) being moved to a different scheduler. It arrived with SQL Server 2019’s scheduling improvements, which allow a worker at the head of one scheduler’s runnable queue to…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting SP_SERVER_DIAGNOSTICS_SLEEP is recorded by the background system health monitor thread while it sleeps between runs of sp_server_diagnostics, the built-in health check procedure added in SQL Server 2012 to power automatic failure detection for Availability…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting SQLCLR_APPDOMAIN is recorded while CLR execution waits for an application domain to complete startup. SQL Server hosts .NET code in appdomains (one per database/owner combination, broadly), and the first CLR call after an appdomain…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting SQLCLR_ASSEMBLY is recorded while a thread waits for access to the list of loaded assemblies in an appdomain. When CLR code runs, the host tracks which assemblies are loaded per domain, and threads consulting…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting Four waits cover SQL Trace’s file-writing machinery, and they share one story, so this page covers the set: Since the default trace runs on practically every instance, small amounts of the family are universal.…