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 operational bite: the database is not available until the thread waiting on this completes.
Is It a Problem?
The wait itself is honest; the duration is the operational fact. In-Memory OLTP recovery time scales with the durable memory-optimized data volume (loading is parallelised and fast per gigabyte, but gigabytes are gigabytes), so databases with large durable memory-optimized tables pay a real availability cost at every recovery event.
If your recovery time objectives are tight, this wait’s duration during a test failover is a number you should know.
Common Causes
- Recovery events on databases with durable memory-optimized tables: restarts, failovers, restores, state changes.
- Large durable memory-optimized data volumes stretching the load.
- Storage read throughput limiting how fast checkpoint files stream into memory.
What To Do
- Measure it: after a controlled failover or restart, the error log’s recovery timings plus this wait show the XTP share of the outage.
- Keep durable memory-optimized data deliberate:
SCHEMA_ONLYtables for data that need not survive restarts, and cleanup of grown-stale durable data. - Give checkpoint file storage genuine read throughput; recovery streams from it.
How To See It
Rank waits with Get-WaitStatistics after recovery events; its total is your XTP recovery cost for that event.
Part of the SQL Server Wait Types Library.
Related deep dive: WRITELOG Wait Type.
Leave a Reply