0

I have an issue where-in index rebuild is causing large redo queue on the synchronous AG secondary node.

This issue occurs only on 1 db, and the only difference between this db and all other dbs is that this db has delayed durability forced. On an average all dbs range from 300 to 600GB data file size.

Based on documentation I understand that for this db, the transaction durability is not guaranteed (for example- upon reboot or crash) and client recieves commit prior to the log record written to disk on primary/secondary AG node.

My question is- when the log is eventually flushed to disk on primary node (either via a durable transaction or force flush command), then does the log get sent to the secondary node as well at this point? So effectively this is a large chunk of records and probably the reason why redo queue becomes large?

Update: The redo queue buildup during index rebuild lasts only for about 20 seconds (when delayed durability is forced). If I set delayed durability off then there is no redo queue buildup during index rebuild. I'm wondering if the large chunks of flush (due to delayed durability) is causing the momentary redo queue build up.

8
  • "I have an issue where-in index rebuild is causing large redo queue..." - Obligatory reminder that index rebuilds are wasteful and unnecessary.
    – J.D.
    Aug 15, 2023 at 12:48
  • Not totally wasteful. To add more context I'm doing this (rebuild) after the one off data file shrink operation (after a large delete operation that clears out about 100GB of data).
    – variable
    Aug 15, 2023 at 16:28
  • Completely wasteful. Index fragmentation doesn't matter, removing it won't make your database faster or more efficient. Rebuilding also negates the shrink you just did by regrowing the database.
    – J.D.
    Aug 15, 2023 at 17:17
  • It doesn't regrow the size for me when rebuild is performed after a large delete operation followed by a shrink.
    – variable
    Aug 16, 2023 at 2:54
  • It unlikely will grow it to the size it previously was, but space is needed to be consumed for the rebuild process, typically a percentage of the size of the indexes themselves. The larger the indexes, the more space will be consumed. I'm sure your database size isn't exactly the same before and after you rebuilt.
    – J.D.
    Aug 16, 2023 at 3:31

1 Answer 1

2

I have an issue where-in index rebuild is causing large redo queue on the synchronous AG secondary node.

Yes, this will happen.

[...] when the log is eventually flushed to disk on primary node (either via a durable transaction or force flush command), then does the log get sent to the secondary node as well at this point?

Yes.

So effectively this is a large chunk of records and probably the reason why redo queue becomes large?

If a redo queue grows it can be due to various reasons. In this cause the reason is not delayed durability but rather the fact that a large index rebuild is occurring which is generating more log that is able to be sent than redo can get through in the same amount of time. Send and Redo are two separate items as the are not coupled. You can look at the redo thread(s) to see if there is anything you can tune, however, with index rebuilds this is just normal so it'd most likely be a waste of time.

4
  • I will update this in the question: the redo queue buildup lasts only for about 20 seconds (when delayed durability is forced). If I set delayed durability off then there is no redo queue buildup. I'm wondering if the large chunks of flush is causing the momentary redo queue build up.
    – variable
    Aug 15, 2023 at 10:36
  • As I stated, generating and sending more log than can be redone is the issue. Whether or not delayed durability helps or hurts it is inconsequential. Aug 15, 2023 at 12:37
  • In my scenario the cause seems to be the fact that delayed durability is resulting in large chunks of logs to be flushed.
    – variable
    Aug 15, 2023 at 16:27
  • The cause is that there will be a lag between large bursts of log being laid down and redo being able to keep up with that. Depending on various items, other servers with the same workload but a different configuration in hardware/OS/SQL may not have that issue. Thus the redo queue performance should be checked. It's pointless to use delayed durability with AGs just as an aside. Aug 16, 2023 at 0:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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