Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a way how to find out what is cached in SQL Server 2008 R2? I have found the following nice article here:

However, I would like to know how much data (e.g. in percentage and KB) are stored of each table and index.

Are there some simple mechanisms to obtain such data?

share|improve this question

The sys.dm_os_buffer_descriptors DMV has two columns that are going to be relevant here: database_id and allocation_unit_id. With the allocation_unit_id, you can go through sys.partitions into sys.indexes and finally to sys.objects. This seems to get the count per object within the context of the current database. Adapt as necessary

SELECT   AS [schema_name]
  , AS [object_name]
  , AS [index_name]
  , COUNT( * )
FROM       sys.dm_os_buffer_descriptors AS buff
INNER JOIN sys.[allocation_units] AS au
        ON buff.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions part
        ON ( au.type IN ( 1, 3 )
             AND au.[container_id] = part.[hobt_id] )
        OR ( au.type = 2
             AND au.[container_id] = part.[partition_id] )
INNER JOIN sys.data_spaces ds
        ON au.[data_space_id] = [ds].[data_space_id]
INNER JOIN sys.[indexes] AS i
        ON part.[object_id] = i.[object_id]
       AND part.[index_id] = i.[index_id]
INNER JOIN sys.[objects] AS o
        ON [i].[object_id] = [o].[object_id]
INNER JOIN sys.schemas s
        ON [o].[schema_id] = [s].[schema_id]
WHERE      o.is_ms_shipped = 0
       AND buff.database_id = DB_ID( )
share|improve this answer

Here is a statement that returns some size info...

-- Breaks down buffers by object (table, index) in the buffer pool
SELECT  OBJECT_NAME(p.[object_id]) AS [ObjectName] ,
        p.index_id ,
        COUNT(*) / 128 AS [Buffer size(MB)] ,
        COUNT(*) AS [Buffer_count]
FROM    sys.allocation_units AS a
        INNER JOIN sys.dm_os_buffer_descriptors
                 AS b ON a.allocation_unit_id = b.allocation_unit_id
        INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id
WHERE   b.database_id = DB_ID()
        AND p.[object_id] > 100 -- exclude system objects
GROUP BY p.[object_id] ,
ORDER BY buffer_count DESC ;
share|improve this answer

Your Answer


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.