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

CMEMTHREAD

(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<SuspendQueueSLock>::Wait+2c6
    SOS_Task::PostWait+90
    EventInternal<SuspendQueueSLock>::Wait+2c6
    SOS_UnfairMutexPair::LongWait+191
    SOS_UnfairMutexPair::AcquirePair+46
    CMemThread<CMemObj>::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<SuspendQueueSLock>::Wait+2c6
    SOS_UnfairMutexPair::LongWait+191
    SOS_UnfairMutexPair::AcquirePair+46
    CMemThread<CMemProc>::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<SuspendQueueSLock>::Wait+2ca
    SOS_UnfairMutexPair::LongWait+191
    SOS_UnfairMutexPair::AcquirePair+46
    CMemThread<CMemObj>::Alloc+b6
    CMemPartitioned<CMemThread<CMemObj> >::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<SuspendQueueSLock>::Wait+2a8
    SOS_UnfairMutexPair::LongWait+191
    CMemThread<CMemObj>::GetObjectAccess+50
    CMemThread<CMemObj>::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