Resumable reindexing

If you have ever needed to run OlaH ”IndexOptimize – USER_DATABASES” on a huge database there is normally no problem in doing so. However, when doing the same in an AG with one of the replicas located in another data center and using aynchronous commit, you can run into problems when the log file grows rapidly for the [HUGE_DB] due to changes in indexes not catching up on the slow 3rd replica [C]. This slowness can be due to network issues or slower specs, setup or physical disks. When the logged changes are lagging behind for the 3rd replica [C] then the log file cannot be cleared and recycled during backups and thus must grow.

In order to avoid excess growth of the log file the following SQL version 2017 and 2019 solution using the @Resumable = ‘Y’ parameter can be useful. OlaH scripts will generate the command level parameters for the ALTER INDEX command. Ensure that you have installed the latest possible version of OlaH solution.

Example setup.

Replica [A] (Primary) Synchronous commit
Replica [B] (Secondary) Synchronous commit
Replica [C] (Secondary) Asynchronous commit, typically on a slower connection or lower specs.

To get around this issue you can change the default OlaH parameters in a separate SQL Agent job for the huge database set up as shown below.

From:

EXECUTE [dbo].[IndexOptimize]
@Databases = 'HUGE_DB',
@LogToTable = 'Y',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

To:

EXECUTE [dbo].[IndexOptimize]
@Databases = 'HUGE_DB',
@Indexes = 'ALL_INDEXES',
@LogToTable = 'Y',
@WaitAtLowPriorityMaxDuration = 1,  -- wait max 1 min for lock
@WaitAtLowPriorityAbortAfterWait = 'SELF',
@LockTimeout = 60,   -- wait max 1 min for lock
@Delay = 10,         -- 10 s extra pause between commands
@Resumable = 'Y',    -- allow resumable operations
@TimeLimit = 180,    -- run every loop for max 3 minutes (in theory)
@MaxDOP = 1,         -- use only one CPU to lessen effect on system
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Schedule the normal log backups job to run every 15 min at HH:00, HH:15, HH:30, HH:45 and schedule the re-org for the huge db to run once every 30 min at HH:20 and HH:50 during typically one of the days in a week ensuring that no other heavy maintainance jobs run at the same time.

This setup will allow the (in theory) 3+ minutes segment of  ALTER INDEX  to execute and then at least 6 minutes for the node [C] to catch up with all the changes. Then the log backups runs two times (for safety) before the next time the resumable job runs.

Whenever the agent job aborts after 3+ minutes, there will be the ”yellow warning triangle symbol” in the job history (which is normal).
You should change the step to ”Quit the job reporting success” to avoid having errors reported every time an ALTER INDEX command stops due to the 180 s time limit.

To check the status of the  ALTER INDEX command and also seeing if there are any suspended jobs PAUSED until resuming 30 minutes later (assuming you use scheduling above).

USE [HUGE_DB]
SELECT * FROM  sys.index_resumable_operations

I am writing ”in theory” above due to having seen occasionally that an alter INDEX command has run well over 7 minutes resulting in larger than normal log file growth and this could be due to some bug or rule inside OlaH script that tries to calculate remaining time and altering parameters accordingly. (I have not investigated this further)

The first couple of times you run this script I would recommend to keep a close eye on the log file growth as well as the AG Dashboard while making sure that 30 minutes is enough to catch up with commits as well as also backup the log file so that it is logically empty in time fo the next round to start. Be prepared seeing typically every 2nd log file backup being considerably larger during the time you schedule these jobs.

In this setup example these jobs should exist on all 3 replicas but should most probably be disabled on replica [C] since its role is perhaps more of an emergency fall-back node.

When checking the job history you will typically see a series of job execution times around 3-5 minutes and then at the end of the day (if all indexes has been updated) < 1 min for each run.

This will give you an indication for how many 30-min repeats typically are needed for future fine tuning of weekly(?) schedule start and end times.