Does Resource Monitor report SQL Server memory usage correctly

  • Good Morning Experts,

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

  • 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;
  • 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

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

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