Thoughts on Automatic Index Compaction

John Deardurff shares some thoughts:

Anyone who has managed SQL Server for a while probably recognizes the traditional 5% reorganize or 30% rebuild approach. But those Index Fragmentation Thresholds were completely made up. Primarily as they were simple values to baseline that made it easy to automate. But today we need to ask whether fragmentation percentage is really the best measurement for deciding when an index needs maintenance.

As storage has changed, Microsoft guidance around index maintenance has changed. The question should no longer be; How fragmented is my index? A better question is: How efficiently are the pages being used, and how much work is needed to access my data?

Click through for a demo of what automatic index compaction can do in Azure SQL Database. The two important notes I have are as follows. First, as John points out, automatic index compaction does not update statistics, so you’d still need to take care of that. Second, I don’t think there’s ever a good use case for REORGANIZE.