We have all met this DBA. That has a “panic button” for every performance issue. The Index Rebuild and then update statistics.
Dave’s maintenance schedule is aggressive. He rebuilds heavy indexes on his biggest tables every single night. When performance tanks at 2:00 PM on a Tuesday, Dave logs in, finds the biggest table involved, and manually kicks off a full index rebuild right then and there.
He thinks he’s fixing the root cause.
He isn’t. In fact, Dave is likely making things worse. This “brute force” approach is a misguided strategy, and it’s time to understand why.
The Library is Closed, What You’re Actually Doing
Imagine your production database is a massive public library. Thousands of people visit every day. Now, imagine that right in the middle of the busiest time, someone decides to close the library, take every single book off every single shelf, and put them all back in perfect alphabetical order.
Sure, when it’s done, the library looks great. But while it’s happening? No one can check out books. The lines are long, people are frustrated, and the librarian is ready to quit.
That’s what rebuilding indexes during the day does to your database.
An index rebuild isn’t a small task. It’s not a quick defrag. SQL Server drops and recreates the index completely. That process burns CPU, disk, and memory, and it causes blocking and slowdowns across your entire system. You’re shutting down access to your data.
The “False Positive” Fix (It Wasn’t the Fragmentation)
Dave sometimes feels vindicated because, after he hits the rebuild button, performance does improve briefly. But he’s confusing correlation with causation.
When you rebuild an index, you aren’t just defragmenting pages. You are also:
- Updating statistics (with a full scan).
- Invalidating the plan cache for queries using that table.
The real problem was likely outdated statistics causing a bad query plan, or a parameter sniffing issue where SQL Server was “stuck” on a plan optimized for unusual data.
The index rebuild “fixed” it by pure coincidence. It forced SQL Server to get fresh stats and compile a new plan. Dave could have achieved the same result in one second with UPDATE STATISTICS or by clearing a single bad plan, all without the massive resource cost and logging.
He used a sledgehammer to hang a picture frame.
You’re Hiding the Real Problem
If rebuilding indexes every day “fixes” performance, you’re not fixing the root issue, you’re just temporarily resetting it. Frequent manual rebuilds are almost always masking a deeper issue:
- Parameter Sniffing
- A good plan goes bad when a different parameter is used.
- Bad Query Design
- The query is just written inefficiently.
- Missing Indexes
- The query is forced to do a full table scan, and no amount of rebuilding will fix that. Do not just go adding those indexes, they aren’t free and may cause other issues!
- Over-Indexing
- Too many indexes cause excessive writes and fragmentation in the first place.
- Could be bad stats
- Is updating the stats fixing the real issue or just a shot gun approach?
It’s like repainting the library walls every day because people keep getting lost, instead of just fixing the signs that tell them where to go.
When Fragmentation Actually Matters (And When It Doesn’t)
Index fragmentation does matter, but only at certain levels. Modern storage (SSDs) makes this even less of an issue than it was a decade ago.
- Under 10% fragmentation: This is just normal “wear and tear.” Leave it alone. Rebuilding it is a complete waste of resources.
- Over 30% fragmentation: Yes, a REBUILD makes sense. But it should be done off-hours during a scheduled maintenance window, not at 2:00 PM on a Tuesday!
- If you’re rebuilding every day on low fragmentation, you are solving a problem that does not exist.
- You should also not be rebuilding indexes constantly on tables with data that don’t change often.
Stop Firefighting, Start Tuning
Great DBAs don’t just press buttons until the complaints stop; they find the root cause so they never have to press the button at all.
- Stop Blind Rebuilds. Switch to “smart” maintenance scripts (like the industry-standard scripts by Ola Hallengren). These check fragmentation levels first and only act on indexes that actually need it.
- Focus on Statistics. If your stats are the real problem, fix them. Schedule regular, targeted UPDATE STATISTICS jobs. This is far lighter and more effective than a full rebuild.
- If you are already doing this don’t do more, it means you have another issue to solve.
- Diagnose Wait Stats. When performance is bad, check the SQL Server Wait Statistics. Is the server waiting on PAGEIOLATCH (disk), SOS_SCHEDULER_YIELD (CPU), or LCK_M_IX (blocking)? This tells you the real bottleneck.
- Analyze Query Plans. Look at the actual execution plan of the slow query. Is it doing a scan instead of a seek? Is the cardinality estimate way off? Fixing the query is going to be the best solution.
The Take Away Down Push the Panic Button
If rebuilding indexes every day “fixes” your performance problems, it’s not really fixing them, it’s masking the real issue. You’re seeing a temporary boost from refreshed stats and plans, not from defragmentation.
This daily ritual wastes resources, increases downtime risk, and prevents you from addressing the actual root cause.
The real fix is to find out why performance degrades in the first place. Stop rearranging the library every morning and just fix the card catalog.
Leave a Reply