Author: Peter Whyte

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

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: Performance & Troubleshooting VDI_CLIENT_OTHER is recorded by threads involved in copying database data through the Virtual Device Interface, most commonly automatic seeding of a new Availability Group replica, and in Azure whenever something triggers a database copy…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORE Wait TypeRelated pillar: Performance & Troubleshooting WAIT_ON_SYNC_STATISTICS_REFRESH is recorded when a query has to wait for a synchronous statistics update to finish before it can compile and run. With auto-update statistics in its default synchronous mode, a query that trips…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting WAIT_XTP_CKPT_CLOSE is recorded when a thread waits for an In-Memory OLTP (XTP) checkpoint to complete. XTP checkpoints differ from disk-based ones: they continuously roll data into checkpoint file pairs, and a checkpoint “close” seals…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting WAIT_XTP_HOST_WAIT is recorded when a thread waits for an In-Memory OLTP operation that was initiated by the regular database engine and implemented by the XTP host layer, the bridge through which the traditional engine…