IO_COMPLETION
Published 28 March 2024
This occurs when SQL Server is waiting for I/O operations to finish that don’t read table or index rows from disk. It’s normal for a thread to enter a wait state as soon as it requests an I/O operation, but a problem may exist if the wait time is significant and your top waits list also includes at least one of the following:
ASYNC_IO_COMPLETION
LOGMGR
WRITELOG
PAGEIOLATCH_*
The SQL Server error log may also contain a message that I/O requests are taking longer than 15 seconds to complete. You need to find out which operation is causing this, and where the bottleneck originates. It commonly occurs during long-running I/O-bound operations, including BACKUP, ALTER DATABASE, CREATE DATABASE or database autogrowth.
Investigating
- Check the following metrics on the Analysis page to find out whether there’s a general problem with your environment. See each metric’s Description tab for guideline values and possible solutions. High values may indicate bottlenecking or excessive pressure on the database engine:
Disk avg. read time
Disk avg. write time
Avg. disk queue length
Buffer cache hit ratio
SQL Server: free memory
Buffer page life expectancy
Machine: memory used
- If the metrics above don’t suggest I/O pressure, check the queries affected by this wait:
- Look for queries affected by the ASYNC_IO-COMPLETION, LOGMGR, WRITELOG and PAGEIOLATCH_* waits.
- Also check for queries affected by waits indicating memory pressure, such as RESOURCE_SEMAPHORE. Memory pressure can cause increased disk activity.
- Sort the top queries and the queries related to this wait by Physical reads or Logical writes to see those with the greatest impact on I/O.
- Check the query plans to see if they’re accessing the disk unnecessarily.
- Tune queries if necessary: check for missing indexes, or indexes containing an incorrect sort order. See: Query tuning (TechNet) and Showplan Operator of the Week - SORT.
As a last resort, review whether disk resources are adequate for your server load:
If your disk drivers and BIOS are out of date, consider upgrading.
Think about moving log files to a different disk, so they’re separate from database data files. See: Storage Top 10 Best Practices.
- Consider splitting data across different physical drives to distribute the I/O workload more evenly.
