Is MEMORYCLERK_SQLBUFFERPOOL how much RAM I'm actually using or how much I could be using?
4
votes
2
answers
535
views
This lovely query of Glenn Berry's lets you see what is in your buffer pool.
SELECT fg.name AS [Filegroup Name], SCHEMA_NAME(o.Schema_ID) AS [Schema Name],
OBJECT_NAME(p.[object_id]) AS [Object Name], p.index_id,
CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
COUNT(*) AS [BufferCount], p.[Rows] AS [Row Count],
p.data_compression_desc AS [Compression Type]
FROM sys.allocation_units AS a WITH (NOLOCK)
INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON a.container_id = p.hobt_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
ON p.object_id = o.object_id
INNER JOIN sys.database_files AS f WITH (NOLOCK)
ON b.file_id = f.file_id
INNER JOIN sys.filegroups AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id
WHERE b.database_id = CONVERT(int, DB_ID())
AND p.[object_id] > 100
AND OBJECT_NAME(p.[object_id]) NOT LIKE N'plan_%'
AND OBJECT_NAME(p.[object_id]) NOT LIKE N'sys%'
AND OBJECT_NAME(p.[object_id]) NOT LIKE N'xml_index_nodes%'
GROUP BY fg.name, o.Schema_ID, p.[object_id], p.index_id,
p.data_compression_desc, p.[Rows]
ORDER BY [BufferCount] DESC OPTION (RECOMPILE);
[Erik Darling offers a similar query](https://github.com/erikdarlingdata/DarlingData/blob/main/Helper%20Views/WhatsUpMemory.sql) . I've ran both against all of my databases **and summed the results**. They both agree that, in total, I'm using about 40 GB of memory for my buffer pool across all of my databases. Yet,
SELECT TOP(10) mc.[type] AS [Memory Clerk Type],
CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type]
ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);
reports that my buffer pool (MEMORYCLERK_SQLBUFFERPOOL
) is using 200 GB of memory.
These can't both be true at the same time; My buffer pool cannot be both 40 GB in size and 200 GB in size. So what do these two numbers actually mean? Is MEMORYCLERK_SQLBUFFERPOOL
how much RAM I'm actually using or is it how much I could be using?
Asked by J. Mini
(1237 rep)
Mar 27, 2025, 08:31 PM
Last activity: Mar 31, 2025, 04:03 PM
Last activity: Mar 31, 2025, 04:03 PM