SQL DBA Blog: Home

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 messages on a queue, letting you react asynchronously to things happening in the engine. The wait’s volume therefore mirrors the firing…

read more

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 used when a client consumes trace events live, which in practice means SQL Server Profiler or an application reading a trace…

read more

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 registration passes through this small lock. Broker connection lifecycle bookkeeping, in a word. Is It a Problem? No; it has not…

read more

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 (service tier changes, geo-replication setup). The quirk that earns it a page: the system threads created for seeding, on the primary…

read more

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 the staleness threshold on a table’s statistics stops, updates those statistics right there, and only then compiles. This wait, added so…

read more

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 the current set. Threads coordinating with that close event park here. Present wherever memory-optimized filegroups exist, however lightly used. Is It…

read more

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 and the in-memory engine cooperate. Cross-engine housekeeping lands its wait time here. Another member of the XTP background family, present wherever…

read more

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 them into checkpoint file pairs; when the scanner has to physically read log from disk, the read time lands here. Its…

read more

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 to read-write). Memory-optimized tables must be fully reloaded into memory from checkpoint files and log before the database is usable. The…

read more

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, instance shutdown, or internal reorganisation, means waiting for them to finish. Lifecycle machinery, active at endings. Is It a Problem? No;…

read more