SQL Server Performance

SQL 2008 backup time is taking too long

Discussion in 'ALL SQL SERVER QUESTIONS' started by WingSzeto, Jul 11, 2012.

  1. WingSzeto Member

    I do daily full back up of a 150G bytes database to a local drive of its server and it took 2 hours and 45 mins. This database is on SQL 2008 standard sp3 server say server A. I have the exact same database on a different SQL server machine say server B and it took 10 mins. The only difference is server B has SQL 2008 r2 standard sp1. However, I also have a SQL 2008 standard sp3 machine say server C, which is backing up a 240 Gbytes db in 20 mins. The backup job occurred at 2:00 am on server A which should be a quiet time and I checked if there are any jobs and windows scheduled tasks running during the back up on server A and I found none. I ran a test backup on server A and found IO and cpu are not being overwhelmed. In fact, the performace encounters (average read/write queue IO) are within the threadholds.
    What could be wrong with the backup on server A? Any thoughts on this are very much appreciated.
  2. TimH New Member

    Is the hardware identical on all three systems? What about workload - is ServerA handling production activity, and the other servers are test beds with little activity?
  3. Shehap MVP, MCTS, MCITP SQL Server

    Backup almost has nothing to do with SQL Server version but some parameters might impact negatively on the time of SQL Server backup ( I am assuming here no other third party tools such as VERITAS/Symantec backup exec ..etc)

    1. File stream files that consumes more time to backup from their cached NTFS directories …

    2. Less memory resources or memory pool assigned for SQL Service

    3. Poor IO subsystem performance such as you use ATA or SATA disks instead of SAS disks.

    4. Storage capacity bottleneck which can impact tangibly sometimes on IO performance thereby you have to free spacec

    5. Poor SAN configurations as all of SAN LUNs are in one RAID group or SAN LUNs have fraction for poor value for partitioning offset alignment ( optimal =8 )

    I do think the assumptions 2-4 can apply for your case so kindly work out them and let me know your feedback

Share This Page