Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com May 31 '13 at 16:49

This question came from our site for professional and enthusiast programmers.

3 Answers 3

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.

share|improve this answer
    
+1 Thanks! I thing the query does soemthing very similar to what is done in the article in my question. However, the link looks very interesting/ –  Ondra Peterka May 31 '13 at 17:08

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.

share|improve this answer
    
+1 Thanks for the article! It is really very interesting stuff. I will try to get it together, but so far I don't see how :). However, if I will have some useful results I iwll post them here. –  Ondra Peterka May 31 '13 at 15:05

For quick overview there is "Memory" Report in SSMS.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.