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