谁占用了我的Buffer Pool?

港控/mmm° 2023-03-01 08:50 19阅读 0赞

原文出处：[https://docs.microsoft.com/en-us/archive/blogs/apgcdsd/buffer-pool][https_docs.microsoft.com_en-us_archive_blogs_apgcdsd_buffer-pool]

*  01/10/2011
 *  4 minutes to read

我在做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;

结果如下：

![format_png][]

从上面的结果可以看到数据库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;

输出结果如下 （部分）:

![format_png 1][]

从上面的结果可以看到表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;

结果：

![format_png 2][]

从上面的结果可以看到，AdventureWorks数据库大概有13.84%的数据是修改过的。如果一个数据库的大部分（超过80%） 是修改过的，那么这个数据库写操作非常多。反之如果这个比例接近0，那么该数据库的活动几乎是只读的。读写的比例对磁盘的安排是很重要的。当然还有其他性能数据来获得数据库读写的大概比例，这里限于篇幅就不多谈了。

[https_docs.microsoft.com_en-us_archive_blogs_apgcdsd_buffer-pool]: https://docs.microsoft.com/en-us/archive/blogs/apgcdsd/buffer-pool
[format_png]: /images/20230209/95e7da5b0d3c44378ec1ee27f678c7de.png
[format_png 1]: /images/20230209/e6d230fd313b44c1be8c910126ad5ea2.png
[format_png 2]: /images/20230209/72b1144ffd0247beaf8591d844008aa9.png