How to find memory usage by table? [duplicate]

Question

This question already has an answer here:

How much memory is a table taking up? (1 answer)

Closed 12 years ago.

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

• Hogan: msdn.microsoft.com/en-us/library/ms176018.aspx
• Alec.: @Hogan Brilliant!
• Alex K.: A table is not an atomic object; all/part/parts of it may be present in the caches at any given time. What specifically do you want to know?
• Mehmet Akyel: @Hogan hahaa no man :) I want to learn used memory by e.g "BranchOffice" Table in BANK database
• Mehmet Akyel: @AlexK. Just want to learn used memory by all tables.Any query ?

Answer 1

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 :

• A quick look at: sys.dm_os_buffer_descriptors
• What's swimming in your bufferpool?
• sys.dm_os_buffer_descriptors aggregations -- more detailed info - what objects/structures/files/etc. are consuming the largest amount of space at a given time (or over time) within the Sql Server buffer pool.

Answer 2

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;