locked
What are reasons for wait types listed below, while doing SQL 2016 db restore ? RRS feed

  • Question

  •               

    HI All, 

    BACKUPTHREAD          

    PREEMPTIVE_OS_WRITEFILEGATHER   - IFI enabled 
    BACKUPIO
    ASYNC_IO_COMPLETION



    And do you think, Virus full scans will be causing these wait types ? 

    please note that the server is on VM, IFI is enabled at VM level not at VM Host level. 

    it has around 200 gigs of empty space in log file for sure, which I cant avoid,I cant control source of backup, so I cant shrink log file before it comes. . 



    please advice 


    Tuesday, October 16, 2018 9:37 AM

Answers

  • sure,i will do that..!! will anti virus scanning cause these wait types ? 

    i have this server on VM , so when scan is running on VM host i see these wait types in restore, and i see too many i/o occurrences, it dosent happen when scan is not running. 

    scan is not allowed to scan, Microsoft suggested folders and extensions, however when it scans vm file, or any other server on on this vm host which is sharing same SAN, this is happening. I wanted to confirm if this is known issue or mere coincidence. 

    AV is not likely to cause slowness of the backups, I have not seen this but yes IF there is VM snapshot backup happening then it could interfere. Also I suggested you to remove ALL SQL Server folders from purview of scanning this is very important please do it if not done already. 

    Or you can simply stop scanning and check if scanning is really making things slow. AFAIK scanning takes lock on database files and folders but I have not noticed anybody saying that due to AV scanning the backup was slow


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    • Marked as answer by datalearner Wednesday, October 17, 2018 8:59 AM
    • Edited by Shanky_621MVP Wednesday, October 17, 2018 9:05 AM
    Wednesday, October 17, 2018 7:04 AM

All replies

  • If you just search all the wait stats on SQLSKILLS.COM you will get all the answer. Just type the wait type and then sqlskills and you would see the link giving you details about all the wait types.

    All these waits are pretty normal when you restore a database. I am sure this is linked to your old question, you are looking at all the aspect but not at disk drive, I told you the drives are slow


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Tuesday, October 16, 2018 10:36 AM
  • i love the way you moderate :D 

    yes, its about same issue, disk is certainly slow but my sysadmins are not admitting it, so I am collecting all possible measures to prove that, one of them is showing them the wait types, and also i am trying to find if these waits come into picture if anti virus scan is running. 

    Tuesday, October 16, 2018 2:08 PM
  • i love the way you moderate :D 

    yes, its about same issue, disk is certainly slow but my sysadmins are not admitting it, so I am collecting all possible measures to prove that, one of them is showing them the wait types, and also i am trying to find if these waits come into picture if anti virus scan is running. 

    I understand, but my friend I guess you would have to cajole him I am sure I gave you sqlskills link which had query to calculate disk latency. Please show it to the sysadmins if still they do not agree I guess I can only say "you can take horse to the well but cannot force it to drink water" :D

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    • Proposed as answer by Naomi N Tuesday, October 16, 2018 4:01 PM
    • Edited by Shanky_621MVP Tuesday, October 16, 2018 4:23 PM
    Tuesday, October 16, 2018 3:32 PM
  • sure,i will do that..!! will anti virus scanning cause these wait types ? 

    i have this server on VM , so when scan is running on VM host i see these wait types in restore, and i see too many i/o occurrences, it dosent happen when scan is not running. 

    scan is not allowed to scan, Microsoft suggested folders and extensions, however when it scans vm file, or any other server on on this vm host which is sharing same SAN, this is happening. I wanted to confirm if this is known issue or mere coincidence. 

    Tuesday, October 16, 2018 5:55 PM
  • sure,i will do that..!! will anti virus scanning cause these wait types ? 

    i have this server on VM , so when scan is running on VM host i see these wait types in restore, and i see too many i/o occurrences, it dosent happen when scan is not running. 

    scan is not allowed to scan, Microsoft suggested folders and extensions, however when it scans vm file, or any other server on on this vm host which is sharing same SAN, this is happening. I wanted to confirm if this is known issue or mere coincidence. 

    AV is not likely to cause slowness of the backups, I have not seen this but yes IF there is VM snapshot backup happening then it could interfere. Also I suggested you to remove ALL SQL Server folders from purview of scanning this is very important please do it if not done already. 

    Or you can simply stop scanning and check if scanning is really making things slow. AFAIK scanning takes lock on database files and folders but I have not noticed anybody saying that due to AV scanning the backup was slow


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    • Marked as answer by datalearner Wednesday, October 17, 2018 8:59 AM
    • Edited by Shanky_621MVP Wednesday, October 17, 2018 9:05 AM
    Wednesday, October 17, 2018 7:04 AM