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
This is just to get you thinking that maybe there’s an easier way to do things than extremely resource-intensive index rebuilds.