SQL Server Performance

ASYNC_IO_COMPLETION

Discussion in 'Performance Tuning for DBAs' started by shark68, Jun 19, 2007.

  1. shark68 New Member

    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


  2. satya Moderator

    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.

    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.
  3. shark68 New Member

    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.
  4. satya Moderator

    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.

Share This Page