How to see what is cached in memory in SQL server 2008?

Question

Is there a way how to find out what is cached in SQL Server 2008 R2? I have found the following nice article: http://blog.sqlauthority.com/2010/06/17/sql-server-data-pages-in-buffer-pool-data-stored-in-memory-cache . However, I would like to know how much data (e.g. in percentage and KB) are stored of each table and index. Is there some simple way how to obtain such data?

Answer 1

You can find whats stored in the buffer pool (data cache) using below query :

From here :

select
 count(*)as cached_pages_count,
 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

Excellent reference : Inside the Storage Engine: What’s in the buffer pool? by Paul Randal.

Answer 2

You can use dynamic management view to list currently cached pages and filter them by database_id:

 select top 100 * from sys.dm_os_buffer_descriptors

Then you can see DBCC PAGE command to list pages of an object. Good reference: http://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/

It'll be up to you, however, to combine the results, and it doesn't seem an easy task :). Let us know when you come up with effective way to do this.

Answer 3

Try this SQL query:

select count(*)*8/1024 AS 'Cached Size (MB)' 
,case database_id 
when 32767 then 'ResourceDB' 
else db_name(database_id) 
end as 'Database'
from sys.dm_os_buffer_descriptors
where page_type in
(
'INDEX_PAGE'
,'DATA_PAGE'
)
group by db_name(database_id), database_id
order by 'Cached Size (MB)' desc