SQL SERVER 當前資料庫佔用Pool緩衝空間情況

今天在微軟網站看到一個檢視當前資料庫的哪個表或者索引佔用Pool緩衝空間最多指令碼

覺得還不錯：

SELECT count(*) * 8 AS cached_pages_kb

   ,obj.name,obj.index_id,b.type_desc,b.name

FROM sys.dm_os_buffer_descriptors AS bd

   INNER JOIN

   (

       SELECT object_name(object_id) AS name

           ,index_id,allocation_unit_id,object_id

       FROM sys.allocation_units AS au

           INNER JOIN sys.partitions AS p

               ON au.container_id=p.hobt_id

                   AND(au.type=1 OR au.type=3)

       UNION ALL

       SELECT object_name(object_id) AS name 

           ,index_id,allocation_unit_id,object_id

       FROM sys.allocation_units AS au

           INNER JOIN sys.partitions AS p

               ON au.container_id=p.partition_id

                   AND au.type=2

   )AS obj

       ON bd.allocation_unit_id=obj.allocation_unit_id

       LEFT JOIN sys.indexes b on b.object_id=obj.object_id AND b.index_id=obj.index_id

 

WHERE database_id=db_id()

GROUP BY obj.name,obj.index_id,b.name,b.type_desc

ORDER BY cached_pages_kb DESC;

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8183550/viewspace-693672/，如需轉載，請註明出處，否則將追究法律責任。