Search sqlserver-dba.com
Follow sqlserver-dba.com
*Use the Comments section for questions
SQLServer-DBA.com Links
Recent Posts
- sys.dm_server_services - How to check if SQL Server service is running or not?
- Msg 3023, Level 16, State 2 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
- Can we restore a database to an older version in SQL Server?
- How do I save all SQL Agent Jobs
- How to view SQL Server mount point space without access to server
- DBCC SHRINKFILE: page could not be moved because it is a work table page.
- SQL Agent Last Modified explanation
- Sql certificate chain was issued by an authority that is not trusted
- sqlserver-dba.com switching from feedburner to follow.it
- Taking transaction log backup and full back at the same time
ASYNC_IO_COMPLETION during sql server backup
18 January,2017 by Tom Collins
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: Tom Collins (http://www.sqlserver-dba.com)
Share:
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |