Does Resource Monitor report SQL Server memory usage correctly

Question

Does Resource Monitor report SQL Server memory usage correctly

coolchaitu

SSChampion

Points: 10334
More actions
November 8, 2017 at 9:36 am

#326156

Good Morning Experts,
Does Resource Monitor report SQL Server memory usage correctly? If no, then which one reports sql server memory usage correctly.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Arsh SSCertifiable Points: 6354 More actions · Answer 1

coolchaitu - Wednesday, November 8, 2017 9:36 AM
Good Morning Experts,
Does Resource Monitor report SQL Server memory usage correctly? If no, then which one reports sql server memory usage correctly.

I suppose querying SQL itself will be best to get accurate values . Why do you suspect Resource Monitor ?
Can use this script to get buffer pool usage directly from SQL server :

DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value   FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'   AND counter_name = 'Total Pages';
;WITH src AS(   SELECT        database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors       --WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id)SELECT   [db_name] = CASE [database_id] WHEN 32767        THEN 'Resource DB'        ELSE DB_NAME([database_id]) END,   db_buffer_pages,   db_buffer_MB = db_buffer_pages / 128,   db_buffer_percent = CONVERT(DECIMAL(6,3),        db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

--then drill down into memory used by objects in database of your choice

USE DBNAME;

WITH src AS(   SELECT       [Object] = o.name,       [Type] = o.type_desc,       [Index] = COALESCE(i.name, ''),       [Index_Type] = i.type_desc,       p.[object_id],       p.index_id,       au.allocation_unit_id
FROM       sys.partitions AS p   INNER JOIN       sys.allocation_units AS au       ON p.hobt_id = au.container_id   INNER JOIN       sys.objects AS o       ON p.[object_id] = o.[object_id]   INNER JOIN       sys.indexes AS i       ON o.[object_id] = i.[object_id]       AND p.index_id = i.index_id   WHERE       au.[type] IN (1,2,3)       AND o.is_ms_shipped = 0)
SELECT   src.[Object],   src.[Type],   src.[Index],   src.Index_Type,   buffer_pages = COUNT_BIG(b.page_id),   buffer_mb = COUNT_BIG(b.page_id) / 128
FROM   src
INNER JOIN   sys.dm_os_buffer_descriptors AS b
ON src.allocation_unit_id = b.allocation_unit_id
WHERE   b.database_id = DB_ID()
GROUP BY   src.[Object],   src.[Type],   src.[Index],   src.Index_Type
ORDER BY   buffer_pages DESC;

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Arsh - Thursday, November 9, 2017 6:49 AM

I suppose querying SQL itself will be best to get accurate values . Why do you suspect Resource Monitor ?
Can use this script to get buffer pool usage directly from SQL server :

Buffer pool usage would not be more accurate as it is only part of SQL Server's memory usage.
There are numerous things consuming memory outside of the buffer pool. Some of this is explained in the documentation for memory server configurations - which also explains why it is not unusual to see SQL Server consuming more than the max memory settings:
Server Memory Server Configuration Options
max server memory controls the SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and clr memory (essentially any memory clerk found in sys.dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked server providers other than SQL Server, and any memory allocated by a non SQL Server DLL are not controlled by max server memory.

Sue