Post reply

buffer and active page

  • November 13, 2018 at 9:17 am

    #368373

    Hello,

    1- what is active pages in buffer cache ?
    2-How to get in in MB  ?
    3-is there a way to know what is in SQL memory with size?
    I know  there is sys.dm_os_performance_counters with Buffer Manager I can may counters , but there is no way to see what is active as of now.

    Thanks

  • November 13, 2018 at 11:16 am

    #2013362

    When looking at SQL Server memory utilization, I tend to take a top down approach.  Here's the first query I run to see high level info:
    SELECT sm.total_physical_memory_kb / 1024 AS physical_mem,
      pm.physical_memory_in_use_kb / 1024 AS sql_physical_used,
      pm.locked_page_allocations_kb / 1024 as locked_in_mem,
      c.value_in_use AS config_max,
      sm.available_physical_memory_kb / 1024 AS avail_physical,
      sm.system_cache_kb / 1024 AS system_cache,
      pm.page_fault_count AS sql_page_faults, pm.memory_utilization_percentage AS mem_utilized_pct,
      sm.system_memory_state_desc
    FROM sys.dm_os_sys_memory sm
      CROSS JOIN sys.dm_os_process_memory pm
      CROSS JOIN sys.configurations c
    WHERE c.name = 'max server memory (MB)'

    Then to see how much memory is used by each database, you can use:
    SELECT
      (CASE WHEN ([database_id] = 32767) THEN N'Resource Database' ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
      COUNT (*) * 8 / 1024 AS [MBUsed],
      SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
    FROM sys.dm_os_buffer_descriptors
    GROUP BY [database_id]
    ORDER BY MBUsed DESC;

    If you then want to see object memory usage within a specific database, change SSMS to use that database then run:
    ;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 TOP 10 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;

  • November 19, 2018 at 9:10 am

    #2013973

    Thank you

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

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