Parameters may improve SQL Backup Performance in SQL Server 2008

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

    Comments posted to this topic are about the item Parameters may improve SQL Backup Performance in SQL Server 2008

    Thanks.

  • Vimal Lohani

    SSCommitted

    Points: 1650

    +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: 258965

    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: 7509

    nice one , thank you for this qotd

    Iulian

  • Hany Helmy

    SSChampion

    Points: 13488

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

  • This was removed by the editor as SPAM

  • marks_262

    SSCommitted

    Points: 1716

    Good question.

    How much can the increase the backup performance by?

  • SqlMel

    SSCrazy

    Points: 2891

    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: 2891

    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

    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: 281252

    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

    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: 7509

    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: 183637

    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: 1295

    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!

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

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