Post reply

Backup Job Performance

  • May 2, 2019 at 8:35 am

    #3621713

    I have a Legacy SQL Server 2014 Cluster that had about 2TB worth of database on it.  The Nodes were Physical 16 cores with around Half a TB of RAM in each node.  Only one Instance resides on the instance with around 20 databases on it. These nodes are about 8 years old and the storage is presented from an old SAN.

     

    As part of a refresh the instance has been migrated new physical hardware with a new enterprise presented storage, same SQL versions installed (Vendor Requirements),  same CPU count and RAM.

    There is a backup schedule in place, Weekly full and a daily differential.  on the old cluster the Full backups would usually complete in 5 hours and the Differentials in about an hour.

    On the new environment these backups are taking about 20 hours to complete a full and about 5 hours for Diffs depending on the day in the week.

    I cant see anything obvious that may be causing this issue.  The servers are not under any load during the backup window start times.  Any advice on how we can get these backup times reduced?

  • May 2, 2019 at 11:05 am

    #3624630

    Here's some food for thought:

    • Are the NICs on the new cluster configured the same in regards to Offloading, Packet Size and the likes?
    • Tweak your Backup Jobs around Buffercount, Blocksize and Maxtransfersize
    • write the Backup to multiple files / locations

     

  • May 2, 2019 at 11:22 am

    #3624981

    another thing to check :

    select * from sys.configurations where name = 'backup compression default'

  • May 8, 2019 at 8:48 am

    #3638159

    Thanks for your reply.

    To be honest i dont have the ability to write the backups to multiple locations but the backups are located on a completely separate volume that is just used for a backup repository.

    Backup compression is selected

    Both clusters mirror each other in how they are configured as this was a requirement of the customer i just can not see how on the old legacy cluster backup performance is alot better whilst with each week that passes full backups and differentials are taking longer and longer to backup.

    I will ask about the nic configuration but i am interested in what you were saying in relation to tweaking the backup jobs.  currently the backups are running off of some simple maintenance plans.  I will follow the link you have provided and see if it can improve the overall backup times.

     

    Many thanks.

     

     

  • May 13, 2019 at 10:27 am

    #3641362

    I have isolated a number of DBS where there backup process is taking along time.  I believe there maybe wait issues that will need resolving but i did as advised and modified the MAXTRANSFERSIZE and BUFFERCOUNT.

     

    On another note i am utilising the ola hallengren backup scripts and have always used but each db backup is done one after another.  Just out of curiosity if i was to split these out is there a specific number of backup operations that can run in tandem?

     

     

  • May 13, 2019 at 11:57 am

    #3641389

    Wecks wrote:

    Just out of curiosity if i was to split these out is there a specific number of backup operations that can run in tandem?    

    imho, it could make sense only when you have different targets (storage)

    otherwise, backing up 2 and more databases to the same storage (disk) in parallel will create another bottleneck

     

  • May 13, 2019 at 1:16 pm

    #3641457

    Thanks for the Reply,  The Backup Volume is a CSV presented from a SAN so I am hoping consecutive writes would not cause to much of a bottle neck.

  • May 13, 2019 at 4:15 pm

    #3641586

    Let's stop an think about this... the backups didn't use to be a problem and moving to the new hardware is now causing a problem.  It could be anything from too low local CPU cache if they went cheap to NIC settings to switch settings to too low cache on the SAN to bad cables/terminations or even a too tight bend in a cable.  Or maybe the new hardware just sucks or your backups are now shared on really busy LUNs. 😀

    We had such problems when we moved an it turned out to be a high resistance connection on one pin of one cable.  Don't ask me how they found it but when they fixed it, all was good.

    Also, if your SAN is disk based (ie: non-SSD based), switching your backups to write to multiple files may actually slow things down (I've seen it happen several times) because they were writing to the same physical disk sets on the SAN.  Unless you can absolutely guarantee that the "pipe" can handle multiple simultaneous transmissions AND you can guaranteed that the multiple backup files will be written to truly different spindles, your multiple file backups are mostly likely going to slow things down even more.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 14, 2019 at 9:36 am

    #3641799

    On the new environment these backups are taking about 20 hours to complete a full and about 5 hours for Diffs depending on the day in the week. On the old cluster the Full backups would usually complete in Maxwell Keto 5 hours and the Differentials in about an hour.

    Maxwell Keto Reviews Best Weight Loss Supplement

  • November 20, 2019 at 1:01 pm

    This was removed by the editor as SPAM

  • November 20, 2019 at 1:06 pm

    This was removed by the editor as SPAM

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

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