Many CMEMTHREAD waits when many SchemaMgr Store entries exist in SQL Server

Applies to: SQL Server 2017 on WindowsSQL Server 2014SQL Server 2016

Symptoms


You may experience performance degradation in Microsoft SQL Server. When this issue occurs, you observer the following situation:

  • There are millions of SchemaMgr Store entries in the memory cache. You can see the information by running the following T-SQL script:
    SELECT entries_count FROM sys.dm_os_memory_cache_counters where name = 'SchemaMgr Store'
  • This issue can be accompanied by an increase in CMEMTHREAD waits and lock blocking, in which the locks' wait_resource refers to a COMPILE lock type. You can see the information by running the following T-SQL script:
    select * from sys.dm_exec_requests where wait_type = 'CMEMTHREAD'select * from sys.dm_exec_requests where wait_resource like '%compile%'

Cause


This issue occurs when the number of cached HOBT statistics hits an internal soft limit. When the limit is reached, the system tries to aggressively remove entries, which causes contention on the memory object that's storing the data.

Resolution


To fix this issue, enable Trace Flag (TF) 8032. For more information about TF 8032, see the following articles: