SQL Server is using 34 GB of RAM. But when query the Memory Consumption Report, the buffer pool size, and the ad-hoc query size, it only adds up to around 2 GB. What is the other 32 GB of RAM doing?
Pre-emptive: "You should cap the amount of RAM SQL Server can use." Lets say it is capped to
x. That simply changes my question to, "What is the other
x GB of RAM doing?"
I have an instance of SQL Server that is consuming 32 GB of RAM:
That's not 32 GB of virtual memory; it's actually consuming 32 GB of physical memory (on the RAM chips) - known as "working set".
And it's not like it is shared with some other process. Essentially all of that is private to SQL Sever:
- Private Working Set: 33,896,700 bytes
What is it doing with all that RAM?!
Bufferpool memory usage by database
So we query memory usage by database - as the buffer pool caches pages from the databases:
--Memory usage server wide ;WITH src AS ( SELECT database_id, COUNT_BIG(*) AS db_buffer_pages FROM sys.dm_os_buffer_descriptors --WHERE database_id BETWEEN 5 AND 32766 GROUP BY database_id ) SELECT CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END AS [Database Name], db_buffer_pages AS BufferPages, db_buffer_pages /128.0 AS BufferMB FROM src ORDER BY db_buffer_pages DESC OPTION(RECOMPILE, MAXDOP 1);
For a grand total of 4.5 MB of the 32 GB.
TempDB uses the most (1.4 MB), and the rest go down from there:
5 MB of 32 GB - doesn't account for much
Yes that might seem low - but that's likely because i called
DBCC DROPCLEANBUFFERS first.
Query plan cache
Next we query the Query Plan Cache. All those T-SQL statements have to be compiled into a huge plan, and those plans are cached in RAM.
--Server-wide memory usage of plan cache SELECT [cacheobjtype], ObjType, COUNT(1) AS Plans, SUM(UseCounts) AS UseCounts, SUM(CAST(size_in_bytes AS real)) / 1024.0 / 1024 AS [SizeMB] FROM sys.dm_exec_cached_plans --where [cacheobjtype] = 'Compiled Plan' and [objtype] in ('Adhoc', 'Prepared') GROUP BY CacheObjType, ObjType ORDER BY SizeMB DESC OPTION(RECOMPILE, MAXDOP 1)
Now we can see how much memory is used to store various query plans:
For a grand total of 250 KB - far short of the missing 32 GB.
Note: Yes that might seem low - but that's likely because i called
Memory Consumption Report
The above queries show me RAM used by:
- the buffer pool (to cache in memory database pages from disk)
- the query plan cache
And that's really all there is. But SQL Server does provide a Memory Consumption Report:
This report provides detailed data on the memory consumption of components within the Instance
Narrator: "It doesn't"
The report is a little hard to read:
But in the end the breakdown is:
- MEMORYCLERK_SOSNODE: 131,832 KB
- MEMORYCLERK_SOSMEMMANAGER: 71,464 KB
- USERSTORE_DBMETADATA: 67,432 KB
- USERSTORE_SCHEMAMGR: 55,784 KB
- MEMORYCLERK_SQLSTORENG: 54,280 KB
- MEMORYCLERK_SQLBUFFERPOOL: 30,576 KB
- Others: 145,056 KB
That gives a grand total of: 556,424 KB → 544 MB
Even if we round that up to 1 GB: it's still a far cry from 32 GB.
So where is the memory going?
Yes, i can cap SQL Server to 25 GB of RAM. But then that would just change my question to:
What is SQL Server is using 25 GB of RAM for; where is the memory going?
Because this sounds an awful lot like a memory leak to me.
- Server: SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Can query for server up-time (creation of tempdb):
--Use creation date of tempdb as server start time
SELECT SERVERPROPERTY('SERVERNAME') AS ServerName, create_date AS ServerStartedDate FROM sys.databases WHERE NAME='tempdb';
- Server Started Date:
SELECT * FROM sys.assemblies
|Microsoft.SqlServer.Types||4||1||microsoft.sqlserver.types, version=220.127.116.11, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil||3||UNSAFE_ACCESS||1||2012-02-10 20:15:58.843||2012-02-10 20:15:59.427||0|
select provider, provider_string from sys.servers
|DB2OLEDB||Package Collection=▒▒▒▒▒▒▒▒;Network Address=▒▒▒▒▒;Network Port=50000;Connection Timeout=0;|