How to maintain Azure SQL Indexes and Statistics

Hi cwalshe

rebuilding and reorganizing indexes as well as updating statistics with full scan is an intensive IO operation.

this can explain the high resource usage you can see. 

however, if this ran when the system is idle - this is good, and it uses as much resource as it can to finish the maintenance task. 

this is the reason for why we recommend to run the maintenance during night / weekend / system idle time. 

if that;s not possible we should be more granular with the maintenance or maybe use different time or different service tier. 

note that even if you see high resource usage that does not necessarily means that your application cannot work at the same time. 

it really depend on the workload. 

HTH, 

Yochanan. 

Hi Sergey_F 

There is no magic formula, this will be decided based on tests only. 

it worth to know that the script is running an analysis at first step and then starts by maintaining the database objects. 

if you stop that and run it again after a while, it will do the assessment again and if an object already maintained and there is no need to re-run the maintenance it will just skip that resource. 

so, it is safe to stop it when your maintenance window ends and restart whenever this is possible. 

For indexed we have the resumable operation, unfortunately this maintenance script is not using that ability yet. 

HTH,

Yochanan. 

Hi Peter-MSFT - Thank you for you feedback, luckily for us fill factor is kept on rebuild

When an option is not explicitly specified, the current setting is applied. For example, if a FILLFACTOR setting is not specified in the REBUILD clause, the fill factor value stored in the system catalog will be used during the rebuild process. To view the current index option settings, use sys.indexes.

Source: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15#remarks

Hi Yochanan_Rachamim 

Thanks for your work! The updated script works perfectly!

Running the latest version, it oddly is trying to do an index rebuild on a Table-valued function.

The error in the log table is:  FAILED : 1914Index cannot be created on object 'dbo.fnTableDate' because the object is not a user table or view.

We have a few Table Valued functions in which return an "at"TableName table variable with a PRIMARY KEY defined in it. This script will try to include these, and then fails...

"FAILED : 1914Index cannot be created on object 'dbo.functionnamehere' because the object is not a user table or view."

The supposed "Best Practices" are not best practices and we're never meant to be taken as "Best Practices". The recommendation of the original author of the MS docs 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

Hi,

thanks for creating this utility, it's very useful.

Looking at the code, I can see it says "Remove statistics if it is handled by index rebuild / reorginize...", please note that an index reorg does not update the statistics e.g. https://dba.stackexchange.com/questions/48991/does-a-re-index-update-statistics

thanks

Ian

AlexOnData 

Seriously... look at the warning I posted earlier in this post about the use of REORGANIZE.  You may actually be perpetuating fragmentation.