CMEMTHREAD

(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)(Back to main page…)Description:

This wait type is when a thread is waiting for access to a critical section of code around a thread-safe memory object (called CMemThread objects internally) so it can allocate or deallocate memory.

(Books Online description: “Occurs when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.”)

Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.

Added in SQL Server version:

Pre-2005/2005

Removed in SQL Server version:

N/A

Extended Events wait_type value:

The map_key value in sys.dm_xe_map_values is 186 in 2008 and 2008 R2, and 190 in 2012 and 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.

Other information:

If this wait type is prevalent, that indicates that a memory object in SQL Server is a contention point. Unfortunately, it is hard to tell which memory object is the contention point without capturing SQL Server call stacks to figure out where most of the CMEMTHREAD waits are occurring.

There is a good CSS post about CMEMTHREAD waits and debugging them here, which explains how the trace flag 8048 may help alleviate the issue if the memory object is one that is currently partitioned by NUMA node. My blog post on collecting SQL Server call stacks for a particular wait type is here.

Note that there have been various bug fixes involving CMEMTHREAD waits over the years, including:

• 3074425, from large number of ad hoc queries on 2012 and 2014
• 2887888, from building large spatial indexes on 2012 and 2014
• 2492381, from using temp tables/variables to load or update rowsets on 2008 R2
• Here, where servers with more than 8 cores per NUMA node may require trace flag 8048

Note also that in SQL Server 2016, CMemThread objects will dynamically be promoted to per-NUMA node or per-CPU when contention is detected (i.e. a superset of the TF 8048 behavior). This should mean that CMEMTHREAD waits become less of an issue on SQL Server 2016. See this blog post for more details.

Additional: I’ve heard of information online that states other reasons for CMEMTHREAD waits occurring, including plan cache lookups on busy systems, or searching through database metadata. These explanations are unfortunately incorrect.

Known occurrences in SQL Server (list number matches call stack list):

1. Allocating memory (in this case, to instantiate a data record in memory during the fact generation phase of DBCC CHECKDB)
2. Allocating memory (in this case, while building a sort table during a stream aggregate)
3. Allocating memory (in this case, to hold the Storage Engine metadata for a temporary sort table during a top-N query)
4. Freeing memory (in this case, removing an SSPI entry while closing a client connection)

And many, many more similar call stacks.Abbreviated call stacks (list number matches known occurrences list):

1. SOS_Task::PostWait+90 EventInternal::Wait+2c6 SOS_Task::PostWait+90 EventInternal::Wait+2c6 SOS_UnfairMutexPair::LongWait+191 SOS_UnfairMutexPair::AcquirePair+46 CMemThread::Alloc+b6 AutoDataBufferFactory::AllocateInternal+4e LocalAutoDataBufferCache::GetAutoDataBuffer+9e RecordMetadata::SetupBuffer+5f CheckTables::InstantiateAndCheckRecordFast+1e CheckTables::InstantiateAndCheckDataRecord+5c CheckTables::ProcessDataPage+164 CheckTables::ProcessPage+7d7 CheckTables::ProcessNextData+2ce CheckAggregateSingleInstance::GetNextFact+247 CTRowsetInstance::FGetNextRow+3c CUtRowset::GetNextRows+a0 CQScanRmtScanNew::GetRowHelper+3b5 CQScanXProducerNew::GetRowHelper+366 CQScanXProducerNew::GetRow+15 FnProducerOpen+57 FnProducerThread+851 SubprocEntrypoint+a59
2. SOS_Task::PostWait+90 EventInternal::Wait+2c6 SOS_UnfairMutexPair::LongWait+191 SOS_UnfairMutexPair::AcquirePair+46 CMemThread::Alloc+bc CXPacket::CXPacket+93 CXPacketMgr::Open+167 CXPipe::Open+b2 CXPort::Open+40 CQScanExchangeNew::Open+104 CQScanSortNew::BuildSortTable+3a CQScanSortNew::OpenHelper+c0 CQScanNew::OpenHelper+41 CQScanStreamAggregateNew::Open+30 CQScanNew::OpenHelper+41 CQScanXProducerNew::Open+c8 FnProducerOpen+44 FnProducerThread+851 SubprocEntrypoint+a59 SOS_Task::Param::Execute+21e SOS_Scheduler::RunTask+a8
3. SOS_Task::PostWait+9e EventInternal::Wait+2ca SOS_UnfairMutexPair::LongWait+191 SOS_UnfairMutexPair::AcquirePair+46 CMemThread::Alloc+b6 CMemPartitioned<CMemThread >::Alloc+86 PageCache::InitPageCache+de AllocationCachesManager::AddAllocationCaches+1f5 CacheOnlyHoBt::Init+332 HoBtFactory::CreateHoBt+5fc SECreateRowset+c03 CreateFakeWorkRowset+228 CreateFakeWorkRowsetFromTi+328 CreateFakeWorkTable+119 RowsetSorted::Init+369 CreateRowsetSort+240 CQScanSortNew::CreateSortTable+140 CQScanTopSortNew::CreateTopNTable+a3 CQScanTopSortNew::CQScanTopSortNew+63 CXteTopSort::QScanGet+a5 CBpXte::PqsInitializeBatchProcessingTree+43 CQScanXProducerNew::CQScanXProducerNew+251
4. SOS_Task::PostWait+90 EventInternal::Wait+2a8 SOS_UnfairMutexPair::LongWait+191 CMemThread::GetObjectAccess+50 CMemThread::Free+9e commondelete+56 SNI_Sec::DeleteSspi+175 SNIClose+b1 CNetConnection::CloseNetlibEP+48 CNetConnection::KillConnection+213 CPhysicalConnection::NotifySessionLogout+e3 CSession::LogoutSession+707 CSession::RemoveBatch+d3 process_close_connection+129 process_commands+920 SOS_Task::Param::Execute+21e SOS_Scheduler::RunTask+a8