It’s hard to believe that this is still a lively debate, given how far technology has come since the original conversations around if and when fragmentation is a problem were a thing, but here we are in 2020 for the third year straight.
I will give people some credit where it’s due, I’ve seen index maintenance habits change a bit over the years:
- Reducing frequency to once a week or once a month from every night
- Bumping the thresholds that they reorg and rebuild way higher than 5% and 30%, like 50% and 80%
- Abandoning it all together when using AGs or other data synchronization technologies
- Realizing how powerful and flexible statistics updates are, especially when using Ola’s scripts
It’s a good start, but people still ascribe far too many benefits to doing it. Rather than rehash everything I’ve ever said about it, I’m gonna post a video of Erin Stellato (b|t) and I discussing the pros, cons, whens, wheres, whys, and hows in this video:
Thanks for reading (and watching)!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- Software Vendor Mistakes With SQL Server: Indexing #Temp Tables Incorrectly
- Software Vendor Mistakes With SQL Server: Lowering Fill Factor For Every Index
- Software Vendor Mistakes With SQL Server: Not Using Filtered Indexes Or Indexed Views
- Software Vendor Mistakes With SQL Server: Designing Nonclustered Indexes Poorly