You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
Regards
Kiran.
The text was updated successfully, but these errors were encountered:
olahallengren
changed the title
Index Maintenance: RESUMABLE Option with WAitfprLowPriority
Index Maintenance: RESUMABLE Option with WaitfprLowPriority
Nov 2, 2024
olahallengren
changed the title
Index Maintenance: RESUMABLE Option with WaitfprLowPriority
Index Maintenance: RESUMABLE Option with WaitAtLowPriority
Nov 2, 2024
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.
The text was updated successfully, but these errors were encountered: