IO_COMPLETION

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

    • Buffer free pages

    • 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 Practises.

    • Consider splitting data across different physical drives to distribute the I/O workload more evenly.

Didn't find what you were looking for?