The #1 Thing to Never Do to Fix a Performance Problem

photo-1443159805125-50ae78d2d00dNever, ever, disable backups to fix a performance problem.

If you’re not having a performance crisis, you might look at that sentence and say, “Wow, that’s super dumb! Who would ever do that?”

Well, a lot of people, actually. It’s pretty common.

When performance gets really bad, people get confused. Are all the backups running making it worse? It’s very common for people to think that disabling backups or making them less frequent might lessen the load on the server. It’s worth testing, right?

It’s not. 

It’s absolutely possible that backups could be part of a performance problem. You might have hit an IO situation or a bug where backups are exacerbating the issue. But backups exist to protect against data loss. And data loss is worse than poor performance in most environments. (If you’re in the rare environment where data loss isn’t an issue, why were you doing backups anyway?)

If you’re in a performance crisis, do all these things before you ever get close to disabling your backups:

  1. Open a ticket with Microsoft
  2. Have your DBA team document their process for diagnosing the cause of the problem — what they’ve done so far, current findings, and steps still to be taken
  3. Hire an independent consultant
  4. Get management to sign off that additional data loss is acceptable. (They won’t want to, but it’ll get you the budget to do one of the items before this in the list.)
Previous Post
Warning Signs That You Have a SQL Server Backup Problem
Next Post
Three Inspiring Articles to Read This Week

Related Posts

15 Comments. Leave new

  • In general I agree but swapping from full backups to differential backups can boost performance and give significant savings
    (£ / $ to you) while still fully protecting the data – worth considering.

    Reply
    • If differentials work as part of the restore sequence and you make sure nothing happens to the fulls, I love diffs!

      Reply
    • It could also make it a whole lot worse because differentials are cumulative until the next full backup is taken. If you’re having a performance problem because of backups, it may be better to simply increase the frequency of the Transaction Log.

      p.s. Hi Kendra! Love your blog!!!

      Reply
      • surely the worst case is every extent is changed so your diff == your full backup, therefore I can´t see that as “a whole lot worse”. Most likely is that somewhere between 1 and 10% of you data changes daily and therefore diff backups are much much smaller than full backups. YMMV

        Reply
        • Diffs can be much slower than fulls. Years ago I had a multi-TB database that did fulls once a weekend and diffs nightly. The full got cancelled one weekend and never re-run. I found an extremely slow running diff that had taken larger than the full ever did.

          Now, was it slower because it passed the end of the maintenance window and other stuff was running, or was it because storage was slower, etc? I didn’t stop to test. We knew we didn’t want to restore a full plus that diff so we had to stop the backup, run a full out of band, and change the jobs to add logic to check when the last full was and proceed accordingly so it never happened again.

          Reply
          • @Stephen,

            It would be nice if “only” every extent were changed. But, we have tables that take inputs from multiple sources and so each extent and all the related indexes on many of a largest tables can be changed a couple of dozen times each day. A Diff backup frequently outstrips the full backup in just about 4 hours and that doesn’t include any of the index maintenance.

          • Jeff

            Now we are off on a tangent but your description of how a differential backup works is misleading so …

            The indexes are also stored on extents, unlike transaction log backups where we store each and every change that has been logged in a differential backup, we backup a snapshot of any extent that has changed as tracked in the DCM pages.

            https://msdn.microsoft.com/en-us/library/ms175526(v=sql.110).aspx

            So it really boils down to the percentage of extents that get touch within a 24 hour period – & this can differ wildly.

          • Stephen,

            Ah, you’re right. My mistake. It wasn’t misleading… it was actually wrong. Only the latest value of any extent is stored which means that, although the Dif can approach or even equal the size of a Full Backup, it won’t ever get larger than a Full Backup. Thank you for the clarification.

          • Jeff’s original point that a large diff can be a whole lot worse than a full backup is still quite valid, regardless of the tangent, because of restore time. Restoring a full plus a large diff will be very slow. Unless you have a very high recovery point objective, that’s a big downside to large diff backups.

          • Fully agree, if you´re in that situation it´s probably time to invest a bit in your hardware setup.

          • Or invest a bit of time in setting up read-only file groups, possible with some form of partitioning, so that you don’t have to backup that which will not change (individual months of data for “Audit” and other “WORM” style tables, which are frequently the largest tables in any database). Done properly, such a thing will also allow for a very quick “Get back in business” restore followed by Piece-Meal restores to bring the legacy data back online. It will also allow online Piece-Meal restores if filegroup(s) goes South.

          • I agree with Kendra that a large diff is not the thing you want. Nonetheless, diff backups can really be effective.

            Usually I do full backups on weekends and diff backups every weekday night. But the my pre-backup script has built in some intelligence borrowed from Paul S. Randal’s excellent script to estimate the size a differential Backup would be.

            http://www.sqlskills.com/blogs/paul/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup/

            So weekdays it does a full backup instead of a diff backup in case the estimate is bigger than 60% of the used data size.

            With this method I try to get the best out of the two backup methods and save some resources 🙂

          • I forgot about that estimate code that Paul wrote! Cool link, thanks.

  • Don’t agree that you should NEVER do it. It’s something to examine – if it helps, you have another clue about where performance problems might be. Certainly, you should not LEAVE them disabled, but I see nothing wrong with turning them off just to see if it has an effect.

    Reply
    • I’d lean towards the NEVER; maybe reschedule them; if app A is slow every night at 10pm, and 10pm is when we do the backups, change backups to 2am, if problem goes away decide whether to keep the change, or going back but looking at striped backups, or full + diffs, or buying those SSD’s…

      And always test those backups, it’s not backups that matter, it’s restores…

      Reply

Leave a Reply to Pete Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu