Why CMEMTHREAD Wait Type Occurs?

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.

[plain]

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.

[/plain]

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

[codesyntax lang=”cpp”]

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

[/codesyntax]

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

[codesyntax lang=”tsql”]

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

[/codesyntax]

[info]

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.

[/info]

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.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan