查看Buffer Pool使用情况

小灰灰 2022-03-18 07:28 185阅读 0赞

----源自：微软官方博客论坛

我的SQL Server buffer pool很大,有办法知道是哪些对象吃掉我的buffer Pool内存么？比方说，能否知道是哪个数据库，哪个表，哪个index占用了buffer Pool么？”针对这个问题可以使用（DMV） sys.dm_os_buffer_descriptors。这个DMV非常强大。根据SQL Server 联机丛书，这个视图的作用是　“返回有关 SQL Server 缓冲池中当前所有数据页的信息。可以使用该视图的输出，根据数据库、对象或类型来确定缓冲池内数据库页的分布”。具体点说，这个视图能够返回buffer pool里面一个8K 的data page的下列属性：
     
    (1)该页属于哪个数据库
     
    (2)该页属于数据库哪个文件
     
    (3)该页的Page_ID
     
    (4)该页的类型。可以根据这个来判断此页时索引页还是数据页
     
    (5)该页内有多少行数据
     
    (6)该页有多少可用空间。
     
    (7)该页从磁盘读取以来是否修改过。
     
    
    有了上面的信息，我们就可以很方便的统计出几种很有用的数据，如下。
     
    
    --1.       Buffer Pool的内存主要是由那个数据库占了？
     
    
    SELECT count(*)*8  as cached_pages_kb,CASE database_id 
    
            WHEN 32767 THEN 'ResourceDb' 
    
            ELSE db_name(database_id) 
    
            END AS Database_name
     
    FROM sys.dm_os_buffer_descriptors
     
    GROUP BY db_name(database_id) ,database_id
     
    ORDER BY cached_pages_kb DESC;
    
    
    ---注 该DMV 并不返回Buffer Pool里面有关非数据页(如执行计划的缓存等)的信息。也就是说这个DMV并没有返回Buffer Pool里面所有页面的信息。
    
    --2.       再具体一点，当前数据库的哪个表或者索引占用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;
    
    --3.       Buffer Pool缓冲池里面修改过的页总数大小。这个比较容易：
     
    
    SELECT count(*)*8  as cached_pages_kb,
     
           convert(varchar(5),convert(decimal(5,2),(100-1.0*(select count(*) from sys.dm_os_buffer_descriptors b where b.database_id=a.database_id and is_modified=0)/count(*)*100.0)))+'%' modified_percentage
     
            ,CASE database_id 
    
            WHEN 32767 THEN 'ResourceDb' 
    
            ELSE db_name(database_id) 
    
            END AS Database_name
     
    FROM sys.dm_os_buffer_descriptors a
     
    GROUP BY db_name(database_id) ,database_id 
    
    ORDER BY cached_pages_kb DESC;