Index Maintenance Madness

When we look at a new server, we check out their database maintenance jobs, making sure their server is in a safe place before we dig into their pain points. We verify if their backups and DBCC CHECKDB jobs can meet their RPO/RTO goals. We also check out their index maintenance jobs, if any.

When I see that they are rebuilding/reorganizing indexes daily, I ask, “What are you trying to solve with running index maintenance so frequently?”

They usually respond with one of these answers:

  • I’m not sure. Someone else set that up.
  • To fix fragmentation.
  • If we don’t do it, performance becomes horrible.

How do I respond to each of those?

Someone else set it up

Fair enough, but let’s make some adjustments.

  • Change it to be weekly or even less frequently.
  • Use Ola Hallengren‘s IndexOptimize but don’t use his defaults. I suggest 50% for REORGANIZE, 80% or even 90% for REBUILD.
  • Setup a new job to run update stats via IndexOptimize daily. See example D on the IndexOptimize page (link above).

To fix fragmentation

See above changes, but then we also briefly discuss why rebuilding/reorganizing indexes isn’t necessary all that often. If it’s a recent client, I’ll tell them to watch Brent’s video.

For performance reasons

Now this is the one that I love to discuss.

I’ve only had a handful of clients who insisted it was necessary to avoid huge performance problems. Without even looking at the rest of the server, I am confident the issue is Parameter Sniffing or out-of-date statistics. When you rebuild an index, you get updated statistics for that index and any execution plan that references that index gets wiped from the plan cache. But that’s a very expensive way to do those two tasks! If the issue is with out-of-date statistics, then update statistics daily. If the issue is with Parameter Sniffing, then you’ve got more work to do.

I’ve lost hundreds of hours to troubleshooting Parameter Sniffing issues over the span of my career: figuring out which queries are the culprits, writing custom code to detect an issue and analyzing the queries to determine if a code change is needed, a covering indexing is needed or if a Parameter Sniffing workaround is needed.

There isn’t just one easy answer

Every query that is having issues needs to be investigated to determine what can be done to either work around the issue or fix it. Back in SQL Server 2005 and earlier, we had very limited workaround options:

  • Index hints
  • sp_recompile/WITH RECOMPILE
  • DBCC FREEPROCCACHE
  • Stored procedure branching
  • Using local variables inside of stored procedures instead of the input parameters

With newer versions, we can get fancier:

  • OPTION (OPTIMIZE FOR (@param1 = 12345)) at the query level
  • If we can’t modify the queries, we can use a plan guide (2012+)
  • With SQL Server 2016+, we’ve got the Query Store

This blog post isn’t going into detail about Parameter Sniffing

We’ve got tons of blog posts and even a dedicated page on that topic already.

This is just to get you thinking that maybe there’s an easier way to do things than extremely resource-intensive index rebuilds.

Previous Post
First Responder Kit Release: Treating Object References Like Object Instances, Man
Next Post
What Should We Change About the Data Professional Salary Survey?

30 Comments. Leave new

  • We generally have an index rebuild job weekly.
    However, if you have a 12-14 hour maintenance window every day, is there really a real world down side to rebuilding every night?
    Yes, it’s overkill, but is it doing any harm?

    • If I had that large of a window every night, I’d probably do all the things. The servers I’ve worked on in the past and the clients I’ve worked with don’t have those kinds of idle windows. Plus lots of these clients are experiencing heavy blocking, and the index maintenance is a big culprit. But one downside is if you have SSDs. You’ll wear them out faster.

  • In our 2016 SP1 data warehouse we rebuild daily using Ola’s scripts and Brent’s suggested defaults so we only rebuild the indexes that need it. Plus we use Ola’s scripts to do a ModifiedOnly statistics update after our overnight data load.

    • I like what you’ve setup except for the schedule. I’d do it less frequently unless you have a large window of very low activity every night. Most systems don’t have that kind of window.

      • We’re lucky that we have a 6 hour maintenance window each night so we limit Ola’s script to only run for 5hrs (allowing a buffer). And also do our diff backups in this time.

  • You forgot a reason:

    Microsoft support won’t move your System Center case forward unless you’re doing daily defrags, even though that has nothing to do with the issue.

  • Yeah, generally one of the first things I do on a new client’s systems is index LESS often.

    And one client, their biggest issue was never the indices, it was bad parameter sniffing. Of course they wouldn’t upgrade from SQL 2005.

  • Andrej Kuklin
    December 6, 2017 6:13 am

    You can use plan guides starting from SQL Server 2005 (with some improvements in SQL Server 2008). They can be a lifesaver for processing queries generated by SSAS which you can’t modify or hint otherwise.

  • David Solomon
    December 6, 2017 8:52 am

    Nice concise write-up, but I’d also point out that certain indexes re-frag so fast it’s hardly worth ever defragging.

  • Good Article! 🙂
    “When I see that they are rebuilding/reorganizing indexes daily, I ask, “What are you trying to solve with running index maintenance so frequently?””
    It’s always good to ask the question and make recommendations but I like to take the stance that if it isn’t causing (or related to) an issue then potentially changing it may introduce issues. And always make sure any change like this is thoroughly tested and analysed in pre-production and non-client facing systems before hitting production.

    • Usually it is causing issues. But even if it’s not, we still dig into what to do instead. So with the clients that I see doing this and specify “for performance reasons”, we go over Parameter Sniffing: what it is, how to find the problematic queries, how to test and what are the workarounds. This blog was written the week before I had a client that was doing index maintenance HOURLY. That’s a first for me. And they are using Standard Edition. YIKES.

  • Hi Tara,

    I have to ask, what is the basis of your recommendation to use a 50% Fill Factor when you REORGANIZE an index?

    • Hey Jeff,

      Tara’s moving and without internet! The line you’re talking about

      50% for REORGANIZE, 80% or even 90% for REBUILD

      Is about the fragmentation level of the indexes before you reorg or rebuild, not the fill factor you should set them to.

      Thanks!

      • Whew!!! Thanks, Erik. I thought Tara had eaten a bad egg or something. 😉
        As for rebuilding at 80 or 90% fragmentation, I’ll say that the damage (slowdowns because of the wrong kind of page splits) has already been done by then. If you’re going to rebuild indexes (and I’m with you folks… I recommend that you don’t rebuild indexes in 98% of tables {there’s always an exception}), then the only reason to do a rebuild is to totally avoid page splits. There’s a way to do it but I’m not yet done with my “bong water” experiments and so I’m also not yet ready to make a recommendation.

        As a bit of a sidebar, the 17th of January, 2018, will be the two year anniversary of the last time I rebuilt indexes with one exception and that’s a table that is heavily inserted into and then nearly as heavily deleted from.

        Hum a rock at Brent and tell him I said “Damn good bong water”! 😉 He’ll understand the meaning.

        • Between your beer popsicles and Brent’s bong water, we’re all in for a good time.

          Is the scenario you’re looking at similar to this one?

          Thanks Jeff!

          • Exactly. As that article states, the problem is that not all the rows in pages are deleted and so the pages persist in the CI at roughly 50% physical fragmentation (page density) and the NCI’s (some of which are quite wide thanks to the unconcerned use of DTA and the like) persist with as little as 5% page density.

            For those reading this, it’s not just DELETEs that kill you on this. As the article states, changing the content of a Status column, even as a second key column, can cause some pretty severe data movement that leaves pages almost empty especially if the first key column has a relatively low cardinality, such as a ClientID column.

            Considering that even a GUID used as the first column of a CI settles out with a “Natural Fill Factor” of 69.5% (almost a perfect 70% for such an animal), it’s pretty easy to find tables that suffer “Perpetual Physical Fragmentation” by checking the “avg_page_space_used_in_percent ” column of sys.dm_db_index_physical_stats for physical fragmentation rather than usually misleading “avg_fragmentation_in_percent”, which depicts logical fragmentation.

            I guess that’s what causes people difficulty in buying in on the “no defrag” recommendation. They lump both types of fragmentation (misnamed as “logical” and “physical” fragmentation) under a single label. When they have a couple of tables that is of the nature that we’re speaking about, they think that justifies defragging everything instead of making the realization that they’re actually measuring the wrong type of fragmentation to determine when a table or NCI’s actually need to be defragged.

            Another part of the problem is that a lot of people will only believe what they can see and I can’t blame them there. If you can’t reliably demonstrate and measure the effects and then turn your test jig over to someone else and then they can repeat the same experiment with the same results, then you’ve invented “cold fusion”. I’m in the process of building such test jigs that simulate a year of doing (at multiple levels) and not doing defragmentation with measurements for every hour. As you can well imagine, the tests take a bit of time to run so I’m also writing the code to automate them so the next poor slob that runs them doesn’t have to have a full supply of beer popsicles and bong water at the ready to suffer through extended runs. 😉

            The other thing that I’m discovering is that when people DO index maintenance as some form of defragmentation, almost all of them are waiting until massive page splits begin (the avoidance of which is one of the primary reasons that people do defrags) instead of catching things early enough to avoid page splits at all simply because they’re using the wrong methods to detect fragmentation.

            To be sure, Brent’s original “bong water” suggestion and Kendra’s follow ups on statistics has solved a lot of problems for me and the systems I manage. You good folks being joined at the hip on the subject certainly helps with changing the public perception on the subject. Thanks for what all of you do.

  • Ola’s index maintenance scripts also have a TimeLimit which is very handy when you have a limited window. Will everything get defragged? Maybe not that night. Should happen another night.

    Also, I am not as worried about performance per say, as memory pressure. Empty pages go into memory and unfortunately my servers do not have terabytes of ram to just load the entire db into memory. 🙂

    • Agreed… nearly empty pages in memory IS a good reason to defrag. Oddly enough, fixing those things will also help performance for multiple reasons.

  • I had one scenario not discussed here which was fixed (accidentally) by doing index rebuilds. One side effect of an index rebuild is it updates statistics at 100% sampling. We found a pattern of a query taking a bad plan after the normal stats update occurred. Their normal stats update would sample at 20%. As a result the query had less accurate stats. The index rebuild was an accidental fix for something that was really stats related. We eventually fixed this with a plan guide though.

    • Why didn’t you just rebuild the stats more often? “A” plan guide only fixes one thing and not permanently.

  • Hi Brent-

    I might be slightly drifting away from this topic, but my question is still related to index maintenance.
    I have a client who has few databases in a server which are closely 2TBs each. They have never run any DBCC CheckDB(Scary, I know) and never has run any index maintenance or stats update in their environment.
    Most of these databases have auto update stats turned on which fairly does a decent job for their requirement.

    These servers are very critical, and it can never have any downtime whatsoever for any sort of maintenance. They only have an hour once in a while allocated for any maintenance work. So whatever has to happen, it has to happen only in that hour. And they do see some performance bottleneck here and there and do complain about slowness at times. Often when I look at certain queries they have a lot of outdated stats too…

    My question as below –

    1. How effectively do I manage indexes for such large databases?
    2. If I ever have to rebuild indexes and update the stats, what sampling rate or fragmentation percentage should I choose?
    3. And not running DBCC CheckDB scares me. What is the best advice on this to run integrity checks on these databases.

    I have tried to run some in the past and it starts blocking processes. They are running SQL 2017 Ent edition.

    Thank you very much for your feedback in advance.

    Much appreciated.

    • Pooh – I do wish I could give everyone advice for their clients for free, but…your best bet is probably to start attending my training classes.

    • Better yet, have them hire Brent! It sounds to me like they’ve got more problems on their hands than index and stats maintenance. It’ll be well worth it, especially if the box is as critical as you say.

  • Fair enough Brent….I just love your way of teaching and do follow you. Will look at attending your class sometime .. And thanks for getting back 🙂

  • Robert G Godbold
    September 25, 2019 2:43 pm

    After reading all of this stuff on index maintenance, I’ve finally settled on using dm_db_index_physical_stats to implement 2 conditions in my automated maintenance task: “REBUILD if external-frag > 85% and space-used {magic-number} MB”.

    So if the index is “too big” (I think it’ll take too long, for my environment), then I won’t rebuild during the task; I’ll have the task email me so I can deal with it manually. And if the index isn’t due for a rebuild, then I only reorg when I think it’s wasting too much space due to “internal/logical fragmentation”.

    I figure that if I’ve got a lot of db-catalogs, each with various indexes with avg 250-750MB of empty space, I could easily be wasting 10’s of GB of RAM and disk space; it’s all about the aggregates. But if I have some indexes with 5% density that only take up ~10MB (~1250 page_count), I almost certainly don’t care.

Menu
{"cart_token":"","hash":"","cart_data":""}