Peeling the Onion: A Step‑by‑Step Guide to Solving SQL Server CMEMTHREAD Wait Issues

Preface

A Microsoft engineer once compared performance tuning to peeling an onion, layer by layer. This article follows that analogy.

Six Major Factors

The most common diagrams we use when analyzing database performance issues cover four layers (the order can vary):

Hardware capability

System scale

Database internal factors

Software environment

Always start from a global perspective and avoid jumping straight into optimizing a single SQL statement.

Real‑World Case

Time: January 2018, one afternoon Event: A hospital’s information system became severely sluggish, overwhelming the support center.

We had monitoring “cameras” on the database and used the captured metrics to diagnose the problem.

Hardware Capability

CPU

CPU usage stayed below 20% during the incident, indicating no CPU bottleneck.

Memory

Memory consumption was normal, as shown by the graphs.

IO

IO queue average was low (~15.48) with a brief spike; no sustained IO bottleneck.

Overall, hardware resources were sufficient.

System Scale

During the incident, the per‑second batch request count actually decreased, indicating system congestion and reduced throughput.

Database Internal Factors

Wait Types

Monitoring showed a surge of CMEMTHREAD waits around 15:58, peaking at 100 concurrent waits.

What Is CMEMTHREAD Wait?

Microsoft describes it as a thread waiting for a thread‑safe memory object when multiple tasks contend for the same memory allocation.

Scenarios

CMEMTHREAD waits appear when:

Ad‑hoc plans are inserted into the plan cache during compilation/re‑compilation.

On NUMA systems, memory objects are partitioned by node.

SQL Server can partition memory objects (Global, Per NUMA Node, Per CPU) to reduce cross‑node/thread contention.

Diagnosing Memory Objects

SELECT type, pages_in_bytes, CASE WHEN (0x20 = creation_options & 0x20) THEN 'Global PMO. Cannot be partitioned by CPU/NUMA Node.' WHEN (0x40 = creation_options & 0x40) THEN 'Partitioned by CPU.' WHEN (0x80 = creation_options & 0x80) THEN 'Partitioned by Node.' ELSE 'UNKNOWN' END FROM sys.dm_os_memory_objects ORDER BY pages_in_bytes DESC;

If “Partitioned by Node” ranks high, applying TRACE FLAG 8048 can reduce CMEMTHREAD waits.

Ensure the latest patches are applied; known fixes exist for SQL Server 2008 R2, 2012, and 2014.

Software & Hardware Environment

Database version: 11.0.5556.0; after patching: 11.0.5623.0.

Code Design

Statements Causing the Wait

SELECT * INTO #Tmp FROM TB WHERE 1=2;

These simple SELECT‑INTO statements generate many concurrent temporary tables, leading to CMEMTHREAD contention.

Business Model & Architecture

The system runs on a single server handling mixed OLAP and OLTP workloads; future plans include read/write splitting or load balancing.

Solutions

Install Latest Patches

Apply the latest SQL Server 2012 SP4 (or newer) fixes for CMEMTHREAD issues.

Modify Parameters

Set optimize for ad hoc workloads from 0 to 1 to reduce memory pressure from ad‑hoc plans.

Increase TEMPDB Data Files

Adding more TEMPDB files reduces latch contention after CMEMTHREAD waits are eliminated.

Move TEMPDB Files

Place all TEMPDB files on a fast SSD (preferably the same drive) to improve response.

Optimize Application Code

Rewrite statements to avoid excessive temporary table creation, use stored procedures or parameterized queries, and adjust TEMPDB settings to lower concurrency.

Optimization Results

After applying the above measures, CMEMTHREAD waits disappeared the next day, and slow‑query metrics improved.

Waits

Slow Statements

Conclusion

By following this onion‑layer approach, you can systematically diagnose and resolve SQL Server performance problems, from hardware checks down to code‑level tweaks.

References

Microsoft blog: How It Works: CMemThread and Debugging Them – https://blogs.msdn.microsoft.com/psssql/2012/12/20/how-it-works-cmemthread-and-debugging-them/

SQL Server 2016 improvements: Dynamic Memory Object (CMemThread) Partitioning – https://www.cnblogs.com/OwenZeng/p/SQL%202016%20%E2%80%93%20It%20Just%20Runs%20Faster:%20Dynamic%20Memory%20Object%20(CMemThread)%20Partitioning