During reindexing using Ola's IndexOptimize stored procedure, the log send queues for remote asynchronous replicas can grow quite large if there are a lot of large index defrags, requiring gigantic transaction logs. There is an @Delay parameter in the script that can be used to insert a delay between each index operation, but it doesn't provide the ability to delay only when the log send queues are high.
A more intelligent throttling mechanism is required to control the log send queue sizes during heavy reindexing.
And yes, I know that reindexing isn't really necessary with our enterprise SSDs, but we do it to appease the third-party support organizations that will blame fragmentation for any performance issue.
Best Answer
Insert the following code into the IndexOptimize procedure to implement a wait that will allow the log send queues to flush before continuing. As written it will continue once there is less than 1 GB in the log send queues.
This code needs to be placed immediately after the
IF @Delay > 0
block in the procedure. With the current version of IndexOptimize, it would look like this: