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 lists every active memory clerk — buffer pool, plan cache, In-Memory OLTP, Query Store, query memory grants, Extended Events — and reports pages_kb per clerk. Grouping those rows by type produces a ranked list of which internal SQL Server subsystem is holding the most memory right now.

Purpose and Overview

In-Memory OLTP, available since SQL Server 2014, keeps every row and index of a memory-optimized table in memory and allocates that memory through its own clerk, MEMORYCLERK_XTP, so a DBA watching only buffer pool metrics can miss 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 active memory clerk. Every component that allocates a significant amount of memory creates its own clerk and allocates through the memory node interfaces, which also track the memory for diagnostics. The key column is pages_kb — the amount of page memory, in kilobytes, allocated for that clerk. A component can have several clerks of one type, and each row carries a memory_node_id, so a raw SELECT * FROM sys.dm_os_memory_clerks on a busy instance returns many more rows than there are clerk types. Aggregating pages_kb by clerk type collapses that instance-level detail into a short, ranked 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 page-memory column; the DMV also has virtual_memory_reserved_kb, virtual_memory_committed_kb, awe_allocated_kb and shared-memory columns for allocations made outside the page allocator, and it returns no pre-aggregated total. 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 several clerks of the same type, the latter a single large 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. 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 clerk memory is a different conversation than one sitting at 4%, independent of the instance's absolute memory configuration. The total includes the buffer pool clerk, because the query has no WHERE filter; add WHERE mc.type <> 'MEMORYCLERK_SQLBUFFERPOOL' to the rollup if you want shares of the non-buffer-pool memory only.

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 carries the internally assigned name that tells clerks of the same type apart; on a server with several memory nodes, memory_node_id shows whether memory is concentrated on one node or spread evenly.

Recognizing key clerk types

A handful of clerk types account for most non-buffer-pool memory investigations. MEMORYCLERK_SQLBUFFERPOOL tracks data and index pages — Microsoft calls it commonly the largest memory consumer inside SQL Server — and it will usually top the rollup; filter it out when the question is specifically about pressure outside data caching. CACHESTORE_SQLCP caches ad hoc queries, prepared statements and server-side cursors, and CACHESTORE_OBJCP caches compiled plans for stored procedures, functions and triggers. A large CACHESTORE_SQLCP total is the classic signature of an application sending non-parameterized ad hoc queries instead of reusing plans. MEMORYCLERK_XTP covers In-Memory OLTP memory allocations. MEMORYCLERK_SQLQERESERVATIONS reflects memory grants for sort and hash operations in executing queries. Query Store allocates through its own clerks (MEMORYCLERK_QUERYDISKSTORE, CACHESTORE_QDSRUNTIMESTATS, USERSTORE_QDSSTMT), and Extended Events through MEMORYCLERK_XE.

Key Benefits and Use Cases

  • Sees past the buffer pool — reports memory consumed by plan cache, XTP, Query Store, and query memory grants in one view and one unit (pages_kb).
  • 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 gives the instance-wide total for In-Memory OLTP allocations; sys.dm_db_xtp_table_memory_stats breaks memory down per table.
  • Memory node 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.
  • Plain T-SQL, no setup step — a single query against a DMV, needing only the permission described under Performance Considerations.

Performance Considerations

  • Point-in-time view: the DMV returns the clerks currently active in the instance, with no history. Save snapshots to a table if you need a trend, as described under Practical Tips.
  • Permissions: on SQL Server 2019 and earlier, querying sys.dm_os_memory_clerks requires VIEW SERVER STATE; on SQL Server 2022 and later it requires VIEW SERVER PERFORMANCE STATE. On Azure SQL Database, the Basic, S0 and S1 objectives and elastic pool databases require the server admin or Microsoft Entra admin account; other objectives require VIEW DATABASE STATE.
  • Clerk counts vary by instance: a component can have several clerks of one type, and each row carries a memory_node_id, so row counts per type differ across instances depending on workload and the number of memory nodes.
  • Clerks do not account for every byte the process uses: sys.dm_os_process_memory notes that allocations might be made in the SQL Server address space that bypass internal memory management routines, and the Memory Management Architecture Guide lists thread stacks, extended procedure DLLs and memory allocated by non-SQL Server DLLs as outside max server memory. Cross-check against sys.dm_os_process_memory for the 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 the optimize for ad hoc workloads option, which caches a small plan stub instead of the full plan on first compile.
  • 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 non-buffer-pool clerk type consistently claiming an outsized share of clerk memory — 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