SQL Server Performance Forum – Threads Archive
ASYNC_IO_COMPLETION
Hi, There is something going on with a server.I am trying to restore a 23G on the server where the actual database not exists.
i used to be able to restore this db in 20 min and all of the sudden it takes hours now.
I am getting this ASYNC_IO_COMPLETION from sysprocesses.The strange thing is there is nothing executing on the server at this time and there is no IO activity at all on the server. This is Win 2003 server with SQL Server 2000 sp4 build 2187.The server is clustered and has SAN.
Have you seen this before and what can be done to speed up the restore? Thanks
Here is appr. file sizes for this backup set: file01.mdf – 500K (done in 15 min)
file02.ndf – 5G (done in 4 hours)
file03.ndf – 5G (done in 5.5 hours)
file04.ndf – 10G (still restoring)
file05.ndf
One of the KBA refers:
quote:
This waittype indicates that the SPID is waiting for the asynchronous I/O requests to complete. Like the IO_COMPLETION waittype, this waittype also indicates an I/O bottleneck. You may see this waittype for the SPIDs during the long-running I/O-bound operations, such as BACKUP, CREATE DATABASE, ALTER DATABASE, or the database autogrow. This waittype may also indicate disk bottlenecks.
This waittype indicates that the SPID is waiting for the asynchronous I/O requests to complete. Like the IO_COMPLETION waittype, this waittype also indicates an I/O bottleneck. You may see this waittype for the SPIDs during the long-running I/O-bound operations, such as BACKUP, CREATE DATABASE, ALTER DATABASE, or the database autogrow. This waittype may also indicate disk bottlenecks.
If you say it is taking hours now, see what has been changed on Operating system, any issues with disks or network card etc. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thank you, I canceled restore and performed some testing. Just tried to create new database to see what will happen. It#%92s interesting that when I create a database with only one file with 500M all ok, when database creates with 2 100M files then also all ok, but when for example first file is 500M (created instantly) and the second file also 500M, the second file just takes hours. We still looking into the problem.
Update: we found a problem.It was a hardware issue.
I don’t think it is a problem and do you have such need to created a 500GB database on the fly? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>