Why is my SQL Server consuming so much memory?

Question

I have a 64GB Windows Server 2019 Standard server in my TEST Server.

I have SQL Server 2019 CU9 Developer Edition and my the instance is consuming 21,199 MB as shown in sql_physical_memory_in_use_MB.

I have 4 user databases. Their sizes are approximately 5GB, 2GB, 400MB, and 200MB.

My tempdb is configured to be 8GB at instance start-up.

I'm trying to understand why my instance is using 21GB of memory. Just worried about a possible memory leak.

Can anyone calm my nerves?

SELECT
    (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
    SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS DirtyPageCount,
    SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS CleanPageCount,
    count(*)AS TotalPageCount,
    cast(count(*) * 8192.0 / (1024.0 * 1024.0) as decimal(8,2)) as BufferPoolMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY TotalPageCount desc
GO

OUTPUT

Database Name	DirtyPageCount	CleanPageCount	TotalPageCount	BufferPoolMB
SFI_WMS	55	805665	805720	6294.69
tempdb	18648	210842	229490	1792.89
DBAtools	483	82035	82518	644.67
Resource Database	0	3896	3896	30.44
msdb	42	1898	1940	15.16
master	24	766	790	6.17
ReportServer	4	476	480	3.75
ReportServerTempDB	0	308	308	2.41
model	0	245	245	1.91

SELECT 
    physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, 
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, 
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, 
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, 
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage, 
    process_physical_memory_low AS sql_process_physical_memory_low, 
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory; 

OUTPUT

sql_physical_memory_in_use_MB	sql_large_page_allocations_MB	sql_locked_page_allocations_MB	sql_VAS_reserved_MB	sql_VAS_committed_MB	sql_VAS_available_MB	sql_page_fault_count	sql_memory_utilization_percentage	sql_process_physical_memory_low	sql_process_virtual_memory_low
21199	0	0	124464	21461	134093263	9673913	100	0	0

SELECT c.value, c.value_in_use
FROM sys.configurations c WHERE c.[name] = 'max server memory (MB)';

value	value_in_use
60288	60288

Answer 1

Why is my SQL Server consuming so much memory?

You're looking at total process memory, and one particular consumer of memory. To drill down from total process memory and get a high-level allocation of SQL Server's memory use use sys.dm_os_memory_clerks, eg:

select type,name, (pages_kb + virtual_memory_committed_kb + awe_allocated_kb) / 1024. committed_mb
from sys.dm_os_memory_clerks 
order by committed_mb desc 

or the old-school

dbcc memorystatus

If you see large amounts of process memory (large_page_allocations_kb + locked_page_allocations_kb + virtual_address_space_committed_kb) that can't be accounted for by the memory clerks, see if you have any OleDb drivers for linked server loaded in-process. They allocate memory outside of the SQL Server memory managers.

The two biggest consumers of memory are the the buffer pool and the lock manager. Both of these will allocate memory and not release it unless the system is under memory pressure.

MEMORYCLERK_SQLBUFFERPOOL Client-Default        9801MB
OBJECTSTORE_LOCK_MANAGER  Lock Manager : Node 0 6929MB

So there's your answer. Since it's started SQL Server has at some point grown the buffer pool to 9GB and the lock manager memory to 7GB, which accounts for enough of your 21GB to indicate that you don't really have a problem.

See related: memory used by Locks

Answer 2

I'm just worried why my instance size is 21GB. Just worried about a memory leak. Can anyone calm my nerves?

Calm down, this is NOT a memory leak. You might have either set max server memory somewhere around 21 GB or might have not set it all and SQL Server would have used 21 GB (which is lot less likely). Please also note once SQL Serve uses a memory portion it will not release unless it sees need of that memory by some other process or low memory notification is flagged by SQL resource monitor. This is how SQL Server memory code is written. You have 5.5 GB of total data and almost 4 times the memory at some point of time your all data will be in memory and SQL Server will not release memory as reading from memory is much much faster than that from disk.

Read Memory Management Architecture Guide