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:
Troubleshoot: Changes on the Primary Replica are not Reflected on the Secondary Replica
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.
and
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
Update:
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.
- Update
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 WAIT
stats showing CMEMTHREAD and the point when the trace flag was enabled, the CMEMTHREAD wait is gone and the redo contention is resolved.
select @@version
in the question, and does this CMEMTHREAD wait only occurs on secondary or do you see it on primary as well ? – Shanky May 17 '19 at 9:36