The
ASYNC_IO_COMPLETION wait type is commonly encountered by a database
administrator. It occurs every time SQL Server performs a file-related action
at storage level and waits for it to complete. As the wait type is related to
storage, you might encounter more often when a backup happens. However, if you
encounter the ASYNC_IO_COMPLETION wait type.
What is the ASYNC_IO_COMPLETION Wait Type?
The ASYNC_IO_COMPLETION wait type occurs when a task is waiting for storage-related action to complete. In general, this wait type is more seen while a backup is happening in your server. The SQL Server monitors the signal from the storage subsystem for faster completion. In case of a delay from the storage side, the ASYNC_IO_COMPLETION wait type occurs. To be simpler, the faster your storage subsystem, the lower you see the ASYNC_IO_COMPLETION wait type.
If you are
seeing this wait type in your server, it means that your SQL Server task is
communicating with the storage subsystem and there should not be any matter of
concern. It generally flags the wait type when a database creation or a
database backup operation takes place. You should take the wait type into
consideration when the wait times are higher than the baseline measurements.
Sys.dm_os_wait_stats
You can
check the ASYNC_IO_COMPLETION wait type using the sys.dm_os_wait_stats DMV.
Before running the DMV, lets clear the information from the sys.dm_os_wait_stats DMV using a simple DBCC command.
use master
go
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
I will now run a full backup in my server. As soon as I execute the full backup, the information gets loaded into the DMV.
select * from sys.dm_os_wait_stats where wait_type='ASYNC_IO_COMPLETION'
How to Lower ASYNC_IO_COMPLETION Wait Type?
In most cases, high ASYNC_IO_COMPLETION wait is due to a database backup. To find if the wait type was because of a backup, then query the DMV with other backup waits.
select * from sys.dm_os_wait_stats where wait_type
in ('ASYNC_IO_COMPLETION', 'BACKUPIO','BACKUPBUFFER')
You can also try to lower the ASYNCH_IO_COMPLETION waits by configuring instant file initialization. Instant file initialization is a feature introduced by Microsoft for Windows 2003 to speed up the disk space allocation.
Bottomline
The ASYNC_IO_COMPLETION wait type is triggered by actions related to the storage subsystem within your SQL Server instance, such as database backups and new database creation. While these waits are generally normal, unusually high wait times can suggest storage issues. Before involving your storage administrator, confirm there is a true performance issue. One way to do this is by checking storage latency, as high latency can increase ASYNC_IO_COMPLETION wait times. Also, verify if the increased wait times coincide with database backups. Enabling instant file initialization by adding your SQL Server service account to the Perfmon volume maintenance tasks local policy can help reduce ASYNC_IO_COMPLETION wait times.
You may also refer:
0 comments:
Post a Comment