Is It a Problem?
Not usually. It is not reported as a contention point, and it is zero on servers without memory-optimized tables, as on our SQL Server 2025 lab. Get Wait Statistics does not filter it out, so if it ranks, you will see it.
What It Means
Microsoft’s definition: it occurs when offline checkpoint is waiting for a log read IO to complete. The offline checkpoint, one thread on SQL Server 2014 and several from 2016, processes changes into checkpoint file pairs, and this is one of those threads waiting for a log block to be read from disk. Its sibling WAIT_XTP_OFFLINE_CKPT_NEW_LOG is the same thread with nothing new to read.
When It Does Matter
Only alongside real In-Memory OLTP checkpoint symptoms, such as the log not truncating. Then check log file read latency:
SELECT
DB_NAME(vfs.database_id) AS database_name,
mf.type_desc,
mf.physical_name,
vfs.num_of_reads,
CAST(vfs.io_stall_read_ms * 1.0 / NULLIF(vfs.num_of_reads, 0) AS DECIMAL(12, 2)) AS avg_read_ms,
vfs.num_of_writes,
CAST(vfs.io_stall_write_ms * 1.0 / NULLIF(vfs.num_of_writes, 0) AS DECIMAL(12, 2)) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
WHERE mf.type_desc = N'LOG'
ORDER BY vfs.io_stall_read_ms + vfs.io_stall_write_ms DESC;
and sys.dm_db_xtp_checkpoint_files for the checkpoint files. The baseline to monitor is the log of that database truncating normally. The query needs VIEW SERVER STATE.
The XTP Family
These are the In-Memory OLTP (XTP) waits, present on databases with a memory-optimized filegroup. Almost all of them are background checkpoint and housekeeping waits. Memory-optimized tables still write to the transaction log, so the main page for this family is WRITELOG.
- WRITELOGLog flush. Waiting for log records to be written to disk, the log wait that costs users time.The main page for this family
- WAIT_XTP_CKPT_CLOSECheckpoint complete. Waiting for an In-Memory OLTP checkpoint to complete.Usually noise
- WAIT_XTP_HOST_WAITHost waits. Waits triggered by the database engine and implemented by the XTP host.Usually noise
- WAIT_XTP_OFFLINE_CKPT_NEW_LOGWaiting for log. The offline checkpoint waiting for new log records to scan.Usually noise
- WAIT_XTP_OFFLINE_CKPT_LOG_IOLog read. The offline checkpoint waiting for a log read to complete.Usually noise. This page.
- WAIT_XTP_RECOVERYRecovery. Database recovery waiting for memory-optimized objects to recover.Worth a look when it climbs
- WAIT_XTP_TASK_SHUTDOWNThread shutdown. Waiting for an In-Memory OLTP thread to complete.Usually noise
- XTP_PREEMPTIVE_TASKBackground work. Documented as internal use only; In-Memory OLTP background work in preemptive mode.Usually noise
Where To Go Next
WAIT_XTP_OFFLINE_CKPT_LOG_IO is background noise on most instances. If you are chasing a real problem, start with the waits that cost you time.
- The waits that usually matter, ranked, with what each one is telling you.
- WRITELOG, the log wait that memory-optimized tables still pay.
- Performance & Troubleshooting, the scripts and guides for this part of the stack.
Leave a Reply