Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. 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 here:

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.

Are there some simple mechanisms to obtain such data?

share|improve this question

2 Answers 2

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
  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
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] ,
        p.index_id
ORDER BY buffer_count DESC ;
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.