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

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting SQLTRACE_INCREMENTAL_FLUSH_SLEEP is recorded by a background thread sleeping between checks for SQL Trace data that needs flushing to a trace file. Because the default trace runs on practically every instance (it is the lightweight…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting TERMINATE_LISTENER is recorded when a thread waits for a SQL Server Network Interface (SNI) listener to be destroyed. SNI is the protocol layer wrapping TDS packets over whatever transports are configured (TCP, named pipes,…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting TRACE_EVTNOTIF is recorded when event notifications are configured, once per firing of an event that has a notification attached. Event notifications are the Service Broker-based mechanism that turns trace events and DDL events into…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting TRACEWRITE is recorded by the SQL Trace rowset provider while it waits for a free trace buffer, or for a buffer containing events to hand to the consumer. The rowset provider is the path…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting UCS_SESSION_REGISTRATION is recorded when a thread waits for access to the list of Service Broker sessions so it can add or remove one. The Unified Communications Stack (UCS) underlies Broker’s connection handling, and session…