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.
- Understanding SQL Server Wait Types
- How to deal with Resource Semaphore Wait type?
- What is BACKUPBUFFER Wait type?
- How to Reduce SQL Server Wait type PREEMPTIVE_OS_WRITEFILEGATHER?
- How to Improve SQL Server Bulk Data Load Performance?
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:
- Add additional IO bandwidth.
- 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.
- Reducing IO with appropriate indexing. This can cause major performance issue if you don’t have proper indexes.
- Check for bad query plans that are consuming most of resources.
- 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.
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018