In a 3 node Availability group, a secondary replica will often become subject to redo lag due to the causes covered in Microsoft's documentation:
In my experience, the issues that I see most often appear to be:
A long-running transaction on the primary replica prevents the updates from being read on the secondary replica.
The redo thread on the secondary replica is blocked from making data definition language (DDL) changes by a long-running read-only query. The redo thread must be unblocked before it can make further updates available for read workload.
I can observe this by looking at the Extended Events session "AlwayOn Health":
When an application issues read-only queries to the secondary replica, if a heavily logged operation (like Index Optimisation) is running on the primary, sync lag becomes pronounced and I see a huge backlog in uncommitted log records on the secondary, as is described in the above MS docs.
The question I have is why I see CMEMTHREAD waits on the secondary replica when Index Reorganisation is taking place on the primary:
Is this normal/expected behaviour or something else?
Whilst there is some read activity on the secondary replica, those queries are most often <1 second in runtime, with the occasional <10 second query. CPU usage around 5%.
Output of @@VERSION: Microsoft SQL Server 2016 (SP1-CU10-GDR) (KB4293808) - 13.0.4522.0 (X64) Jul 17 2018 22:41:29 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
- CMEMTHREAD wait is only observed on the secondary replica
- The replica that shows this wait (and the lag) is an actively queried synchronous replica
I just spotted this WAIT occurring again during index optimisation. I killed the index job and that then obviously stopped the sync lag from increasing, however the CMEMTHREAD wait continued and redo seemed quite slow. I also noticed occasioinal PARALLEL_REDO_FLOW_CONTROL waits on the redo thread, so I simply executed
DBCC TRACEON (3459, -1) and suddenly redo speed increased and the backlog started to clear extremely quickly.
You can see I stopped index optimisation at 1:20pm and applied the trace flag at 1:45pm. Note that the SQLSentryOne wait graph is in UTC whilst the latter graph is in BST.
I have just observed this exact behaviour again on an
ASYNC replica with nothing running on it. The same trace flag resolved this again. I'm surprised as I had thought this was caused by read-only queries causing contention on
SYNC replicas with heavily-logged operations (like index maintenance) occuring on the primary. On this occasion, we have index maintenance on the primary, a
SYNC replica with no redo issue but an
ASYNC replica with this issue. Here you can see the
WAITstats showing CMEMTHREAD and the point when the trace flag was enabled, the CMEMTHREAD wait is gone and the redo contention is resolved.