SQL Server Performance

"Normal" backup speed?

Discussion in 'Performance Tuning for DBAs' started by cmccormick, Oct 14, 2005.

  1. cmccormick New Member

    Hello all, first post here.

    I am a new SQL Server DBA, and one of my first jobs is optimizing some of our backups. I have read a good bit (here, other forums, books, MS's site, etc.) but can't find any good resources for estimating what a "normal" backup time (for a given setup) would be.

    I know that it's impossible to get an exact answer, but I'd appreciate thoughts about a "ballpark" time.

    Our setup:
    HP Proliant 720DL
    Windows Server 2003 Enterprise Edition (pre SP-1)
    4 CPUs: XEON MP 2.8 GHz
    8 GB RAM
    SQL Server 2000 Enterprise Edition SP3 (8.00.760)

    The DB is stored on four 360GB (virtual) disks presented to the server by an HP EVA 5000 (SAN). The disks are fibre channel 10K RPM. They are HP's "VRaid1" (mirrored). The entire network (2GB) is multipathed using Compaq SecurePath.

    The DB files are allocated at about 1.3TB, but are currently using about 780GB of that.
    There are multiple files and filegroups. Each filegroup is split across all four disks.

    Ok, so the backup takes (mostly overnight, with little activity): 16 hours.

    Is this about right? Way too long? Pretty fast for such a large amount of data? I have read and read and can't find much to go on.

    Any help greatly appreciated.


  2. satya Moderator

  3. indshri Member

    Frankly I don't have exact answer, but have some thoughts if you cantry it out. Distribution of db files does not matter. What matters is your backup devices. First ensure that your backup is spread across all the disks So that all the disks are involved. Another setting that you can try is enable Hyper Threading if not, so that there will 8 logical cpu's.

    And lastly you may want to shrink the db. I know this may take long time. You said that 1.3 TB is allocated however current size is 780 GB, which means 1/3rd is not used. I don't know your requirement but may be you can allocate extra 75-100 GB so that total size is around 850-900 GB. However I am not sure whether it will improve or not. I think sql server will still backup those unallocated space even though it does not contain data hence I think it should improve, but I am not sure.
  4. dineshasanka Moderator

  5. satya Moderator

    To avoid such long hours backup, you may try using log shipping to the secondary server. IN this case you will have opportunity to providing hot stand by in case any issues with the production server.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page