ASYNC_IO_COMPLETION

This occurs when SQL Server is waiting for asynchronous I/O operations to finish. 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:

  • 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 exists. It often 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 you can’t see I/O pressure from these metrics, check the affected queries and the Top queries table:

  • 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 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.
  • Consider splitting data across different physical drives to distribute the I/O workload more evenly.

Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com


Didn't find what you were looking for?