Enable support for Resumable index builds in conjunction with @TimeLimit

[XVII]

Strict maintenance windows (such as those imposed by Azure Automation -- 3 hours) can cause maintenance on large tables to fail.

@Timelimit can be used to limit the overall execution time, however it may only get partially through an index before the time limit is reached.

Resumable Online Index Rebuilds along with the "retry" nature of Azure Automation could complete a long running job over several iterations.

• Modernize index maintenance with Resumable Online Index Rebuild

[olahallengren]

Thank you. I have this high on the list of features to add.

[olahallengren]

I have released a new version with support for online resumable index rebuilds.
https://ola.hallengren.com/versions.html

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html#Resumable

It is setting the MAX_DURATION for the index rebuild, if you are using the @TimeLimit parameter.

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@Resumable = 'Y',
@TimeLimit = 3600

[fernandojncarvalho]

Hi olahallengren,

thank you very much for your scripts, they are very useful.
I'm having a problem with the index rebuild operations when using the resumable option = 'Y'. It's a very cool new feature on SQL 2017 and the script uses it very well but, when its used in combination with the TimeLimit it breaks when reaches the max_duration (this is a expected behavior but when the script is inside a job it will report failure and then retry).
In my case I want the job to run for 3 hours and then resume the operations on another day. Because the job fails it will be retried and the actual execution is more than 3 hours...
Is it possible to not break the execution of the script when the index rebuild reaches the max_duration?

I hope you understand my question.
Thank you once again.

[philcart]

@fernandojncarvalho I run the IndexOptimise in two SQL Agent jobs.

In the first job that runs weekly, I pull out all the indexes that need optimisation using the following,
EXEC [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @LogToTable = 'Y', @Execute = 'N';

This captures the Index rebuild/defrag commands in the CommandLog table. After running the IndexOptimise command, the "EndTime" column is set to null for all the records just inserted.

Then on a daily basis, within our maintenance window, the second job just uses a simple cursor to pull out each command that has an EndTime of NULL and run it if the time window hasn't elapsed.
SET NOCOUNT ON; SET QUOTED_IDENTIFIER ON; DECLARE @logID int; DECLARE @sqlCmd nvarchar(max); DECLARE @maxDuration int = 60; DECLARE @startTime datetime = GETDATE(); DECLARE @totalCmds int = (SELECT COUNT(1) FROM [dbo].[CommandLog] WHERE [EndTime] IS NULL); DECLARE @currCount int = 0; DECLARE @cmdSample nvarchar(100); IF @totalCmds > 0 BEGIN -- we have work to do DECLARE cmds CURSOR FAST_FORWARD FOR SELECT [ID],[Command] FROM [dbo].[CommandLog] WHERE [EndTime] IS NULL ORDER BY [ID] DESC OPEN cmds FETCH NEXT FROM cmds INTO @logID, @sqlCmd WHILE (@@FETCH_STATUS = 0 AND (DATEDIFF(MI,@startTime,GETDATE()) < @maxDuration)) BEGIN SET @currCount += 1; SET @cmdSample = LEFT(@sqlCmd,60)+'...'; UPDATE [dbo].[CommandLog] SET [StartTime] = GETDATE() WHERE [ID] = @logID; RAISERROR('Excuting IndexOptimize command for ID:%i (%i of %i) - %s',10,1,@logID, @currCount, @totalCmds,@cmdSample) WITH NOWAIT; EXEC sp_executeSql @command = @sqlCmd; UPDATE [dbo].[CommandLog] SET [EndTime] = GETDATE() WHERE [ID] = @logID; RAISERROR('Command complete for ID:%i (%i of %i) - %s',10,1,@logID, @currCount, @totalCmds,@cmdSample) WITH NOWAIT; FETCH NEXT FROM cmds INTO @logID, @sqlCmd END IF (@currCount < @totalCmds) BEGIN RAISERROR('IndexOptimize finishing due to elapsed time, executed %i commands out of %i',10,1,@currCount, @totalCmds) WITH NOWAIT; END ELSE BEGIN RAISERROR('All commands executed within allowed time window',10,1) WITH NOWAIT; END CLOSE cmds DEALLOCATE cmds END -- we have work to do ELSE BEGIN -- we have work to do RAISERROR('IndexOptimize has nothing to execute',10,1) WITH NOWAIT; END -- we have work to do

[fernandojncarvalho]

Hi, thank you for your suggestion. I have to tweak it a little because i'm rebuilding very large indexes, that sometimes don't fit the duration window of the job, that's why i'm using the @Resumable = 'Y'. In this cases i have to change the command on the commandLog table to do a resume and not to try a new rebuild on the same index on the next execution. But its a really nice start. Thank you very much. philcart <notifications@github.com> escreveu no dia terça, 27/11/2018 à(s) 21:00:

…

@fernandojncarvalho <https://github.com/fernandojncarvalho> I run the IndexOptimise in two SQL Agent jobs. In the first job that runs weekly, I pull out all the indexes that need optimisation using the following, EXEC [dbo].[IndexOptimize] @databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @LogToTable = 'Y', @execute = 'N'; This captures the Index rebuild/defrag commands in the CommandLog table. After running the IndexOptimise command, the "EndTime" column is set to null for all the records just inserted. Then on a daily basis, within our maintenance window, the second job just uses a simple cursor to pull out each command that has an EndTime of NULL and run it if the time window hasn't elapsed. SET NOCOUNT ON; SET QUOTED_IDENTIFIER ON; DECLARE @logid int; DECLARE @sqlcmd nvarchar(max); DECLARE @maxDuration int = 60; DECLARE @starttime datetime = GETDATE(); DECLARE @totalCmds int = (SELECT COUNT(1) FROM [dbo].[CommandLog] WHERE [EndTime] IS NULL); DECLARE @currCount int = 0; DECLARE @cmdSample nvarchar(100); IF @totalCmds > 0 BEGIN -- we have work to do DECLARE cmds CURSOR FAST_FORWARD FOR SELECT [ID],[Command] FROM [dbo].[CommandLog] WHERE [EndTime] IS NULL ORDER BY [ID] DESC OPEN cmds FETCH NEXT FROM cmds INTO @logid, @sqlcmd WHILE (@@FETCH_STATUS = 0 AND ***@***.***,GETDATE()) < @maxDuration)) BEGIN SET @currCount += 1; SET @cmdSample = ***@***.***,60)+'...'; UPDATE [dbo].[CommandLog] SET [StartTime] = GETDATE() WHERE [ID] = @logid; RAISERROR('Excuting IndexOptimize command for ID:%i (%i of %i) - ***@***.***, @currCount, @***@***.***) WITH NOWAIT; EXEC sp_executeSql @command = @sqlcmd; UPDATE [dbo].[CommandLog] SET [EndTime] = GETDATE() WHERE [ID] = @logid; RAISERROR('Command complete for ID:%i (%i of %i) - ***@***.***, @currCount, @***@***.***) WITH NOWAIT; FETCH NEXT FROM cmds INTO @logid, @sqlcmd END IF ***@***.*** < @totalCmds) BEGIN RAISERROR('IndexOptimize finishing due to elapsed time, executed %i commands out of ***@***.***, @totalCmds) WITH NOWAIT; END ELSE BEGIN RAISERROR('All commands executed within allowed time window',10,1) WITH NOWAIT; END CLOSE cmds DEALLOCATE cmds END -- we have work to do ELSE BEGIN -- we have work to do RAISERROR('IndexOptimize has nothing to execute',10,1) WITH NOWAIT; END -- we have work to do — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#32 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/Am36vhrArYAeHyVV2uhv_j_Yu5Slrzblks5uzafvgaJpZM4SxEQe> .