Are you struggling with a laggy redo and a build up in the redo queue on your readonly secondaries? Are you suffering with high PARALLEL_REDO_TRAN_TURN waits? Then this magic remedy could cure your ailments.
There is a slight misconception with readonly AG replicas that the data should be 100% up to date with what’s on the primary. Even with a synchronous node you can never guarantee that what you see in the database will be 100% up to date with the primary. I discussed why this happens in a different post here https://sqlundercover.com/2019/10/16/availability-groups-and-redo-latency-data-committed-on-a-primary-isnt-visible-on-a-synchronous-secondary/
A few milliseconds behind is fine but if you start seeing your secondary replicas falling behind by seconds, minutes or longer then you probably have something else going on.
There are a handful of things that can block redo, one of those being DDL statements run from the primary, but I’ll look into that in a separate post. Today I want to have a look at how parallel redo can get itself into a tangle due to things like page splits and forwarded records and the symptoms that you might see when it does.
Parallel Redo
Prior to SQL2016, redo was a serial process. Each database had a single thread with which to play in the transactions but from SQL2016, things changed and redo became a parallel process.
Generally, parallel redo is a good thing and for the most part it should mean better performance for redo. According to Microsoft,
workloads with highly concurrent small transactions are expected to achieve better redo performance. When the transaction redo operation is CPU intensive, such as when data encryption and/or data compression are enabled, parallel redo has even higher redo throughput (Redone Bytes/sec) compared to serial redo. Moreover, indirect checkpoint allows parallel redo to offload more disk IO (and IO waits for slow disk) to its helper worker thread and frees main redo thread to enumerate more received log records in secondary replica. It further speeds up the redo performance.
However, if your workload on the primary happens to be very insert heavy and you see a large number of page splits and forwarded records going on you could start to see an issue.
When Parallel Redo Goes Bad
Let’s take a look at a recent issue that I was faced with.
I started to receive numerous reports that reports that were running against a read only secondary were returning out of date information.
The first thing that I do when I hear reports like that it to check, firstly that the AG is healthy and the databases are replicating.
That was all fine.
The next thing I checked was the size for the redo queue, the redo queue holds the transactions waiting to be rolled into the database. If we see that looking very large or growing we know that something’s not right.
Take a look at the redo queue size over the course of the morning,
Each of those spikes represents a time when redo was falling behind and the database was getting out of date. That’s really not good viewing.
You can also check the size of the redo queue using the SSMS Availability Group dashboard or with the following query.
SELECT databases.name, ReplicaStates.redo_queue_size
FROM sys.dm_hadr_database_replica_states ReplicaStates
JOIN sys.databases Databases ON Databases.replica_id = ReplicaStates.replica_id
AND databases.group_database_id = ReplicaStates.group_database_id
Check Your Wait Stats
What do you do if you are seeing your redo getting hung up like this?
As I said at the start of this post, there are a few things that can cause redo to fall behind, another common one is if the redo thread gets blocked but I’ll look into that in a separate post because there are a few very interesting things when talking about that which I don’t want to get into here.
We specifically looking at when parallel redo can become a problem and to diagnose that, the next thing you want to look at is your wait stats. If you’re seeing a lot of PARALLEL_REDO_TRAN_TURN waits which coincide with redo falling behind then you’re seeing this problem. Just out of interest what did my waits look like when all this was going on?
Yup they’re there and there’s a very close correlation between those and the redo queue size growing.
sqlskills.com is always my first go to when I need and explanation of a wait type so what do they say about this one?
This wait type is when a parallel redo thread on an Availability Group readable secondary replica needs to redo a log record, but there is another log record that must be redone first, and another parallel redo thread has not yet redone it. In other words, an operation occurred on the primary replica for which the log records must be redone in the correct order (either a page split or the generation of a forwarded record in a heap) and the log records for the operation were given to two or more parallel redo threads to redo, causing one of them to be delayed.
https://www.sqlskills.com/help/waits/parallel_redo_tran_turn/
What Can We Do About It?
So we know this issue is being caused by high page splits or a forwarded record (I also believe that creation and rebuild of indexes can contribute but more investigation is needed into the effect they can have).
We could look at reducing page splits or forwarded records and solve the problem at the root cause, there are many posts on there on those subjects so I won’t go into that here.
There is also a fairly quick and easy fix, simply switch off parallel redo and let it run in the old fashioned serial way.
Switching on trace flag 3459, either using
DBCC TRACEON (3459, -1)
or setting it as a start up trace flag. If you want the trace flag to persist after a server restart you’ll need to add it as a start up trace flag, trace flags set using DBCC TRACEON will be lost on server restart.
Did it Make a Difference
I switched the trace flag on at just after midday and as you can see, things are looking much better.
So, if you find yourself facing this issue, a simple trace flag could be the way out. Obviously, as with any changes to SQL configuration, monitor closely after the change to ensure that there are no unexpected side affects.
Thanks for reading