Published 31 January 2017
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:
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.
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.
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.