☰

如何查看SQL Server 2008中内存中缓存的内容？

tag-icon • tag-icon sql-server sql-server-2008 memory
如何查看SQL Server 2008中内存中缓存的内容？

有没有办法找出 SQL Server 2008 R2 中缓存的内容？我在这里找到了以下好文章：

http://blog.sqlauthority.com/2010/06/17/sql-server-data-pages-in-buffer-pool-data-stored-in-memory-cache

但是，我想知道每个表和索引存储了多少数据（例如以百分比和 KB 为单位）。

是否存在一些简单的机制来获取这些数据？

答案1

sys.dm_os_buffer_descriptors DMV 有两列与此相关：database_id 和 assignment_unit_id。使用 assignment_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

答案2

以下是返回一些尺寸信息的语句......

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

相关内容