誰占用了我的Buffer Pool?



我在做SQL Server 7.0技術支援的時候有客戶問我,“我的SQL

Server buffer pool很大,有辦法知道是哪些對象吃掉我的buffer

Pool記憶體麼？比方說，能否知道是哪個資料庫，哪個表，哪個index占用了buffer

Pool麼？”當時我沒有找到這個問題的答案，但是我一直記着這個問題。直到SQL server 2005

版本出現，這個問題迎刃而解。答案就是使用動态視圖（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;

結果如下：

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

注意該DMV

并不傳回Buffer Pool裡面有關非資料頁(如執行計劃的緩存等)的資訊。也就是說這個DMV并沒有傳回Buffer

Pool裡面所有頁面的資訊。

2.      

再具體一點，目前資料庫的哪個表或者索引占用Pool緩沖空間最多?

count(*)*8

AS cached_pages_kb

,obj.name

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

AS bd

INNER JOIN

(

SELECT object_name(object_id)

AS name

,index_id ,allocation_unit_id,object_id

FROM sys.allocation_units

AS au

sys.partitions

AS p

ON au.container_id

= p.hobt_id

AND (au.type

= 1 OR au.type

= 3)

UNION ALL

AS name  

,index_id, allocation_unit_id,object_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()

BY obj.name, obj.index_id

,b.name,b.type_desc

輸出結果如下

（部分）:

從上面的結果可以看到表Individual

在Pool記憶體裡面緩沖最多，可能這個就是經常通路的熱表，或者是比較大的表。注意Pool裡面的緩沖頁是經常變化的。

你如果再跑一次語句，出現在頭條的可能是另外一個表了。

3.      

Buffer Pool緩沖池裡面修改過的頁總數大小。這個比較容易：

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

sys.dm_os_buffer_descriptors a

結果：

從上面的結果可以看到，AdventureWorks資料庫大概有13.84%的資料是修改過的。如果一個資料庫的大部分（超過80%）

是修改過的，那麼這個資料庫寫操作非常多。反之如果這個比例接近0，那麼該資料庫的活動幾乎是隻讀的。讀寫的比例對磁盤的安排是很重要的。當然還有其他性能資料來獲得資料庫讀寫的大概比例，這裡限于篇幅就不多談了。