Backup Job Performance

Question

Post reply

Backup Job Performance

Wecks

Right there with Babe

Points: 778
More actions
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?
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

DinoRS SSCrazy Points: 2683 More actions · Answer 1

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

Andrey SSC Eights! Points: 840 More actions · Answer 2

another thing to check :

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

Wecks Right there with Babe Points: 778 More actions · Answer 3

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.

Wecks Right there with Babe Points: 778 More actions · Answer 4

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?

Andrey SSC Eights! Points: 840 More actions · Answer 5

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

Wecks Right there with Babe Points: 778 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

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)

Maxwel6396 Newbie Points: 1 More actions · Answer 8

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