sys.dm_os_memory_clerks - Usage & Examples

Memory clerks are SQL Server's internal accounting mechanism for tracking memory allocations across different subsystems. Each clerk manages memory for specific components: MEMORYCLERK_SQLBUFFERPOOL handles data page caching, MEMORYCLERK_SQLOPTIMIZER manages query compilation memory, MEMORYCLERK_SQLCONNECTIONPOOL tracks connection overhead, and dozens of others track everything from XML parsing to full-text indexing.

The memory manager allocates memory to clerks based on demand and configured limits. When memory pressure occurs, SQL Server's Resource Monitor examines clerk allocations to determine which components should release memory first. Clerks with lower priorities or excessive allocations become targets for memory trimming through the DBCC MEMORYSTATUS and internal memory pressure responses.

SQL Server 2012 introduced significant changes to memory clerk accounting, consolidating many smaller clerks and improving visibility into memory allocations. SQL Server 2016 added query store memory tracking through dedicated clerks. SQL Server 2019 enhanced memory grant feedback mechanisms that directly impact optimizer and execution-related clerk allocations.

Buffer pool extensions in SQL Server 2014+ create additional clerk entries for SSD-backed buffer pool storage. In-Memory OLTP workloads since SQL Server 2014 generate substantial MEMORYCLERK_XTP allocations that don't follow traditional buffer pool memory management rules. SQL Server 2022's memory-optimized tempdb metadata creates new clerk categories for system database memory management.

-- Identify top memory consuming clerks by allocated memory
SELECT TOP 20
    type,
    name,
    memory_node_id,
    pages_kb,
    virtual_memory_reserved_kb,
    virtual_memory_committed_kb,
    awe_allocated_kb,
    shared_memory_reserved_kb,
    shared_memory_committed_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;

-- Monitor memory clerk changes over time (run periodically)
SELECT 
    type,
    SUM(pages_kb) AS total_pages_kb,
    COUNT(*) as clerk_count,
    AVG(pages_kb) as avg_pages_per_clerk,
    MAX(pages_kb) as max_pages_single_clerk
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 0
GROUP BY type
ORDER BY total_pages_kb DESC;

-- Identify clerks with significant virtual memory reservations
SELECT 
    type,
    name,
    pages_kb,
    virtual_memory_reserved_kb,
    virtual_memory_committed_kb,
    (virtual_memory_reserved_kb - virtual_memory_committed_kb) AS unreserved_virtual_kb,
    CAST(virtual_memory_committed_kb AS FLOAT) / 
        NULLIF(virtual_memory_reserved_kb, 0) * 100 AS commit_ratio_pct
FROM sys.dm_os_memory_clerks
WHERE virtual_memory_reserved_kb > 10240  -- > 10MB reserved
ORDER BY virtual_memory_reserved_kb DESC;

-- Analyze memory clerk distribution across NUMA nodes
SELECT 
    memory_node_id,
    type,
    COUNT(*) as clerk_instances,
    SUM(pages_kb) as total_memory_kb,
    AVG(pages_kb) as avg_memory_per_instance
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 0
GROUP BY memory_node_id, type
HAVING COUNT(*) > 1  -- Multiple instances of same clerk type
ORDER BY memory_node_id, total_memory_kb DESC;

-- Correlate high memory clerks with current query activity
SELECT 
    mc.type,
    mc.name,
    mc.pages_kb,
    COUNT(r.session_id) as active_sessions,
    SUM(r.granted_query_memory) as total_granted_query_memory_kb
FROM sys.dm_os_memory_clerks mc
LEFT JOIN sys.dm_exec_requests r ON r.session_id > 50  -- Exclude system sessions
WHERE mc.pages_kb > 50000  -- Focus on large allocations > 50MB
GROUP BY mc.type, mc.name, mc.pages_kb
ORDER BY mc.pages_kb DESC;

Identify and Clear Excessive Plan Cache Memory This script identifies plan cache clerks consuming excessive memory and provides safe cache clearing options.

-- First identify plan cache memory usage
SELECT 
    type,
    pages_kb,
    pages_kb / 1024 AS pages_mb
FROM sys.dm_os_memory_clerks 
WHERE type LIKE '%CACHESTORE%' 
ORDER BY pages_kb DESC;

-- Clear specific cache stores if excessive (TEST IN DEV FIRST)
-- DBCC FREESYSTEMCACHE('SQL Plans');  -- Clears plan cache
-- DBCC FREESYSTEMCACHE('Object Plans'); -- Clears object plans
-- DBCC FREESYSTEMCACHE('Bound Trees'); -- Clears bound trees

-- Alternative: Clear single plan cache entry
-- DBCC FREEPROCCACHE(plan_handle); -- Use specific plan_handle from dm_exec_cached_plans

Caveats: Clearing plan cache causes immediate performance degradation as queries recompile. Monitor CPU usage after execution. Clear during maintenance windows only.

Configure Buffer Pool Extension for Memory Pressure Relief Reduces buffer pool clerk pressure by offloading clean pages to SSD storage.

-- Check current buffer pool extension status
SELECT 
    path,
    state_description,
    current_size_in_kb,
    current_size_in_kb / 1024 / 1024 AS current_size_gb
FROM sys.dm_os_buffer_pool_extension_configuration;

-- Enable buffer pool extension (requires SQL Server 2014+ Standard/Enterprise)
-- ALTER SERVER CONFIGURATION 
-- SET BUFFER POOL EXTENSION ON 
--     (FILENAME = 'F:\BufferPoolExtension\BufferPoolExtension.BPE', 
--      SIZE = 32 GB);

-- Monitor buffer pool extension effectiveness using sys.dm_os_buffer_descriptors
-- (is_in_bpool_extension identifies pages residing on the BPE file)
SELECT 
    database_id,
    file_id,
    page_id,
    page_type,
    page_level,
    allocation_unit_id,
    is_in_bpool_extension
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID()
  AND is_in_bpool_extension = 1;

Caveats: Requires fast SSD storage. BPE files cannot be compressed or encrypted. Size should be 1-16x physical RAM. Test performance impact in non-production first.

Optimize Memory-Intensive Query Workloads Identifies and addresses queries causing excessive optimizer memory clerk allocations.

-- Identify queries with high memory grants contributing to clerk pressure
SELECT TOP 10
    t.text,
    qp.query_plan,
    r.session_id,
    r.granted_query_memory,
    r.used_query_memory,
    r.max_used_query_memory,
    r.dop
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.granted_query_memory > 10240  -- > 10MB grants
ORDER BY r.granted_query_memory DESC;

-- Set Resource Governor to limit query memory grants per workload group
-- CREATE RESOURCE POOL limited_memory_pool 
-- WITH (MAX_MEMORY_PERCENT = 70);  -- Limit pool to 70% of server memory

Caveats: Resource Governor requires Enterprise Edition. Memory limit changes affect all queries in the workload group. Monitor for query timeout increases.

Configure max server memory to leave adequate memory for OS and other applications, preventing SQL Server from consuming all available system memory and forcing aggressive memory clerk trimming. Set max server memory to total_RAM minus 4GB for OS on systems with 16GB+ RAM.

Enable Resource Governor in Enterprise Edition to control memory grants for specific workload groups. Create separate pools for OLTP and analytical workloads with appropriate memory limits. Configure classifier functions to route high-memory queries to dedicated resource pools with controlled memory allocations.

Monitor memory clerk growth trends using automated scripts that capture sys.dm_os_memory_clerks snapshots every 15 minutes. Alert when any single clerk type exceeds 20% of max server memory or when buffer pool memory drops below 60% of max server memory allocation. Set up proactive plan cache clearing during maintenance windows when cache stores exceed 2GB.

Implement query memory grant feedback in SQL Server 2017+ by enabling intelligent query processing features. Configure database compatibility level to 150+ to leverage adaptive memory grants that reduce excessive optimizer memory clerk allocations. Use Query Store to identify problematic query patterns that consistently request excessive memory grants.

Configure buffer pool extension on high-IOPS SSD storage to reduce memory pressure on buffer pool clerks. Size the extension file between 1x and 4x physical RAM based on working set characteristics. Monitor buffer pool hit ratios and page life expectancy to validate effectiveness.