CMEMTHREAD waits during Index Optimisation in Availability Group

Question

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

Answer 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.