Why is my SQL Server consuming so much memory?

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_mem

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

StackExchange Database Administrators Q#286637, answer score: 2