Sql-server – Why is the SQL Server consuming so much memory

memorysql serversql-server-2019

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

Best Answer

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

Related Question