sys.dm_os_process_memory - Usage & Examples

sys.dm_os_process_memory exposes the Windows process memory counters for the SQL Server service process (sqlservr.exe). The DMV queries the Windows API GetProcessMemoryInfo() function to retrieve real-time memory statistics from the OS kernel's process control block.

The physical_memory_in_use_kb column reflects the working set, which represents pages currently resident in physical RAM. This includes both buffer pool pages and non-buffer pool allocations like CLR assemblies, linked server OLE DB providers, extended stored procedures, and thread stacks. The virtual_memory_committed_kb shows committed virtual address space, including paged and non-paged allocations.

SQL Server 2012 introduced significant changes to memory management with the "Any Size Buffer" allocator, affecting how non-buffer pool memory appears in this DMV. Prior versions showed more predictable ratios between working set and committed memory. SQL Server 2016 added query memory grant improvements that reduce virtual memory fragmentation visible in this view.

The available_commit_limit_kb represents the maximum virtual memory the process could theoretically commit based on system commit charge limits and the process virtual address space. On 64-bit systems, this is typically constrained by system commit charge rather than virtual address space exhaustion.

Memory pressure scenarios manifest differently in this DMV depending on the source. Buffer pool pressure shows stable or decreasing physical_memory_in_use_kb while virtual_memory_committed_kb remains high. Non-buffer pool leaks show both values climbing together. External memory pressure forces SQL Server's working set out of physical RAM, creating a gap between committed and physical memory usage.

-- Current process memory utilization
SELECT 
    physical_memory_in_use_kb / 1024 AS [Physical Memory MB],
    virtual_memory_committed_kb / 1024 AS [Virtual Memory MB], 
    available_commit_limit_kb / 1024 AS [Available Commit Limit MB],
    process_physical_memory_low,
    process_virtual_memory_low
FROM sys.dm_os_process_memory;

-- Memory utilization trend over time with perfmon counters
SELECT 
    pm.physical_memory_in_use_kb / 1024 AS [Current Physical MB],
    ppc.cntr_value / 1024 AS [Target Server Memory MB],
    ppc2.cntr_value / 1024 AS [Total Server Memory MB],
    CASE WHEN pm.process_physical_memory_low = 1 THEN 'LOW' ELSE 'OK' END AS [Memory Pressure]
FROM sys.dm_os_process_memory pm
CROSS JOIN sys.dm_os_performance_counters ppc
CROSS JOIN sys.dm_os_performance_counters ppc2
WHERE ppc.object_name = 'SQLServer:Memory Manager'
    AND ppc.counter_name = 'Target Server Memory (KB)'
    AND ppc2.object_name = 'SQLServer:Memory Manager' 
    AND ppc2.counter_name = 'Total Server Memory (KB)';

-- Compare process memory to buffer pool allocation
SELECT 
    pm.physical_memory_in_use_kb / 1024 AS [Process Physical MB],
    pm.virtual_memory_committed_kb / 1024 AS [Process Virtual MB],
    (SELECT SUM(pages_kb) FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL') / 1024 AS [Buffer Pool MB],
    (pm.virtual_memory_committed_kb - (SELECT SUM(pages_kb) FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL')) / 1024 AS [Non-Buffer Pool MB]
FROM sys.dm_os_process_memory pm;

-- Memory pressure indicators with system state
SELECT 
    pm.process_physical_memory_low,
    pm.process_virtual_memory_low,
    si.committed_kb / 1024 AS [System Committed MB],
    si.commit_limit_kb / 1024 AS [System Commit Limit MB],
    CAST((si.committed_kb * 100.0 / si.commit_limit_kb) AS DECIMAL(5,2)) AS [Commit Utilization %]
FROM sys.dm_os_process_memory pm
CROSS JOIN sys.dm_os_sys_info si;

-- Historical memory clerk breakdown for non-buffer pool analysis
SELECT TOP 20
    type,
    SUM(pages_kb) / 1024 AS [Memory Used MB],
    COUNT(*) AS [Clerk Count]
FROM sys.dm_os_memory_clerks
WHERE type NOT IN ('MEMORYCLERK_SQLBUFFERPOOL')
GROUP BY type
ORDER BY SUM(pages_kb) DESC;

Identify memory-consuming sessions causing process memory growth

-- Find sessions with high memory grants contributing to virtual memory usage
-- granted_query_memory is expressed in 8KB pages on sys.dm_exec_requests
SELECT TOP 10
    s.session_id,
    s.login_name,
    r.granted_query_memory * 8 / 1024 AS [Granted Memory MB],
    t.text,
    r.status,
    r.wait_type
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.granted_query_memory > 0
ORDER BY r.granted_query_memory DESC;
-- Use this to identify runaway queries holding excessive memory grants
-- Kill problematic sessions after validating they are not critical processes

Reconfigure max server memory when process memory exceeds expectations

-- Calculate and apply appropriate max server memory setting
DECLARE @TotalRAM_MB INT, @RecommendedMax_MB INT;
-- physical_memory_kb is the correct column on sys.dm_os_sys_info (SQL Server 2012+)
SELECT @TotalRAM_MB = physical_memory_kb / 1024 FROM sys.dm_os_sys_info;
SET @RecommendedMax_MB = @TotalRAM_MB - CASE 
    WHEN @TotalRAM_MB <= 4096 THEN 1024
    WHEN @TotalRAM_MB <= 8192 THEN 2048  
    WHEN @TotalRAM_MB <= 16384 THEN 4096
    ELSE 6144 END;

PRINT 'Current Total RAM: ' + CAST(@TotalRAM_MB AS VARCHAR(10)) + ' MB';
PRINT 'Recommended Max Server Memory: ' + CAST(@RecommendedMax_MB AS VARCHAR(10)) + ' MB';

-- EXEC sp_configure 'max server memory (MB)', @RecommendedMax_MB;
-- RECONFIGURE;
-- Uncomment above lines after validating the recommendation
-- This reduces buffer pool size to control total process memory

Force memory cleanup for non-buffer pool allocations

-- Clear procedure cache and free unused memory clerks
-- WARNING: This will cause recompilation of all cached plans
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE('ALL');

-- Review any CLR assemblies that may be contributing to non-buffer-pool memory
IF EXISTS (SELECT 1 FROM sys.assemblies WHERE name != 'Microsoft.SqlServer.Types')
BEGIN
    PRINT 'CLR assemblies detected - review MEMORYCLERK_SQLCLR usage and investigate leaks';
END;
-- Use this during maintenance windows to reclaim fragmented non-buffer pool memory
-- Monitor sys.dm_os_process_memory before and after to measure impact

Enable trace flags for memory diagnostic enhancement

-- Enable detailed memory allocation tracking
-- DBCC TRACEON(8666, -1);  -- Enables additional memory clerk reporting
-- DBCC TRACEON(2861, -1);  -- Keeps zero-cost plans in cache (reduces recompilation overhead)

-- Check current trace flag status
DBCC TRACESTATUS(-1);

-- These trace flags provide enhanced visibility into memory clerk behavior
-- Test in development first as trace flag 8666 adds overhead
-- Use only during active memory troubleshooting periods

Configure max server memory to leave adequate RAM for the operating system and other applications. The formula varies by total RAM: leave 1GB for systems with 4GB RAM, scaling up to 6GB reserved for systems with 64GB+ RAM. Monitor the ratio between virtual_memory_committed_kb and physical_memory_in_use_kb; ratios above 1.5:1 indicate potential virtual memory fragmentation.

Implement automated monitoring of process_physical_memory_low and process_virtual_memory_low flags. These indicate operating system memory pressure affecting SQL Server performance. Set up alerts when physical_memory_in_use_kb exceeds 90% of max server memory setting plus 2GB overhead for non-buffer pool allocations.

Review non-buffer pool memory clerks monthly using sys.dm_os_memory_clerks to identify growing allocations from CLR assemblies, linked servers, or third-party components. Establish baselines for MEMORYCLERK_SQLCLR, MEMORYCLERK_SQLOLEDB, and MEMORYCLERK_XP memory usage patterns.

Configure Resource Governor workload groups with memory_percent limits for applications prone to excessive query memory grants. This prevents individual queries from consuming disproportionate virtual memory through sorting and hash operations. Enable query store to identify queries with historically high memory grant requirements.

Schedule monthly DBCC FREEPROCCACHE operations during maintenance windows to prevent procedure cache fragmentation from accumulating in virtual memory. Consider implementing automated cache clearing for development environments where ad-hoc query patterns create cache bloat.