Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Index Maintenance: RESUMABLE Option with WaitAtLowPriority #716

Open
nakiva9 opened this issue Jan 31, 2023 · 0 comments
Open

Index Maintenance: RESUMABLE Option with WaitAtLowPriority #716

nakiva9 opened this issue Jan 31, 2023 · 0 comments

Comments

@nakiva9
Copy link

nakiva9 commented Jan 31, 2023

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.

@olahallengren olahallengren changed the title Index Maintenance: RESUMABLE Option with WAitfprLowPriority Index Maintenance: RESUMABLE Option with WaitfprLowPriority Nov 2, 2024
@olahallengren olahallengren changed the title Index Maintenance: RESUMABLE Option with WaitfprLowPriority Index Maintenance: RESUMABLE Option with WaitAtLowPriority Nov 2, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants