May I suggest two small changes and hear what you think of them?
First, it makes sense to me to go for the low hanging fruit first and start with the smaller tables as that will quickly give you at least some improvement. If your database is in a bad state, then rebuilding the biggest indexes can take too long for the whole maintenance to finish.
For this I've added ORDER BY record_count ASC into the /* create queue for indexes */ and /* create queue for heaps */ selects and ORDER BY [rows] ASC into the one for /* create queue for update stats */.
Second, I think it makes sense to add a "soft" timeout into the script. If you are nearing the limit imposed on the procedure from outside (eg. the 3 hours timeout for Azure runbooks), then it doesn't make sense to waste effort starting another rebuild and it's easy to check how long had the script been running before starting another task.
I added argument @timeoutMins int = null to my version,
declare @TimeoutTime datetime2 = DateAdd(minute, @timeoutMins, @OperationTime);
under the @OperationTime declaration,
set @msg = 'set timeoutMins = ' + CASE WHEN @timeoutMins is null THEN 'NULL' ELSE cast(@timeoutMins as varchar(10)) END;
raiserror(@msg,0,0)
IF (@TimeoutTime is not null) BEGIN
SET @msg = ' It''s ' + convert(varchar(30),@OperationTime,120) + ' and I promise not to start anything new after ' + convert(varchar(30),@TimeoutTime,120) + ' GMT.';
raiserror(@msg,0,0) with nowait;
if @LogToTable=1 insert into AzureSQLMaintenanceLog values(@OperationTime,null,null,sysdatetime(),null,@msg)
END
at the end of the /* Write operation parameters */ section and
IF (@TimeoutTime is not null and @TimeoutTime < SYSDATETIME()) BEGIN
raiserror('I ran too long. I''m not starting anything new.',0,0) with nowait
if @LogToTable=1 insert into AzureSQLMaintenanceLog values(@OperationTime,@SQLCMD,@ExtraInfo,sysdatetime(),null,'I ran too long. I''m not starting anything more.')
return;
END
into the body of the while exists(select * from AzureSQLMaintenanceCMDQueue where ID>0) loop. This way I can ask the procedure not to start anything new a minute before it gets killed anyway.
Third, is there any reason NOT to have a version tweaked to reside in master of an ordinary SQL Server and thus accessible from all databases on the server? Losing the queue table in case of a server crash is irrelevant and it's not likely to grow too large either.