WAIT_XTP_OFFLINE_CKPT_LOG_IO Wait Type in SQL Server

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting

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.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *