Most of the time I find some or other topic to write on my blog sphere during my weekend on-call, last week was no exception. A client has a monthly reboot schedule for few servers spanning over two days – Saturday and Sunday. All these servers are configured with Always On, a Primary, a Secondary and a DR node. While working on this activity, a couple of servers did not catch up on synchronization, few databases were having an issue and they stuck at some point. This issue is known to the client, it is called – Parallel Redo Thread.
What it means is, there are main and slave threads for REDO queue and what engine does is, it performs queuing, analyzing or dequeuing streams of data so that it can be sent to slave threads. And, when it find another thread that sends data streams to Secondary/DR threads i.e. Index Rebuild operation it makes the operation little slower for this REDO thread.
Fixing Parallel REDO Thread in Always On
Fixing this issue needs recycle SQL Server service by adding trace flag 3459 as the startup parameter. What this trace flag will do is, it will disable Parallel REDO Thread and thus help to fix the issue.
Reading on Parallel REDO Thread
Parallel REDO Thread can cause you high memory usage as well according to Microsoft, I haven’t found much more details on this yet but here are couple of links that you can read
https://blogs.msdn.microsoft.com/alwaysonpro/2015/01/06/troubleshooting-redo-queue-build-up-data-latency-issues-on-alwayson-readable-secondary-replicas-using-the-wait_info-extended-event/
https://blogs.msdn.microsoft.com/alwaysonpro/2013/12/04/recovery-on-secondary-lagging-shared-redo-target/
I assume that this detail will prove helpful to you to some extent. Do let me know in case you have much more and detailed information regarding this issue of Parallel REDO Thread as I am interested in reading more on this and if we have a permanent fix available. In the meantime, you may want to browse through HADR and Troubleshooting section of my blog space for more troubleshooting tips like troubleshooting Always on Synchronization issue.
You may also want to download my script to monitor AlwaysOn Health.
