Why CMEMTHREAD Wait Type Occurs?

The estimated reading time for this post is 4 minutes

It is very rare to get CMEMTHREAD wait type as top 10 SQL Server wait-stats ordered by wait_time_ms column as descending. CMEMTHREAD indicates there are lots of memory object synchronization to make sure that object accessed by different thread (worker) would not get corrupted. this technique is mostly talked about when Windows Synchronization Object, such as Mutex or CriticalSection topic comes into the picture.

In simple example, Synchronization process is like people using ATM, in a time only one person can use ATM and ATM only process request for one card holder. The process workflow will be such as the following:-

  1. Enter CriticalSection/Mutex
  2. Do Something That Other Threads are not Allowed.
  3. Leave CriticalSection/Mutex

Prior focusing on what is CMEMTHREAD or CMEMOBJ, that great to know a little bit about HEAP.

What is HEAP?

Each process has a default heap provided by the system. Applications that make frequent allocations from the heap can improve performance by using private heaps. A private heap is a block of one or more pages in the address space of the calling process. After creating the private heap, the process uses functions such as HeapAlloc and HeapFree to manage the memory in that heap. The heap functions can also be used to manage memory in the process’s default heap, using the handle returned by the GetProcessHeap function.

heap

When a HeapAlloc takes in place, the heap will locate a free block (As illustrated) to support the allocation, update the free list, update used information and could even allocate new segment if necessary to create more free memory. The maintenance of the lists are important to make sure the heap structure remains intact. if multiple threads attempt to modify the heap structure in parallel, the structure will be damaged and lead to memory corruption.

At this point, you learnt about HEAP, but SQL Server does not use HEAP or HEAP functions directly. There are two different classes in SQL Server to represent HEAP and HEAP functions called CMEMTHREAD and CMEMOBJ.

CMEMOBJ acts like a HEAP in SQL Server engine, so SQL Server calls CreateMemoryObject function instead of HeapCreate, which is managed by SQL Server Memory Manager. CMEMOBJ is responsible to handle any kind of activity in SQL Server such as Alloc, Free, ReAlloc, Size and Validate. Remember that CMEMOBJ is also not thread safe, therefore only one thread can access it at same time, it is similar to HEAP_NO_SERIALIZE in Windows.

CMEMTHREAD acts like a serializer around CMEMOBJ, the SQL Server creates a memory object with a thread safe flag and CreateMemoryObject will return a pointer to CMEMTHREAD instead of the CMEMOBJ. By overriding some underling Windows methods, SQL Server is able to share a memory object among any thread and make it thread safe object.

For example the CMEMTHREAD::Alloc method in SQL Server looks like below:-

CMemThread::Alloc(_Some_Parameters)
{
                Enter SOS_Mutex       //        SQL Server accumulates the CMEMTHREAD wait type value and makes sure the synchronization is done.
                CMemObj::Alloc()   //        It calls the HEAP::Alloc method by using __super::Alloc
                Leave SOS_Mutex		//	SQL Server release the lock on the object and lets other thread access it.
}

It is very usual to get CMEMTHREAD wait during query execution, which means multi threads are accessing the same CMEMOBJ. When the wait time becomes huge, then it is time for you to release some pressure from the specific CMEMOBJ.

Memory Object Segments

For high scalability and performance increment, SQL Server engine allows a memory object to be segmented by Node (NUMA) or CPU, therefore only threads on the same segment can access the memory object. This technique reduces the thread interaction with other Nodes or CPUs. Several activities in SQL Server uses CMEMPARTITIONED allocator, which partitions memory by NUMA Node or CPU.

mem-obj-segment

From the top, is the NUMA node based memory object, which uses synchronization object (usually CMEMTHREAD or SOS_SUSPEND_QUEUE type shows in the wait type) at NUMA node level to allocate memory for the local worker. On the bottom, is the CPU based memory object allocation that synchronization object at CPU level.

You may find out the count and size of memory object per segment by executing the following T-SQL script:-

SELECT 
	SUM(pages_in_bytes)/1024 AS [MemObjSize_KB],
	COUNT(*) AS [MemObjCount],
	CASE WHEN (0x20 = creation_options & 0x20) THEN 'Global'
	WHEN (0x40 = creation_options & 0x40) THEN 'CPU'
	WHEN (0x80 = creation_options & 0x80) THEN 'NUMA'
	ELSE 'Unknown'
	END AS [OPTION]
FROM sys.dm_os_memory_objects
GROUP BY
	CASE WHEN (0x20 = creation_options & 0x20) THEN 'Global'
	WHEN (0x40 = creation_options & 0x40) THEN 'CPU'
	WHEN (0x80 = creation_options & 0x80) THEN 'NUMA'
	ELSE 'Unknown'
	END

In most cases, CPU based allocation reduces the synchronization collisions because SQLOS handles the logical scheduling in efficient manner. However this type of partitioning comes with an overhead for maintaining the synchronization, access path and memory list in CPU.

In most cases, NUMA based allocation reduces the number of partition nodes but in other hand increases the collision possibility.

How to Reduce CMEMTHREAD Wait Time?!

Of course it is not an easy task to troubleshoot and rectify the CMEMTHREAD wait type, therefore to resolve this specific wait type you are advised to follow the following steps:-

  1. Check the Memory Object Count in each Segments: by given T-SQL, you need to check which segment is having huge number of Memory Objects. If NUMA node is holding huge memory objects, then you may use trace flag 8048 at startup to change the default behavior and make all the memory objects on CPU segments.
  2. Tune the T-SQL query that Access to Memory Object: if the result of the first step shows that Global or CPU segments are having the most Memory Objects, then it is time for you to find the query that causes the issue and tune it to reduce the memory object and thread synchronization.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

1 Comment on "Why CMEMTHREAD Wait Type Occurs?"

Notify of
avatar
1000
Sort by:   newest | oldest | most voted
Anil
Guest

Thank you Hamid for sharing interesting stuff !

wpDiscuz