backup buffer and backupIO wait stats

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    top 2 waitstats on one of the server are BackupBuffer and BackupIO. The backup is being done at a network location using symantec backup utility. There are around 20 databases with transaction log backup scheduled to occur at 30 min interval. Does that mean that I need to add storage to the server or to the network location where the backup is being done.

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • dmoldovan

    SSCertifiable

    Points: 6334

    Do you back up on a tape? Any trouble with that tape?

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    As I said..its a network storage.

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • Grant Fritchey

    SSC Guru

    Points: 396209

    No, waits are not because of inadequate storage. It's because either the network, or the storage system itself, is running slow and causing SQL Server to wait on it. Most of time, most people, try to perform backups to a local drive for the server and then copy it across the network to networked storage just because you don't want to subject the database backup routine to the vagaries of network traffic.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    Grant Fritchey (4/29/2010)


    No, waits are not because of inadequate storage. It's because either the network, or the storage system itself, is running slow and causing SQL Server to wait on it. Most of time, most people, try to perform backups to a local drive for the server and then copy it across the network to networked storage just because you don't want to subject the database backup routine to the vagaries of network traffic.

    I also had a similar view but I am also getting pagelatch waits in top 10 wait stats.

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • Grant Fritchey

    SSC Guru

    Points: 396209

    Sqlfrenzy (4/29/2010)


    Grant Fritchey (4/29/2010)


    No, waits are not because of inadequate storage. It's because either the network, or the storage system itself, is running slow and causing SQL Server to wait on it. Most of time, most people, try to perform backups to a local drive for the server and then copy it across the network to networked storage just because you don't want to subject the database backup routine to the vagaries of network traffic.

    I also had a similar view but I am also getting pagelatch waits in top 10 wait stats.

    If you're getting pagelatch waits, I'd suggest reading this from the SQL CAT.

    I still don't think that has anything at all with space allocation at the backup location.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    Grant Fritchey (4/29/2010)


    Sqlfrenzy (4/29/2010)


    Grant Fritchey (4/29/2010)


    No, waits are not because of inadequate storage. It's because either the network, or the storage system itself, is running slow and causing SQL Server to wait on it. Most of time, most people, try to perform backups to a local drive for the server and then copy it across the network to networked storage just because you don't want to subject the database backup routine to the vagaries of network traffic.

    I also had a similar view but I am also getting pagelatch waits in top 10 wait stats.

    If you're getting pagelatch waits, I'd suggest reading this from the SQL CAT.

    I still don't think that has anything at all with space allocation at the backup location.

    If its because of network..I think if i decrease the tlog backup time from 30min to 15min then it will be of smaller size and It may help..

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • Paul White

    SSC Guru

    Points: 150442

    Sqlfrenzy (4/29/2010)


    top 2 waitstats on one of the server are BackupBuffer and BackupIO. The backup is being done at a network location using symantec backup utility. There are around 20 databases with transaction log backup scheduled to occur at 30 min interval. Does that mean that I need to add storage to the server or to the network location where the backup is being done.

    I have had fun with NetBackup and Backup Exec in the past 🙁

    You don't say whether you are seeing a problem in terms of backup throughput or not.

    Have you tried performing a native backup direct to the same network location for comparison?

    There's a process to go through to determine where the bottleneck is.

    There's some excellent advice in the following White Paper, documenting how a 2TB database was backed up (very) remotely in 34 minutes:

    http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Technical%20Case%20Study-Backup%20VLDB%20Over%20Network_Final.docx (Word document - well worth the download).

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    Paul White NZ (5/3/2010)


    Sqlfrenzy (4/29/2010)


    top 2 waitstats on one of the server are BackupBuffer and BackupIO. The backup is being done at a network location using symantec backup utility. There are around 20 databases with transaction log backup scheduled to occur at 30 min interval. Does that mean that I need to add storage to the server or to the network location where the backup is being done.

    I have had fun with NetBackup and Backup Exec in the past 🙁

    You don't say whether you are seeing a problem in terms of backup throughput or not.

    Have you tried performing a native backup direct to the same network location for comparison?

    There's a process to go through to determine where the bottleneck is.

    There's some excellent advice in the following White Paper, documenting how a 2TB database was backed up (very) remotely in 34 minutes:

    http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Technical%20Case%20Study-Backup%20VLDB%20Over%20Network_Final.docx (Word document - well worth the download).

    I haven't compared it with native backups and it's difficult because the problem is on production server.

    will go through the doc...it is for 2008 and am on 2005.

    thanks.

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • Paul White

    SSC Guru

    Points: 150442

    Sqlfrenzy (5/3/2010)


    I haven't compared it with native backups and it's difficult because the problem is on production server.

    Understood. Remember that COPY_ONLY backups are available if that helps.

  • Grant Fritchey

    SSC Guru

    Points: 396209

    Sqlfrenzy (5/3/2010)


    Paul White NZ (5/3/2010)


    Sqlfrenzy (4/29/2010)


    top 2 waitstats on one of the server are BackupBuffer and BackupIO. The backup is being done at a network location using symantec backup utility. There are around 20 databases with transaction log backup scheduled to occur at 30 min interval. Does that mean that I need to add storage to the server or to the network location where the backup is being done.

    I have had fun with NetBackup and Backup Exec in the past 🙁

    You don't say whether you are seeing a problem in terms of backup throughput or not.

    Have you tried performing a native backup direct to the same network location for comparison?

    There's a process to go through to determine where the bottleneck is.

    There's some excellent advice in the following White Paper, documenting how a 2TB database was backed up (very) remotely in 34 minutes:

    http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Technical%20Case%20Study-Backup%20VLDB%20Over%20Network_Final.docx (Word document - well worth the download).

    I haven't compared it with native backups and it's difficult because the problem is on production server.

    will go through the doc...it is for 2008 and am on 2005.

    thanks.

    Most of the syntax and concepts will be the same between 2008 & 2005. Compression, of course, being the exception.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply