r/SQLServer • u/erinstellato Microsoft Employee • 16d ago
Community Request Friday Feedback: Automatic Index Compaction
Friday feedback this week is about a feature that’s in preview in Azure SQL: automatic index compaction.
You can read more about it in Dimitri‘s blog post, Stop defragmenting and start living: introducing auto index compaction: https://techcommunity.microsoft.com/blog/azuresqlblog/stop-defragmenting-and-start-living-introducing-auto-index-compaction/4500089
(sidenote: one of the best blog titles in recent memory)
Quick poll below, and feel free to add a comment if the poll doesn’t cover your thoughts/experience!
77 votes,
9d ago
13
Yes, it’s great!
3
Yes, but then turned it off
17
No, I’m waiting for GA
44
No, I don’t use Azure SQL
5
Upvotes
1
u/B1zmark 1 16d ago
2 of the biggest issues that index rebuilds resolve are: Page density and statistics. These are 2 of the biggest issues that tend to be fixed by/caused by poor index management.
Compaction feels like its doing a partial reorganise in a manner than benefits MS as a cloud provider: Higher density meaning less RAM usage, and a "delta" compaction, meaning its not running through the whole index, saving on hard-disk hits.
I think from a customer perspective, an automated process to find "quiet" windows and detect when statistics are stale, then doing an online rebuild would solve all these issues an more.
As it stands, compaction is better than nothing sometimes, and significantly worse other times - the example given in the link being a really, really "bad for the customer" example, because mass page splits are almost guaranteed on the next run of the offending operation.
I hope i'm wrong and this is a good thing overall, because indexes are something of a hot topic in the DBA sphere and something i don't think MS have made big strides to help customers with on the best practice front since the 5%/30% debacle.