Database Performance Tuning: Analyzing and Resolving CMEMTHREAD Waits in SQL Server

Preface: A Microsoft engineer likened performance tuning to peeling an onion, layer by layer.

Six Factors

The most commonly used diagrams for analyzing database performance issues are presented, followed by the typical analysis order: hardware capability, system scale, internal database factors, software environment, business model and architecture, and finally code design.

Practical Case

On an October 2019 afternoon, a hospital’s information system experienced severe slowdown, overwhelming the support center.

Time: Some day in October 2019 Event: Hospital client experienced massive slowdown at 4 pm, causing system‑wide issues.

Fortunately, we had monitoring “cameras” on the database, allowing us to review the incident.

Hardware Capability

CPU

CPU usage stayed below 20 % during the incident.

Memory

Memory usage was normal; page lifecycle shown.

Available Memory

IO

IO queue average was low (~15.48) with a brief spike, possibly due to batch writes.

Overall, hardware resources were sufficient.

System Scale

Request rate per second did not increase but actually decreased, indicating congestion and reduced throughput.

Internal Database Factors

Waits

Slow Queries

Trend graphs confirm that the slowdown matched the reported incident time.

Root Cause of Slowness

Around 15:58, many CMEMTHREAD waits appeared, peaking at 100 concurrent waits at 16:08.

CMEMTHREAD wait occurs when tasks compete for thread‑safe memory objects, leading to contention.

Scenario

Occurs when ad‑hoc plans are inserted into the plan cache during compilation or recompilation.

In NUMA architectures, memory objects are partitioned by node.

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

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

If “Partitioned by Node” memory usage ranks high, use trace flag 8048 to reduce CMEMTHREAD waits.

Patch level: original version 11.0.5556.0, after applying the fix upgraded to 11.0.5623.0.

Software Environment

Current database version is 11.0.5556.0; after patching it becomes 11.0.5623.0.

Code Design

Typical statements causing waits are simple SELECT … INTO #tmp queries, each costing less than 5 seconds and not parallel.

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

Adjusting these statements can reduce CMEMTHREAD waits.

Business Model and Architecture

The system runs on a single machine with mixed OLAP and OLTP workloads; future plans include read/write separation or load balancing.

Solution

Install Latest Patch

Apply the latest SQL Server 2012 SP4 patch that contains the CMEMTHREAD fix.

Modify Parameter

Set “optimize for ad hoc workloads” from 0 to 1 to reduce memory consumption of ad‑hoc plans.

Increase TEMPDB Files

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

Move TEMPDB Files

Place TEMPDB files on faster storage (e.g., SSD) to improve response.

Optimize Application Code

Rewrite problematic queries as stored procedures or parameterized statements and limit concurrent creation of temporary tables.

Optimization Effect

After applying the measures, CMEMTHREAD waits disappeared the next day.

Waits

Slow Queries

Conclusion

The article teaches a systematic, onion‑layer approach to database performance troubleshooting and optimization.

Did you find this article helpful? Like and share to show your support!

For more resources, scan the QR code below: