Question: I’m getting a very high level of ASYNC_IO_COMPLETION during a SQL Server backup schedule. These are very small databases , usually taking a few minutes, but now taking over 1 hr each. My suspicion is it’s an underlying storage issue, but I need a straightforward method to show the storage guys.
Answer: This is one of those situations where it’s difficult to present evidence to the storage team .
The usual definition of ASYNC_IO_COMPLETION is SQL Server Books online defines Async_io_completion as “Occurs when a task is waiting for I/Os to finish “. Read more on SQL Server Async_io_completion and how to reduce it (SQL Server ...
This definition does not convince any storage person! Other information is required. A good way to approach the problem , if it’s backup related .
A quick and dirty method, is to use the backup to nul trick. This presents the read time over gathering the database files. You can then subtract the read time from total backup time . This gives you some objective timings and strong direction on bottleneck
The ASYNC_IO_COMPLETION wait type during backups is severs will usually be accompanied by IO Requests taking longer than 15 seconds to complete in the SQL Server Error Logs