Category: Wait Types

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

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting WAIT_XTP_OFFLINE_CKPT_LOG_IO is recorded when an In-Memory OLTP offline checkpoint thread waits for a log read I/O to complete. The XTP checkpoint mechanism continuously scans the transaction log for changes to memory-optimized tables and folds…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting WAIT_XTP_RECOVERY is recorded when database recovery waits for the recovery of memory-optimized objects to finish. It appears during crash recovery, after failovers and restores, and around certain database state changes (offline to online, read-only…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting WAIT_XTP_TASK_SHUTDOWN is recorded while a thread waits for an In-Memory OLTP thread to complete and shut down. XTP runs its own background threads (checkpoint workers, garbage collection helpers), and orderly teardown, at database offline,…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting WAITFOR_PER_QUEUE is recorded when a Service Broker worker thread waits for a message on a specific queue as part of an activation procedure’s WAITFOR (RECEIVE …). Each wait runs up to the statement’s timeout,…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETIONRelated pillar: Performance & Troubleshooting WRITE_COMPLETION appears when a thread waits for a synchronous file write to finish. Ordinary dirty-page flushing goes through checkpoint and the lazy writer (showing up as other waits), so WRITE_COMPLETION covers the special cases: writes to…