How to find memory usage by table? [duplicate]

Posted on

Question :

I want to discover how much memory is being used by each table. Is this information available in DMVs or elsewhere?

Answer :

what you can do is

  1. Find out the database that consumes highest memory in buffer pool using below :

    SELECT COUNT(*) AS cached_pages_count , 
        ( COUNT(*) * 8.0 ) / 1024 AS MB , 
        CASE database_id 
          WHEN 32767 THEN 'ResourceDb' 
          ELSE DB_NAME(database_id) 
        END AS Database_name 
    FROM sys.dm_os_buffer_descriptors 
    GROUP BY database_id
    
  2. Drill down the database using below query :

    set nocount on;
    set transaction isolation level read uncommitted;
    select
       count(*)as cached_pages_count,
       (COUNT(*) * 8.0) / 1024 AS Total_MB_Occupied, -- convert pages into MB -  the page size is 8 KB for sql server
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
    from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
    left outer join sys.indexes ind 
    on  obj.objectid = ind.object_id
    and  obj.index_id = ind.index_id
    where bd.database_id = db_id()
    and bd.page_type in ('data_page', 'index_page')
    group by obj.name, ind.name, obj.index_id
    order by cached_pages_count desc
    

Reference :

From my tip here:

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

Leave a Reply

Your email address will not be published.