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 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