Description of the feature
It can be a new feature, or a change to an existing feature
Hi Ola
Thanks for your scripts, we use them a lot in all our environments
we are using Index maintenance script, as below
when we set the option to @Resumable = 'Y', along with @WaitAtLowPriorityMaxDuration = 2,
@WaitAtLowPriorityAbortAfterWait='SELF'
the index rebuild failed with a dead lock as it is chosen as the victim
we had to check the sys.index_resumable_operations for any entries and then run the Alter Index with resume for the paused index
if the table has many indexes, this check will take time for every pause.
if we use the same option with 'ALL INDEXES', and if the job is paused or failed, it will be tedious task to do the additional check and resume the index
is there any updates in this regard, like the below
Check before every rebuild if there is an entry in sys.index_resumable_operations
If there is an entry, run the alter index command as RESUME (e.g. ALTER index index_name ON table_name RESUME )
If rebuild fails due to deadlock, wait for nnnn milliseconds and retry 3 times. If it still fails, move on to rebuild the next index or end the job if this is the last one
if any such feature is coming up soon, it will be a life saver
Description of the feature It can be a new feature, or a change to an existing feature Hi Ola Thanks for your scripts, we use them a lot in all our environments we are using Index maintenance script, as below
EXECUTE [dbo].[IndexOptimize] @Databases = 'ABCD', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 40, @FragmentationLevel2 = 60, @OnlyModifiedStatistics= 'Y', @Indexes= 'ABCD.dbo.Table1', --@Resumable = 'Y', @MaxDOP = 4, @WaitAtLowPriorityMaxDuration = 2, @WaitAtLowPriorityAbortAfterWait='SELF', @LogToTable = 'Y'
when we set the option to @Resumable = 'Y', along with @WaitAtLowPriorityMaxDuration = 2, @WaitAtLowPriorityAbortAfterWait='SELF'
the index rebuild failed with a dead lock as it is chosen as the victim we had to check the sys.index_resumable_operations for any entries and then run the Alter Index with resume for the paused index if the table has many indexes, this check will take time for every pause. if we use the same option with 'ALL INDEXES', and if the job is paused or failed, it will be tedious task to do the additional check and resume the index
is there any updates in this regard, like the below Check before every rebuild if there is an entry in sys.index_resumable_operations If there is an entry, run the alter index command as RESUME (e.g. ALTER index index_name ON table_name RESUME ) If rebuild fails due to deadlock, wait for nnnn milliseconds and retry 3 times. If it still fails, move on to rebuild the next index or end the job if this is the last one
if any such feature is coming up soon, it will be a life saver
Regards Kiran.