Author: Peter Whyte

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

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting XE_BUFFERMGR_ALLPROCESSED_EVENT is recorded on a background thread while Extended Events session buffers are flushed to their targets, waiting for the “all buffers processed” signal. Flush-everything moments happen at session stops, target-state queries, and checkpoint-like…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting XE_DISPATCHER_WAIT is recorded by the background dispatcher threads that serve Extended Events asynchronous targets, while they wait for filled event buffers to process. Events accumulate in session buffers; when a buffer is ready (full,…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting XE_FILE_TARGET_TVF is recorded while a thread performs operations for a query reading event data from an Extended Events file target via sys.fn_xe_file_target_read_file, the function behind most XE analysis queries, including reading system_health files. It…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting XE_LIVE_TARGET_TVF is recorded while a thread services a query that pulls data from an Extended Events live data stream, most commonly the “Watch Live Data” viewer in SSMS. The live target streams events to…