[轉]SQL Server為啥使用了這麼多記憶體？

標籤：des   style   http   color   io   os   使用   ar   strong   

原文地址：http://support.microsoft.com/gp/anxin_techtip6/zh-cn

SQL Server為啥使用了這麼多記憶體？ SQL Server的使用者，常常會發現SQL進程使用了很多記憶體。這些記憶體大多數都是用來緩衝使用者要訪問的資料，以達到最優的效率。那怎麼能夠知道哪些資料現在正緩衝在記憶體中呢？其實，資料庫管理員跑幾句查詢，就能得到答案。 

誰佔用了我的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緩衝空間最多?

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 span>

        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，那麼該資料庫的活動幾乎是唯讀。讀寫的比例對磁碟的安排是很重要的。當然還有其他效能資料來獲得資料庫讀寫的大概比例，這裡限於篇幅就不多談了。

[轉]SQL Server為啥使用了這麼多記憶體？