10

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":

enter image description here

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:

enter image description here

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.

CMEMTHREAD solved?

REDO speed increase

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 WAITstats showing CMEMTHREAD and the point when the trace flag was enabled, the CMEMTHREAD wait is gone and the redo contention is resolved.

enter image description here

15
  • Please add output of 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
  • 1
    Many people abandon index maintenance when using AGs because the cost of performing it far outweighs any benefits. Maybe try switching to just updating statistics for a bit? – Erik Darling May 17 '19 at 10:30
  • Thanks Eric, I had been considering not reorg/rebuild of the biggest indexes in favour is just stats updates, so I’ll definitely give that a go now that you’ve suggested it. I’m just wondering if the above wait type is typical of index optimisation on AGs or if it’s a bug. Doesn’t happen on the non-queried async replica, just the queried sync replica. – Peter May 17 '19 at 12:09
  • 1
    @peter it is hard to tell why this is happening, there was a bug related to CMEMTHREAD in older SQL Server version but not sure about SQL Server 2016. All i can direct you is to blog by Paul Randal for troubleshooting – Shanky May 17 '19 at 13:18
  • 1
    @trusha the solution is to enable the trace flag. This always resolves the issue immediately. I have that flag set on all my replicas now and no longer have the issue. My question is about why this is happening - is it a bug or expected behavior given the scenario? There is a plan to potentially update our SQL Servers to the latest sp/CU. If/when that happens, I'll disable the flag and see if the issue is resolved in an update. I doubt it. – Peter Jun 2 '20 at 10:31
1

I don't have access to an Availability Group secondary that's really cooking :-)

but i can share some things that'll get you a little farther.

When you have parallel redo on the secondary, activity like redo of heavy index maintenance is accompanied by CMEMTHREAD waits. These waits disappear when Trace Flag 3459 is globally enabled. And you are wondering if the CMEMTHREAD wait behavior is due to a bug.

It is unlikely the CMEMTHREAD wait behavior is due to a bug.

The post linked below is a few years old but still my favorite in explaining the intent of CMEMTHREAD waits as memory object serialization control. Bob explains those memory objects with access potentially controlled by CMEMTHREAD waits may be global, partitioned per NUMA node, or partitioned per CPU.

How It Works: CMemThread and Debugging Them Bob Dorr & Rohit Nayak 2012 December 20 https://techcommunity.microsoft.com/t5/sql-server-support/how-it-works-cmemthread-and-debugging-them/ba-p/317488

One of the important enhancements in SQL Server 2016 was the introduction of dynamic memory object partitioning. Prior to SQL Server 2016, as contention was observed to be a significant issue on individual global memory objects, individual trace flags would enable partitioning that object by NUMA node(trace flag 1236 is an example). And trace flag 8048 would promote many of the memory objects partitioned at the NUMA node level to being partitioned at the CPU level.

In SQL Server 2016, many of the memory objects became eligible for dynamic object partitioning: they'd start with a single global partition but as contention was detected they'd be promoted to NUMA node partitioned. If further contention is detected, the object would be promoted again to CPU partitioned.

More about that in this blog post.

Dynamic Memory Object Scaling Ajay Jagannathan 2016 June 27 https://techcommunity.microsoft.com/t5/sql-server/dynamic-memory-object-scaling/ba-p/384755

So while parallel redo activity is seeing high CMEMTHREAD waits, watching the results of the query below over time will reveal which memory objects are under contention. I think its likely to be a global object that is not eligible for dynamic partitioning.

SELECT omo.type, memory_node_id, omo.pages_in_bytes, omo.partition_type, omo.partition_type_desc, 
omo.waiting_tasks_count, omo.exclusive_access_count, omo.contention_factor
FROM sys.dm_os_memory_objects omo
WHERE omo.waiting_tasks_count > 0
ORDER BY omo.waiting_tasks_count DESC;

So, enabling global trace flag 3459 mitigates the CMEMTHREAD wait time. In this case, that's to be expected. Global trace flag 3459 disables parallel redo, leaving the AG secondary with serial redo. With a single redo thread for the Availability Group secondary redo activity, the contention for the memory object that was present with parallel redo is suddenly gone.

Whichever memory object is under contention in this scenario, it may be possible for enhancement to allow it to be dynamically promoted as many other objects are, which would alleviate the CMEMTHREAD waits even with parallel redo. But some work is harder or more expensive to partition than to accomplish as a single global object.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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