ASYNC_IO_COMPLETION during sql server backup

18 January,2017 by Jack Vamvas

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

Read More

Disk IO performance , disk block size tuning and SQL Servers

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on ASYNC_IO_COMPLETION during sql server backup


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer