Part of the SQL Server Wait Types Library
IO_COMPLETION covers I/O waits that happen outside the normal buffer pool page path. Where PAGEIOLATCH_SH and PAGEIOLATCH_EX track data page reads and writes through the buffer pool, IO_COMPLETION tracks everything else: sort spills to tempdb worktables, backup file writes, restore reads, and DBCC work file I/O.
The wait type name is generic, which makes it harder to diagnose than the PAGEIOLATCH_* family. The first step is always figuring out what type of I/O is generating the wait.
What Generates IO_COMPLETION
Sort spills to tempdb, the most common cause in production. When a query does not get enough memory to complete a sort or hash operation in-memory, it spills to disk, writing and reading back worktable rows in tempdb. Each spill read and write generates IO_COMPLETION. Unlike PAGEIOLATCH_*, these are worktable pages, not buffer pool pages, so they appear as IO_COMPLETION instead.
Backup operations, writing backup data to disk (or a network share) generates IO_COMPLETION on the backup I/O threads. If backup jobs are running, elevated IO_COMPLETION is expected.
DBCC CHECKDB and DBCC CHECKTABLE, DBCC creates internal work files in tempdb for its snapshot and processing work. Large databases running DBCC will show IO_COMPLETION.
Database restore, reading backup files during a restore generates IO_COMPLETION on the restore threads.
Bulk operations using row set caches, some bulk insert paths use tempdb staging that goes through IO_COMPLETION rather than the buffer pool.
Is This Wait Expected?
During backup windows, yes. If your backup job runs at midnight and IO_COMPLETION spikes then, it is just the backup. Check the timing correlation.
During DBCC runs, yes. DBCC CHECKDB on a large database generates substantial tempdb I/O.
Outside of maintenance windows, investigate. IO_COMPLETION during production hours usually means sort spills, which means queries are not getting the memory they need (missing indexes or stale stats) or tempdb I/O is slow.
How To Identify The Cause
Check whether backup jobs are running:
SELECT
job.name AS job_name,
ja.start_execution_date,
ja.last_executed_step_date,
jh.run_status
FROM msdb.dbo.sysjobs job
JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = job.job_id
JOIN msdb.dbo.sysjobhistory jh ON jh.job_id = job.job_id
WHERE ja.start_execution_date IS NOT NULL
AND ja.stop_execution_date IS NULL
ORDER BY ja.start_execution_date DESC;
Or simply check if BACKUPIO is also elevated in the wait stats, backup operations generate both.
Find queries that are spilling to tempdb:
SELECT TOP 20
qs.total_spills / qs.execution_count AS avg_spills_per_exec,
qs.total_spills,
qs.execution_count,
qs.total_grant_kb / qs.execution_count AS avg_grant_kb,
qs.total_worker_time / qs.execution_count AS avg_cpu_us,
SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qs.total_spills > 0
ORDER BY qs.total_spills DESC;
Queries with avg_spills_per_exec > 0 are consistently spilling.
Check tempdb I/O specifically:
SELECT
mf.name AS file_name,
mf.type_desc,
mf.physical_name,
vfs.io_stall_write / 1000.0 AS write_stall_sec,
vfs.io_stall_read / 1000.0 AS read_stall_sec,
vfs.num_of_writes,
vfs.num_of_reads,
CASE WHEN vfs.num_of_writes > 0
THEN vfs.io_stall_write / vfs.num_of_writes ELSE 0 END AS avg_write_ms,
CASE WHEN vfs.num_of_reads > 0
THEN vfs.io_stall_read / vfs.num_of_reads ELSE 0 END AS avg_read_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) vfs -- database_id 2 = tempdb
JOIN sys.master_files mf
ON mf.database_id = vfs.database_id
AND mf.file_id = vfs.file_id
ORDER BY vfs.io_stall_write + vfs.io_stall_read DESC;
High avg_write_ms or avg_read_ms on tempdb data files during production hours, combined with spilling queries, confirms sort spills are the cause.
What To Do
For sort spills (the most common cause):
The root cause is almost always a missing index or stale statistics. A query that spills to disk is doing work it should not need to do.
- Identify the spilling queries from
dm_exec_query_statsabove - Run their execution plans in SSMS with “Include Actual Execution Plan”, look for Sort operators with a yellow warning triangle (“Warnings: Operator used tempdb to spill data”)
- Check for missing indexes on the tables involved:
.\run.ps1 Get-MissingIndexes
- Update statistics on the involved tables
- If the query legitimately needs to sort large data sets (e.g. a reporting query), consider whether the result set can be reduced before sorting
For tempdb I/O performance in general:
– Tempdb data files should be on fast storage, ideally the same SSD/NVMe tier as production data
– Multiple tempdb data files (one per physical core, up to 8) reduce allocation contention, though they do not directly improve sort spill I/O
For backup-driven IO_COMPLETION:
– Enable backup compression, less data written means faster backup I/O
– Schedule backups during low-traffic windows
– Consider offloading backups to an AG secondary replica
For DBCC-driven IO_COMPLETION:
– Run DBCC CHECKDB during off-peak hours
– On Enterprise Edition, DBCC with PHYSICAL_ONLY is faster and generates less tempdb I/O (though it checks less)
Related Scripts
Get-WaitStatistics, the overview scriptGet-MissingIndexes, the usual fix for sort spillsGet-StatisticsHealth, stale stats cause bad memory grant estimates leading to spillsGet-TempdbHotspots, tempdb allocation contention
Leave a Reply