Rank Top Memory Consumers with sys.dm_os_memory_clerks

calendar Jul 20, 2026 / · 8 min read · sql server sys.dm_os_memory_clerks memory clerks memory pressure plan cache xtp query store dmv performance monitoring scripts  ·
Share on: linkedin copy

Buffer pool counters explain data cache pressure but go silent the moment memory trouble comes from somewhere else. sys.dm_os_memory_clerks is the DMV that covers everything the buffer pool doesn't — plan cache, In-Memory OLTP, Query Store, query memory grants, Extended Events sessions — by reporting pages_kb per clerk. Grouping those rows by type produces a ranked list of exactly which internal SQL Server subsystem is holding the most memory right now.

Purpose and Overview

In-Memory OLTP tables, introduced in SQL Server 2014, don't live in the buffer pool at all — they hold their rows in memory-optimized structures managed by a dedicated clerk, so a DBA watching only buffer pool metrics will never see that memory pressure building. The same is true of the plan cache, which can grow large on a server plagued by non-parameterized ad hoc queries, and of Query Store, which persists plan and runtime statistics in memory before they're written to disk. Each of these subsystems allocates through SQL Server's internal memory manager, and every one of those allocations is tracked by a memory clerk.

sys.dm_os_memory_clerks exposes one row per clerk instance, each representing a component that has registered with SQL Server's memory broker to request and release pages. The key column is pages_kb — the amount of committed memory, in kilobytes, that clerk currently holds. A raw SELECT * FROM sys.dm_os_memory_clerks on a busy instance returns dozens to hundreds of rows, because some clerk types — notably CACHESTORE_SQLCP for ad hoc plan cache entries — spawn one instance per NUMA node or per resource pool. Aggregating pages_kb by clerk type collapses that instance-level detail into a small, ranked, actionable summary.

The script below produces two views of the same data: a type-level rollup showing total memory and percentage of total clerk memory per subsystem, and a detail query showing the largest individual clerk instances by name. Together they answer the two questions a memory-pressure investigation usually starts with — which subsystem is consuming memory, and which specific instance within it.

Code Breakdown

The rollup query groups every clerk row by type, sums pages_kb, and computes each type's share of total clerk memory with a window function.

 1SELECT
 2    mc.type                                          AS clerk_type,
 3    COUNT(*)                                         AS clerk_instances,
 4    SUM(mc.pages_kb)                                 AS total_pages_kb,
 5    SUM(mc.pages_kb) / 1024                          AS total_pages_mb,
 6    CAST(SUM(mc.pages_kb) * 100.0 /
 7        SUM(SUM(mc.pages_kb)) OVER ()                AS DECIMAL(5,2))
 8                                                      AS pct_of_total_clerk_memory
 9FROM sys.dm_os_memory_clerks AS mc
10GROUP BY mc.type
11ORDER BY total_pages_kb DESC;

A companion query drops the aggregation and surfaces the largest individual clerk instances by name and NUMA node, for cases where the rollup points at a type with many instances and the next question is which one:

1SELECT TOP 25
2    mc.type            AS clerk_type,
3    mc.name             AS clerk_name,
4    mc.memory_node_id,
5    mc.pages_kb,
6    mc.pages_kb / 1024  AS pages_mb
7FROM sys.dm_os_memory_clerks AS mc
8ORDER BY mc.pages_kb DESC;

Aggregating pages_kb by clerk type

pages_kb is the only sizing column the DMV exposes — there is no pre-aggregated total anywhere in the system views. GROUP BY mc.type with SUM(mc.pages_kb) collapses potentially hundreds of clerk-instance rows into one row per subsystem, which is the level a DBA actually reasons about during triage. COUNT(*) alongside the sum shows instance count per type, which matters because a type with a high total spread across many small instances behaves differently than the same total concentrated in one instance — the former often reflects normal per-NUMA-node partitioning, the latter a single runaway consumer.

Computing percent of total with a window function

SUM(SUM(mc.pages_kb)) OVER () nests a window sum over the already-grouped result, producing the grand total across every clerk type without a second query or a self-join back to the ungrouped DMV — the same pattern used to compute buffer-pool share per database from sys.dm_os_buffer_descriptors. Dividing each type's total by that grand total and casting to DECIMAL(5,2) turns raw kilobyte figures into a percentage that reads directly: a clerk type sitting at 40% of total non-buffer-pool memory is a different conversation than one sitting at 4%, independent of the instance's absolute memory configuration.

Drilling into individual clerk instances

The second query skips the GROUP BY and orders raw rows by pages_kb descending, exposing name and memory_node_id per instance. This is the follow-up step once the rollup identifies a type worth investigating: for CACHESTORE_SQLCP or CACHESTORE_OBJCP (the ad hoc and object plan cache stores), the name column often carries additional detail about which cache store instance is involved; for a NUMA-partitioned server, memory_node_id shows whether memory pressure is concentrated on one node or spread evenly.

Recognizing key clerk types

A handful of clerk types account for most non-buffer-pool memory pressure investigations. MEMORYCLERK_SQLBUFFERPOOL is the buffer pool itself and is usually excluded when the question is specifically about pressure outside data caching. CACHESTORE_SQLCP and CACHESTORE_OBJCP hold the SQL plan cache and object plan cache respectively — a large CACHESTORE_SQLCP total is the classic signature of an application sending non-parameterized ad hoc queries instead of reusing plans. MEMORYCLERK_XTP tracks memory-optimized table and index structures for In-Memory OLTP; because those tables bypass the buffer pool entirely, this clerk is the only place their memory footprint is visible. MEMORYCLERK_SQLQERESERVATIONS reflects memory grants reserved for sort and hash operations in currently executing queries, and Query Store's internal caches — data persisted before it flushes to the msdb-adjacent Query Store storage — show up as their own clerk entries as well.

Key Benefits and Use Cases

  • Sees past the buffer pool — the only DMV that reports memory consumed by plan cache, XTP, Query Store, and query memory grants in one consistent unit.
  • Fast triage rollup — grouping by type turns a sprawling row-per-instance DMV into a short, ranked list a DBA can read in seconds.
  • Plan cache bloat detection — a disproportionately large CACHESTORE_SQLCP total flags applications generating excessive ad hoc, non-parameterized query plans.
  • In-Memory OLTP visibility — MEMORYCLERK_XTP is the only window into memory-optimized table and index memory consumption, since those structures don't appear in buffer pool DMVs at all.
  • NUMA distribution check — the detail query's memory_node_id column shows whether a given clerk's memory is spread evenly across nodes or concentrated on one.
  • No configuration required — available on every edition with VIEW SERVER STATE permission and no trace flags or setup.

Performance Considerations

  • Memory-only, resets on restart: like every sys.dm_os_* DMV, clerk data reflects the instance's current in-memory state. A service restart or failover zeroes every clerk, and totals rebuild only as subsystems allocate memory again under workload.
  • Requires VIEW SERVER STATE permission: querying sys.dm_os_memory_clerks needs VIEW SERVER STATE at minimum; on Azure SQL Database the scoped equivalent is VIEW DATABASE STATE and results are limited to the current database's visible clerks.
  • Clerk counts vary by NUMA topology: some clerk types instantiate once per NUMA node, so instance counts and per-type row counts differ across otherwise identical hardware configurations depending on node count.
  • Total clerk memory is not equal to max server memory: clerks report committed pages tracked by the memory manager, but some SQL Server memory consumption — certain thread stacks and non-buffer-pool allocations outside the memory manager's tracking — falls outside what clerks report. Cross-check against sys.dm_os_process_memory for the full process-level picture.
  • Plan cache clerks fluctuate with workload, not just leaks: a spike in CACHESTORE_SQLCP after a deployment or a burst of ad hoc reporting queries is often transient; distinguish a genuine leak from workload-driven growth by re-running the rollup after the burst subsides.

Practical Tips

  • Schedule the rollup query as a SQL Server Agent job writing to a history table at regular intervals, so a gradual climb in one clerk type over days or weeks is visible as a trend rather than a single point-in-time snapshot.
  • Cross-reference a high CACHESTORE_SQLCP total with sys.dm_exec_cached_plans filtered to usecounts = 1 to confirm the plan cache is genuinely bloated with single-use ad hoc plans before recommending optimize for ad hoc workloads.
  • When MEMORYCLERK_XTP shows unexpectedly high memory, pair this report with sys.dm_db_xtp_table_memory_stats to break the total down by individual memory-optimized table.
  • Run the detail query immediately before and after a suspected memory-hungry deployment or batch job to see which clerk's pages_kb grew, narrowing down the subsystem responsible without guesswork.
  • On instances approaching max server memory, watch for any single clerk type consistently claiming an outsized share of the non-buffer-pool total — that's the candidate to investigate first, whether through query tuning, Resource Governor limits, or an XTP memory budget review.

Conclusion

sys.dm_os_memory_clerks grouped by type closes the visibility gap the buffer pool DMVs leave open — it's the direct path to answering which internal SQL Server subsystem, from plan cache to In-Memory OLTP to Query Store, is consuming memory when the data cache isn't the story. Kept alongside buffer pool and process-memory queries in a DBA's toolkit, it turns "the instance feels memory-constrained" into a specific, named consumer worth investigating.

References

Posts in this series