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?


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

  s.name   AS [schema_name]
  , o.name AS [object_name]
  , i.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( )
GROUP      BY s.name
              , o.name
              , i.name
ORDER      BY s.name
              , o.name
              , i.name

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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