Understanding ASYNC_IO_COMPLETION Wait type in SQL Server

Normally, ASYNC_IO_COMPLETION Wait type can be seen during backup and restore activities. Whenever you will see this wait type your backup/restore process will be in suspended state most of the time because the process is waiting to get IO resource to proceed their operation and it will wait till certain time period then moved in suspended state. In that case your process will take more than its usual time to complete or most of the time it will hung or will showing in executing state for unknown time duration.

Related Articles:

ASYNC_IO_COMPLETION wait type occurs when a task is waiting for asynchronous I/Os to finish.  This wait type is normally seen with few other wait types like BACKUPBUFFER, BACKUPIO etc. This is clear indication of DISK IO issue. You can also get the Average disk queue length or current disk queue length value at the same time when you are getting this wait type. Compare both counters and if these counters have high value then you should look into your storage subsystem. Identify disk bottlenecks, by using Perfmon Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN.

Any of the following will reduce this wait type occurrence:

  1. Add additional IO bandwidth.
  2. Balancing IO across other drives. If you are running your backups on same drive in which you have placed your database files. Make sure to keep separate drive for data, log and backups.
  3. Reducing IO with appropriate indexing. This can cause major performance issue if you don’t have proper indexes.
  4. Check for bad query plans that are consuming most of resources.
  5. Check memory pressure if your server has enough memory to run resource extensive operations.

We can also correlate this wait type between Memory pressure and Disk IO subsystem issues.

If you like this tip, you can follow us on our facebook page and on Twitter handle to get latest updates.

Manvendra Deo Singh
Follow me:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *