ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL SERVER 当前数据库占用Pool缓冲空间情况

SQL SERVER 当前数据库占用Pool缓冲空间情况

原创 Linux操作系统 作者:edwardking888 时间:2011-04-25 15:49:09 0 删除 编辑

今天在微软网站看到一个查看当前数据库的哪个表或者索引占用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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-04-03

  • 博文量
    477
  • 访问量
    1440475