Recommendations for Index Maintenance with AlwaysOn Availability Groups

The supposed "Best Practices" are not best practices and we're never meant to be taken as "Best Practices".  The recommendation for the original author and creator of REORGANIZE and REBUILD is to, and I quote... "take those numbers with a pinch of salt and don’t treat them as absolute."

https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/

I'll also tell you that REORGANIZE does NOT work the way most people think it does.  It does NOT "follow the Fill Factor" in any way ever close to a REBUILD.  Instead, all it does is it tries to shrink the index by combining "some" pages UP TO the Fill Factor.  It does NOT create any Free Space above the Fill Factor.  The bad part about that is that 99.99% of the people in the world use 5 or 10% to 30% LOGICAL fragmentation to identify when REORGANIZE should be done and they do a REBUILD only if the LOGICAL fragmentation goes over 30%.  Since REORGANIZE DOES remove LOGICAL fragmentation, it very quickly gets "stuck" on indexes and they frequently never be rebuilt ever again.  That means that no Free Space is created above the Fill Factor.  That means that virtually every index you lowered the Fill Factor to prevent fragmentation is NOT working correctly and the fragmentation such an action was supposed to prevent is now perpetual and actually gets worse after each REORGANIZE because all the critical space above the Fill Factor is permanently full because REORGANIZE removes free space below the Fill Factor and compresses pages.

The phenomena prevents perfect wide or random distribution indexes from working correctly.  For example, a correctly maintained Random GUID index (and I have the proof in multiple repeatable tests) can withstand 100,000 per day inserts for 58 days (that's a total of 5.8 MILLION rows, folks!) with less than 1% logical fragmentation and absolutely no index maintenance during that entire period.  The way I did that was to STOP using REORGANIZE.  In a parallel test, REORGANIZE cause fairly massive and perpetual page splits all day every day during that same 58 day period along with fragmentation going over 5% and needing another REORGANIZE every 4 days.

During that same testing, it was proven that it's actually better to do NO index maintenance than it is to ever use REORGANIZE.  The only 2 places that you should use reorganize is to compress LOBs (it even sucks at that and needs a rebuild right afterwards) and to remove physical fragmentation on an Insert Hot Spot cause by an Insert/Update pattern on ever increasing indexes.  And, other fragmenting indexes are also affected in a similar manner... not just Random GUIDs.

For anyone interested (and, no.... this is NOT meant to be any form of spam), I'm giving the hour long version of my 2.5 hour presentation on the subject on the 28th of July at "EightKB".  You can register to attend at the following URL...

https://eightkb.online/

--Jeff Moden