Как узнать, что кэшируется в памяти в SQL Server 2008?

DMV sys.dm_os_buffer_descriptors имеет два столбца, которые будут иметь значение здесь: database_id и allocation_unit_id. С помощью allocation_unit_id вы можете пройти через sys.partitions в sys.indexes и, наконец, в sys.objects. Кажется, что это количество на объект в контексте текущей базы данных. При необходимости адаптировать

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

Вот инструкция, которая возвращает некоторую информацию о размере ...

-- 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 ;