Backup command with multiple threads.

Question

Backup command with multiple threads.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
July 22, 2017 at 11:18 pm

#338469

Hi,
Is there any switch / option is available to take the backup in multiple threads (parallelism) so that it can be completed quickly? Do not want to use striped backups though.
Thanks.

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

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

SQL-DBA-01 - Saturday, July 22, 2017 11:18 PM
Hi,
Is there any switch / option is available to take the backup in multiple threads (parallelism) so that it can be completed quickly? Do not want to use striped backups though.

Striping is the only option you have.
😎
Why don't you want to use it?

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

This is strange to know why not.
I am doing my AG setup (adding dBs) via my automated script and hence creating multiple files would add more complexity.

Coming back to striping issues, there are many 3rd party tools available and there are options to update the count values and the number we mention it goes on with that parallelism value (ofcourse within a range).

Thanks.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

SQL-DBA-01 - Sunday, July 23, 2017 12:05 AM
This is strange to know why not.
I am doing my AG setup (adding dBs) via my automated script and hence creating multiple files would add more complexity.
Coming back to striping issues, there are many 3rd party tools available and there are options to update the count values and the number we mention it goes on with that parallelism value (ofcourse within a range).

Multiple files shouldn't add more complexity, simply use the FILELISTONLY and HEADERONLY options to automate the restore script generation.
😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

You get one thread per file in the DB (I think), and one per backup file, so if you want more threads, you stripe the backup.

Why do you want more threads without more places for them to read/write?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

GilaMonster - Sunday, July 23, 2017 5:57 AM
You get one thread per file in the DB (I think), and one per backup file, so if you want more threads, you stripe the backup.
Why do you want more threads without more places for them to read/write?

DB is roughly around 800 GB. Bkp taking 2.5 hrs and restore as well taking similar number of hrs. Having said that a single thread seems like a slower approach.

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

SQL-DBA-01 - Sunday, July 23, 2017 7:16 AM
GilaMonster - Sunday, July 23, 2017 5:57 AM
You get one thread per file in the DB (I think), and one per backup file, so if you want more threads, you stripe the backup.
Why do you want more threads without more places for them to read/write?
DB is roughly around 800 GB. Bkp taking 2.5 hrs and restore as well taking similar number of hrs. Having said that a single thread seems like a slower approach.

Have you tested that assumption?

Here's a question for you. What limits a backup speed most of the time?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 7

GilaMonster - Sunday, July 23, 2017 8:01 AM
SQL-DBA-01 - Sunday, July 23, 2017 7:16 AM
GilaMonster - Sunday, July 23, 2017 5:57 AM
You get one thread per file in the DB (I think), and one per backup file, so if you want more threads, you stripe the backup.
Why do you want more threads without more places for them to read/write?
DB is roughly around 800 GB. Bkp taking 2.5 hrs and restore as well taking similar number of hrs. Having said that a single thread seems like a slower approach.
Have you tested that assumption?
Here's a question for you. What limits a backup speed most of the time?

The time which I mentioned above is not a projection or assumption. I saw while it was doing./ backup. Next time when I will run this again would note down the backup speed.

Thanks.

Tom Uellner SSCrazy Points: 2954 More actions · Answer 8

SQL-DBA-01 - Sunday, July 23, 2017 8:12 AM
GilaMonster - Sunday, July 23, 2017 8:01 AM
SQL-DBA-01 - Sunday, July 23, 2017 7:16 AM
GilaMonster - Sunday, July 23, 2017 5:57 AM
You get one thread per file in the DB (I think), and one per backup file, so if you want more threads, you stripe the backup.
Why do you want more threads without more places for them to read/write?
DB is roughly around 800 GB. Bkp taking 2.5 hrs and restore as well taking similar number of hrs. Having said that a single thread seems like a slower approach.
Have you tested that assumption?
Here's a question for you. What limits a backup speed most of the time?
The time which I mentioned above is not a projection or assumption. I saw while it was doing./ backup. Next time when I will run this again would note down the backup speed.

I'm not sure how having more threads will help if they all have to queue up to write to a single file. I/O is much slower than processor time.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

If you want your backups to run faster and you're only writing to one physical spindle, then you need to play with the "pipe", not more threads. More threads and "parallel" files will just make things run slower. You need to experiment with the number of buffers dedicated to the backups, packet size, and, if you can, move to compressed backups. The less you have to send over the "pipe", the faster things will be.

You may also have to look into the settings for the switches, routers, and NICs themselves. We've had problems in the past where the backup speeds were slower than a USB 2.0 thumb drive. The network folks figured out some different settings (I don't know what they were) but I've personally run into the previous horror of having auto-negotiate turned on and someone making the mistake of only using half-duplex.

The target disks also play heavily as a part of the "pipe". If they're slow spinners or don't have much in the line of cache, things are going to be slow. If the disks aren't dedicated to the backups, things are going to be slow.

--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)