3

I have created a SQL maintenance job using Azure Elastic Job Agent with the following step:

EXEC jobs.sp_add_jobstep @job_name = @jobName,
@step_name = 'Optimize indexes and statistics',
@command=N'
    EXECUTE dbo.IndexOptimize
    @Databases = ''USER_DATABASES'',
    @FragmentationLow = NULL,
    @FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE'',
    @FragmentationHigh = ''INDEX_REBUILD_ONLINE'',
    @FragmentationLevel1 = 10,
    @FragmentationLevel2 = 30,
    @MinNumberOfPages = 10,
    @TimeLimit = 3600,
    @UpdateStatistics = ''ALL'',
    @OnlyModifiedStatistics = ''Y'',
    @SortInTempdb = ''Y'',
    @MaxDOP = 1,
    @LogToTable = ''Y''
',
@credential_name = @jobStepCredName,
@target_group_name= @targetGroupName,
@retry_attempts  = 0,
@step_timeout_seconds = 3600,
@max_parallelism = 1 -- IMPORTANT! We don't want to run index optimization on multiple databases at the same time

The code uses stored procedure dbo.IndexOptimize provided by Ola Hallengren.

The job is scheduled to run daily at 5 am and fails once or twice per week. The reason for failure is the internal issue to the Azure Elastic Job Agent: "Jobs service restarted while this task was in progress.". The service from Azure is still in preview so internal service errors are expected.

My current solution is to set @retry_attempts to some number greater than 0 so that Job Agent can retry the step, however I'm not sure if that is a good idea to retry a failed step for index optimization.

In particular, I'm not sure what would happen to the INDEX REBUILD, INDEX REORGANIZE, OR UPDATE STATISTICS processes if they would be cancelled or killed.

So to summarize I have the following questions:

  1. Is this a good idea to retry index maintenance if step fails?
  2. What happens when INDEX REBUILD, INDEX REORGANIZE, OR UPDATE STATISTICS processes fail or terminate.

I appreciate your feedback on this matter.

  • Under what schedule does this job run? Perhaps it's timing out, or running for 24 hours such that the next day's run "steps on" the previous day's? – Jon of All Trades Apr 11 '19 at 19:43
  • Hi @JonofAllTrades, the job runs daily at 5 am as provided in the description. It fails within a minute or so after starting the indexing step. – Alex S Apr 11 '19 at 21:55
  • May I suggest that you consider running the index optimize less frequently? Erik Darling has written on Brent Ozars blog about it in Stats week But do run update stats more often (it is an implicit part of index rebuilds)! – Henrik Staun Poulsen Sep 12 '19 at 18:19
  • @HenrikStaunPoulsen Can you elaborate? By less frequent you suggest not to run the job daily? I think fragmentation levels control the optimize, am I correct? – Alex S Sep 13 '19 at 14:00
  • @AlexS; yes, but when rebuilding indexes you also get a new set of stats. And this Stats is often the bit you need, not a perfect ironed index. Modern SSD as opposed to old slow HDDs. Stats updates are a lot cheaper and faster to do too. – Henrik Staun Poulsen Sep 15 '19 at 19:34
3
+50

In your case you should add the @Resumable = 'Y' option to your job and either restart or just wait for the next schedule to run the job and finish, depending on time and concurrency issues, as running index rebuild while many users are using the database can be an issue.

Statistic and index rebuilds were transactional in earlier versions so if an alter index rebuild statement failed the results were rolled back and the old object used but in 2017 onward there is a new option WITH (RESUMABLE=ON) that allows you to resume the index rebuild later.

You should also look at the @WaitAtLowPriorityMaxDuration and @LockTimeout parameters in Ola's scripts as those can help in minimizing blocking.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.