MicrosoftI have little knowledge of Azure so I have to ask... are ONLINE rebuilds allowed in Azure (Synapse)?
As for auto-updates of stats, even in SQL Server, they're seriously overrated if you're using ever-increasing index keys such as dates, Identity, and Sequence columns. I check the row modification counter for each stat to evaluate what needs to be done for each stat.
And, yeah! Nice to see that someone understands that the best way to deal with fragmentation is to avoid it. As you say, that does take a bit of study for each index, especially the clustered indexes. I have some experimental code that will create a low impact set-based after trigger that checks variable width columns for expansive updates to help me figure out which columns actually need work and might have a workaround.
On that subject, there's a really nasty source of fragmentation that a whole lot of people aren't aware of and that would be the fragmentation of when a previously fixed width column is updated from NULL to something and either row or page compression is active on the index. For Ints, any value being updated from NULL to any value over 255 causes and any Date being updated will cause an "ExpAnsive" update, which is one of the two primary causes for page-splits and the resulting logical (out of order pages) and physical (low page density) fragmentation.
Of course, it sounds like you're already well aware of that and so I'm just saying that for others that may be reading this.
The emphasis is really on REORGANIZE. It's frequently responsible for perpetuating fragmentation all day every day because it doesn't actually work the way people think it does (like I said in one of my previous posts on this thread).
Either way, thank you for the post-back. I appreciate it.
