SQLSERVER中查看誰占用了Buffer Pool

使用動態視圖： sys.dm_os_buffer_descriptors

這個視圖返回一個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;

從上面的結果可以看到資料庫AdventureWorks占用了大概30MB左右的緩衝池空間。

 2.當前庫哪個表占用的最多：

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;

從上面的結果可以看到表Individual 在Pool記憶體裡面緩衝最多，可能這個就是經常訪問的熱表，或者是比較大的表。註意Pool裡面的緩衝頁是經常變化的。 你如果再跑一次語句，出現在頭條的可能是另外一個表了。

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;

從上面的結果可以看到，AdventureWorks資料庫大概有13.84%的數據是修改過的。如果一個資料庫的大部分（超過80%） 是修改過的，那麼這個資料庫寫操作非常多。反之如果這個比例接近0，那麼該資料庫的活動幾乎是只讀的。

（此文為轉載）