How It Works: CMemThread and Debugging Them

EnterCriticalSection              //             Wait for your turn

dwVal++   //             Do something that no other thread is allowed to do unless they have the ticket

LeaveCriticalSeciton             //             Allow the next ticket owner to execute the code

While the example is simplistic it quickly applies to a Heap.   To allocate memory from a heap you would use HeapAlloc.  The heap maintains various lists that can only be adjusted by one thread at a time or it would corrupt the lists.  Let’s take a closer look at a high level heap design.

EnterCriticalSection
HeapAlloc
LeaveCriticalSection

If you allow the Heap to maintain synchronization it will provide an efficient synchronization wrapper on your behalf so you don’t have additional synchronization mechanisms in your code.

CMemObj

SQL Server has a class named CMemObj that can be thought of as acting like a heap for the SQL Server developers.  Instead of using HeapCreate the developer is calls CreateMemoryObject (often called a PMO – pointer to memory object) that is backed by the SQL Server memory manager.  If you execute a select against sys.dm_os_memory_objects you can see the various memory objects currently in use by the SQL Server.  The CMemObj is responsible for handling common activities such as Alloc, Free, ReAlloc, … as you would expect.

CMemThread

The CMemThread is the serialization wrapper around a CMemObj.   For example the CMemThread::Alloc looks like the following.

CMemThread::Alloc(…)

{

Enter SOS_Mutex       //        CMEMTHREAD WAIT TYPE AND ACCUMULATION OCCURS HERE

CMemObj::Alloc(…)   //        __super::Alloc

Leave SOS_Mutex

}

3 Types

There are 3 types of memory objects  (Global, Per Numa Node, Per CPU).    For scalability SQL Server will allow a memory object to be segmented so only threads on the same node or cpu have the same underlying CMemObj, reducing thread interactions from other nodes or cpus, thereby increasing performance and scalability.

bThreadSafe = 0x2,

bPartitionedByCpu = 0x40,

bPartitionedByNode = 0x80,     -T8048 upgrade from by Node to by CPU  (Can’t upgrade from global to by CPU)

Here is an example that shows the active memory objects that are partitioned by cpu.

SELECT * FROM sys.dm_os_memory_objects
WHERE 0x40 = creation_options & 0x40

Will TF 8048 Help Reduce CMEMTHREAD Waits ?

Here is a query that you can run on your box when you see high CMEMTHREAD waits.

SELECT
 type, pages_in_bytes,
 CASE
 WHEN (0x20 = creation_options & 0x20) THEN 'Global PMO. Cannot be partitioned by CPU/NUMA Node. TF 8048 not applicable.'
 WHEN (0x40 = creation_options & 0x40) THEN 'Partitioned by CPU.TF 8048 not applicable.'
 WHEN (0x80 = creation_options & 0x80) THEN 'Partitioned by Node. Use TF 8048 to further partition by CPU'
 ELSE 'UNKNOWN'
 END
from sys.dm_os_memory_objects
order by pages_in_bytes desc

Removing Hot Memory Object

·         If the memory object is NUMA partitioned you may be able to use the trace flag to further partition the object and increase performance.

·         If the memory object is global or already partitioned by CPU you need to study and tune the queries impacting the memory object.

Troubleshooting

To troubleshoot this issue, we need to understand the code path that is causing contention on a memory object.

00 sqlservr!CMemThread<CMemObj>::Alloc

01 sqlservr!operator new

02 sqlservr!HoBtFactory::AllocateHoBt

03 sqlservr!HoBtFactory::GetFreeHoBt

04 sqlservr!HoBtFactory::GetHoBtAccess

05 sqlservr!HoBtAccess::Init

06 sqlservr!HoBtFactory::CreateHoBt

07 sqlservr!SECreateRowset

08 sqlservr!DDLAgent::SECreateRowsets

09 sqlservr!CIndexDDL::CreateRowsets

0a sqlservr!CIndexDDL::CreateEmptyHeap

…

00 ntdll!NtSignalAndWaitForSingleObject

01 KERNELBASE!SignalObjectAndWait

02 sqlservr!SOS_Scheduler::Switch

03 sqlservr!SOS_Scheduler::SuspendNonPreemptive

04 sqlservr!SOS_Scheduler::Suspend

05 sqlservr!EventInternal<Spinlock<154,1,0> >::Wait

06 sqlservr!SOS_UnfairMutexPair::LongWait

07 sqlservr!SOS_UnfairMutexPair::AcquirePair

08 sqlservr!CMemThread<CMemObj>::Alloc

09 sqlservr!operator new

0a sqlservr!HoBtFactory::AllocateHoBt

0b sqlservr!HoBtFactory::GetFreeHoBt

0c sqlservr!HoBtFactory::GetHoBtAccess

0d sqlservr!HoBtAccess::Init

0e sqlservr!HoBtFactory::CreateHoBt

0f sqlservr!SECreateRowset

10 sqlservr!DDLAgent::SECreateRowsets

11 sqlservr!CIndexDDL::CreateRowsets

12 sqlservr!CIndexDDL::CreateEmptyHeap

…

SELECT r.session_id,r.wait_type,r.wait_time,r.wait_resource
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
 ON s.session_id=r.session_id AND s.is_user_process=1

session_id wait_type       wait_time   wait_resource
---------- --------------- ----------- ---------------
54         NULL            0
55         NULL            0
56         CMEMTHREAD      17062
57         CMEMTHREAD      17062
58         CMEMTHREAD      17063
59         CMEMTHREAD      17063
60         CMEMTHREAD      17062

--First get the map_key for CMEMTHREAD wait type from the name-value pairs for all wait types stored in sys.dm_xe_map_values
--NOTE :- These map values are different b/w SQL Server 2008 R2 and 2012

SELECT m.* 
FROM sys.dm_xe_map_values m
JOIN sys.dm_xe_packages p 
  ON m.object_package_guid = p.guid
WHERE p.name = 'sqlos' 
 AND m.name = 'wait_types'
 AND m.map_value = 'CMEMTHREAD'

/*

name                                                         object_package_guid                  map_key     map_value

------------------------------------------------------------ ------------------------------------ ----------- ---------------

wait_types                                                   BD97CC63-3F38-4922-AA93-607BD12E78B2 186         CMEMTHREAD

*/

--Create an Extended Events session to capture callstacks for CMEMTHREAD waits ( map_key=186 on SQL Server 2008 R2)
--Create an Extended Events session to capture callstacks for CMEMTHREAD waits ( map_key=186 on SQL Server 2008 R2)

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='XeWaitsOnCMemThread')
DROP EVENT SESSION [XeWaitsOnCMemThread] ON SERVER

CREATE EVENT SESSION [XeWaitsOnCMemThread] ON SERVER
ADD EVENT sqlos.wait_info(
ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)
WHERE (
[wait_type]=(186)) --map_key for CMEMTHREAD on SQL Server 2008 R2)
AND [opcode] = (1)
AND [duration]> 5000 -- waits exceed 5 seconds
)
ADD TARGET package0.asynchronous_bucketizer
(SET filtering_event_name=N'sqlos.wait_info',
 source_type=1,
 source=N'package0.callstack')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

--Create a second Xevent session to generate a mini dump of all threads for the first two wait events catpured for CMEMTHREAD

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='XeDumpOnCMemThread')
DROP EVENT SESSION [XeDumpOnCMemThread] ON SERVER

CREATE EVENT SESSION [XeDumpOnCMemThread] ON SERVER
ADD EVENT sqlos.wait_info(
ACTION(sqlserver.session_id,sqlserver.sql_text,sqlserver.create_dump_all_threads)
WHERE (
[wait_type]=(186)) --map_key for CMEMTHREAD on SQL Server 2008 R2)
AND [opcode] = (1)
AND [duration]> 5000 -- waits exceed 5 seconds
AND package0.counter <=2 --number of times to generate a dump
)
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

--Start the sessions
ALTER EVENT SESSION [XeWaitsOnCMemThread] ON SERVER STATE=START
GO
ALTER EVENT SESSION [XeDumpOnCMemThread] ON SERVER STATE=START
GO

When you collect data using the histogram target, you can acquire the un-symbolized call stack using the following query.

SELECT
 n.value('(@count)[1]', 'int') AS EventCount,
 n.value('(@trunc)[1]', 'int') AS EventsTrunc,
 n.value('(value)[1]', 'varchar(max)') AS CallStack
FROM
(SELECT CAST(target_data as XML) target_data
 FROM sys.dm_xe_sessions AS s
 JOIN sys.dm_xe_session_targets t
 ON s.address = t.event_session_address
 WHERE s.name = 'XeWaitsOnCMemThread'
 AND t.target_name = 'asynchronous_bucketizer') as tab
CROSS APPLY target_data.nodes('BucketizerTarget/Slot') as q(n)

EventCount  EventsTrunc CallStack
--------------------------------------------------------------------------------------
1           0           0x0000000001738BD8
0x0000000000E53F8B
0x0000000000E541C1
0x0000000000E529B6
0x0000000000FBF22A
0x0000000000F763CB
0x0000000000E578C4
0x0000000000E56DFA
0x0000000000F86416
…

0:049> .sympath SRV*c:\symcache_pub*http://msdl.microsoft.com/download/symbols

Symbol search path is: SRV*c:\symcache_pub*http://msdl.microsoft.com/download/symbols

Expanded Symbol search path is: srv*c:\symcache_pub*http://msdl.microsoft.com/download/symbols

0:049> .reload /f sqlservr.exe

0:049> ln 0x0000000001738BD8

(00000000`00e5462c)  sqlservr!XeSosPkg::wait_info::Publish+0xe2   | (00000000`00e5471c)   sqlservr!SETaskSuspendingNotification

0:049> ln 0x0000000001738BD8;ln 0x0000000000E53F8B;ln 0x0000000000E541C1;ln 0x0000000000E529B6;;ln 0x0000000000FBF22A;ln 0x0000000000F763CB;ln 0x0000000000E578C4;ln 0x0000000000E56DFA;ln 0x0000000000F86416;ln 0x0000000000F7D922;ln 0x0000000000F87943;ln 0x0000000000F0083B;ln 0x0000000000F05D00

(00000000`00e5462c)  sqlservr!XeSosPkg::wait_info::Publish+0xe2

(00000000`00e53d58)  sqlservr!SOS_Scheduler::UpdateWaitTimeStats+0x286

(00000000`00e54174)  sqlservr!SOS_Task::PostWait+0x4d

(00000000`00e52890)  sqlservr!EventInternal<Spinlock<154,1,0> >::Wait+0x1b2

(00000000`00f7628c)  sqlservr!SOS_UnfairMutexPair::LongWait+0x104

(00000000`00e577f4)  sqlservr!SOS_UnfairMutexPair::AcquirePair+0x46

(00000000`00e57858)  sqlservr!CMemThread<CMemObj>::Alloc+0x6c

(00000000`00e56ddc)  sqlservr!operator new+0x1e

(00000000`00f7d930)  sqlservr!HoBtFactory::AllocateHoBt+0xba

(00000000`00ef4a38)  sqlservr!HoBtFactory::GetFreeHoBt+0x12a

…