SQL Server Memory Leak: Diagnosing and Fixing 99% RAM Usage with Hidden Process Consumption


4 views

When your SQL Server shows 99% memory usage but the sqlservr.exe process only reports minimal consumption (like 100K in your case), you're dealing with one of SQL Server's more perplexing memory management behaviors. The key insight is that SQL Server uses Windows' Working Set memory differently than Task Manager displays.

-- Check actual SQL Server memory usage with this query:
SELECT
    physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],
    locked_page_allocations_kb/1024 AS [Locked Pages Allocation (MB)],
    large_page_allocations_kb/1024 AS [Large Pages Allocation (MB)]
FROM sys.dm_os_process_memory;

SQL Server's buffer pool often accounts for the "missing" memory. Unlike regular processes, SQL Server allocates memory through the Windows AWE (Address Windowing Extensions) API when Lock Pages in Memory privilege is enabled. This bypasses standard process memory reporting.

-- Identify memory clerks (actual memory consumers):
SELECT TOP 10 
    type AS [Memory Clerk Type],
    pages_kb/1024 AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;

The slowdown occurs when SQL Server's memory demand exceeds available physical RAM, forcing paging. Check these critical thresholds:

-- Monitor memory pressure signals
SELECT
    memory_utilization_percentage,
    process_physical_memory_low,
    process_virtual_memory_low
FROM sys.dm_os_process_memory;

Instead of service restarts, try these targeted solutions:

-- Release memory without restart (SQL Server 2012+)
DBCC FREESYSTEMCACHE('ALL');
DBCC FREESESSIONCACHE;
DBCC FREEPROCCACHE;

-- Set max server memory to leave room for OS (adjust values)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 6144; -- 6GB for 8GB system
RECONFIGURE;

For persistent leaks, investigate these areas:

  • Check for memory-optimized tables consuming lock pages
  • Review recent query plan changes with excessive memory grants
  • Verify columnstore index rebuild schedules
-- Find memory-greedy queries
SELECT TOP 10
    query_hash,
    total_logical_reads,
    total_elapsed_time,
    execution_count,
    last_execution_time
FROM sys.dm_exec_query_stats
ORDER BY total_logical_reads DESC;

Genuine memory leaks in SQL Server are rare but possible with:

  • Custom CLR assemblies
  • Buggy third-party extended stored procedures
  • Certain SSIS package executions
-- Check for active CLR allocations
SELECT 
    clr_allocated_bytes/1024 AS [CLR Memory (KB)],
    clr_allocation_count
FROM sys.dm_clr_appdomains;

When SQL Server consumes 99% of system memory while showing minimal process-specific usage in Task Manager, we're dealing with a classic case of memory pressure with hidden allocation patterns. The key indicators are:

  • Service restart temporarily resolves the issue (memory drops to ~5%)
  • Gradual memory buildup over ~2 hours
  • Severe query performance degradation at peak usage
  • Discrepancy between Task Manager reports and actual memory pressure

The 100K RAM reporting in Task Manager is misleading because SQL Server uses Windows' MEM_RESERVE allocations that don't show in standard process metrics. To see the real picture, run:

SELECT
    physical_memory_in_use_kb/1024 AS [SQL Used Memory (MB)],
    locked_page_allocations_kb/1024 AS [SQL Locked Pages (MB)],
    large_page_allocations_kb/1024 AS [Large Pages (MB)],
    virtual_address_space_committed_kb/1024 AS [SQL Committed (MB)]
FROM sys.dm_os_process_memory;

First identify memory consumers within SQL Server:

-- Buffer Pool Usage
SELECT TOP 10
    COUNT(*)AS [Pages],
    (COUNT(*)*8)/1024 AS [MB Used],
    CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE DB_NAME(database_id)
    END AS [Database]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY [Pages] DESC;

-- Memory Clerks
SELECT TOP 10
    [type] AS [Memory Clerk],
    SUM(pages_kb)/1024 AS [MB Used]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC;

For an 8GB server, these settings often help:

-- Set max server memory (leave 1-2GB for OS)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 6144; -- 6GB
RECONFIGURE;

-- Enable Lock Pages in Memory (requires Windows policy)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'awe enabled', 1;
RECONFIGURE;

Create an extended events session to capture memory changes:

CREATE EVENT SESSION [Memory_Tracking] ON SERVER
ADD EVENT sqlserver.memory_node_oom_ring_buffer_recorded,
ADD EVENT sqlserver.memory_node_out_of_memory,
ADD EVENT sqlserver.memory_usage_increase,
ADD EVENT sqlserver.memory_usage_decrease
ADD TARGET package0.event_file(SET filename=N'Memory_Tracking')
WITH (MAX_MEMORY=4096KB, MAX_DISPATCH_LATENCY=30 SECONDS);
GO

ALTER EVENT SESSION [Memory_Tracking] ON SERVER STATE = START;
GO

Identify problematic queries with high memory grants:

SELECT TOP 20
    qs.execution_count,
    qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
    qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
    qs.total_worker_time/qs.execution_count AS [Avg CPU Time],
    qs.max_logical_reads,
    qs.max_elapsed_time,
    qs.max_worker_time,
    SUBSTRING(qt.text,(qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) AS [Query Text]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.max_logical_reads DESC;