Parameters may improve SQL Backup Performance in SQL Server 2008

Question

Parameters may improve SQL Backup Performance in SQL Server 2008

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
September 17, 2014 at 10:43 pm

#296316

Comments posted to this topic are about the item Parameters may improve SQL Backup Performance in SQL Server 2008
Thanks.
September 18, 2014 at 5:00 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

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

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 1

+1

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 3

Iulian -207023

SSCertifiable

Points: 7639

September 18, 2014 at 12:51 am

#1746287

nice one , thank you for this qotd

Iulian

Hany Helmy SSChampion Points: 13921 More actions · Answer 4

Good question, thanx, note that BlockSize option typically affects performance only when writing to tape devices not to disk.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

marks_262 SSCommitted Points: 1716 More actions · Answer 5

Good question.

How much can the increase the backup performance by?

SqlMel SSCrazy Points: 2901 More actions · Answer 6

This was a very good question.

Made me think about options that I would normally not consider.

Oddly enough I had a brain fart while answering and checked the "Log Consistency Checking" answer and hit submit. When I realized what I had done, it was too late.:crying:

I guess today is Backwards Day for me! :hehe:

---------------
Mel. 😎

SqlMel SSCrazy Points: 2901 More actions · Answer 7

marks_262 (9/18/2014)
Good question.
How much can the increase the backup performance by?

I guess it would depend on the situation.

For example, as per BOL, the Blocksize default is 65536KB for tape devices and 512KB for others. They recommend a blocksize of 2048 if you are planning to use a CD-ROM to copy the backup to and restore from it.

I have personally not used these features, guess I would have to play with it for a while to know for sure just how helpful it can be.

This is really not a problem that I have in my environment but it's good to know we have options.

Edit: To present default blocksize for tapes.

---------------
Mel. 😎

briankwartler Ten Centuries Points: 1332 More actions · Answer 8

Just to clarify, the default block size is 65536 (64 K) bytes for tape devices and 512 bytes otherwise. According to the referred MSDN page, the BLOCKSIZE option is typically unnecessary because BACKUP automatically selects a block size that is appropriate to the device.

- Brian

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

To be a bit of a pedantic, but when typed out as-is, two of the options are incorrect. "Buffer Count" should be a single word "BufferCount".

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Interesting question, but I hope it doesn't encourage anyone to use these options without extreme care. Letting SQL Server pick the values according to the number and type of output devices, size of the database, number and type of the devices holding the database, and amount of store available provides less scope for error.

Increasing BUFFERCOUNT may be useful when writing to striped units (RAID 10, RAID 5) with more than about 4 discs in the array (but won't unless the channel from the CPU to the raid controller has enough bandwidth for this to be useful), but if it is set too high backup may fail because it runs out of store. I'm not sure what that does to SQL 2008, but in SQL 2012 the instance stops working, the service can't be stopped by any of the usual mechanisms, and getting things back to normal requires using task manager to kill the process (see connect item).

If a raid controller has an optimum transfer size it may make sense to use that as MAXTRANSFERSIZE for that device, but if that size is too small the increase in admin overhead in NTFS may outweigh the gain in the raid controller. If the output device will allow it may make sense to increase MAXTRANSFERSIZE to 4MB, but as with BUFFERCOUNT that increases the amount of store required for buffers and may cause a failure.

Tom

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 11

Hany Helmy (9/18/2014)
Good question, thanx, note that BlockSize option typically affects performance only when writing to tape devices not to disk.

yes, I ignored that one too 🙂

Cheers,

Iulian

Luis Cazares SSC Guru Points: 183698 More actions · Answer 12

I was expecting that compression would be one of the answers.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ozzmodiar Ten Centuries Points: 1315 More actions · Answer 13

Good question although I would disagree with the solution if only for the incorrect terminology used.

Buffer Count (Should be BUFFERCOUNT) is going to exponentially increase the amount of cpu used. This answer is correct; although I truly didn't want to select it because of the way it was written, I assumed the question was just as much about syntax as it was about performance.

MAXTRANSFERSIZE, this one is a no-brainer. The way it is written in the question though (Correctly) really makes you question the misspelled BUFFERCOUNT.

BLOCKSIZE is something that is entirely dependent on the media you are writing too, it is very rare to manually change this option. It is best practice to allow the engine to determine this on it's own. Am I incorrect on this?

I can't understand why compression was left off of the list. There is no single option that will decrease the backup execution time as much as this will.

I love these questions, but I wish they were reviewed a little closer for the grey wording that exists in so many of them!

Thanks!