我在做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的内存主要是由那个数据库占了?
SELECTcount(*)*8ascached_pages_kb,CASEdatabase_id
WHEN32767THEN'ResourceDb'
ELSEdb_name(database_id)
ENDASDatabase_name
FROMsys.dm_os_buffer_descriptors
GROUPBYdb_name(database_id),database_id
ORDERBYcached_pages_kbDESC;
结果如下:
从上面的结果可以看到数据库AdventureWorks占用了大概30MB左右的缓冲池空间。
注意该DMV并不返回Buffer Pool里面有关非数据页(如执行计划的缓存等)的信息。也就是说这个DMV并没有返回Buffer Pool里面所有页面的信息。
2.再具体一点,当前数据库的哪个表或者索引占用Pool缓冲空间最多?
SELECTcount(*)*8AScached_pages_kb
,obj.name,obj.index_id,b.type_desc,b.name
FROMsys.dm_os_buffer_descriptorsASbd
INNERJOIN
(
SELECTobject_name(object_id)ASname
,index_id,allocation_unit_id,object_id
FROMsys.allocation_unitsASau
INNERJOINsys.partitionsASp
ONau.container_id=p.hobt_id
AND(au.type=1ORau.type=3)
UNIONALL
SELECTobject_name(object_id)ASname
,index_id,allocation_unit_id,object_id
FROMsys.allocation_unitsASau
INNERJOINsys.partitionsASp
ONau.container_id=p.partition_id
ANDau.type=2
)ASobj
ONbd.allocation_unit_id=obj.allocation_unit_id
LEFTJOINsys.indexesbonb.object_id=obj.object_idANDb.index_id=obj.index_id
WHEREdatabase_id=db_id()
GROUPBYobj.name,obj.index_id,b.name,b.type_desc
ORDERBYcached_pages_kbDESC;
输出结果如下(部分):
从上面的结果可以看到表Individual在Pool内存里面缓冲最多,可能这个就是经常访问的热表,或者是比较大的表。注意Pool里面的缓冲页是经常变化的。你如果再跑一次语句,出现在头条的可能是另外一个表了。
3.Buffer Pool缓冲池里面修改过的页总数大小。这个比较容易:
SELECTcount(*)*8ascached_pages_kb,
convert(varchar(5),convert(decimal(5,2),(100-1.0*(selectcount(*)fromsys.dm_os_buffer_descriptorsbwhereb.database_id=a.database_idandis_modified=0)/count(*)*100.0)))+'%'modified_percentage
,CASEdatabase_id
WHEN32767THEN'ResourceDb'
ELSEdb_name(database_id)
ENDASDatabase_name
FROMsys.dm_os_buffer_descriptorsa
GROUPBYdb_name(database_id),database_id
ORDERBYcached_pages_kbDESC;
结果:
从上面的结果可以看到,AdventureWorks数据库大概有13.84%的数据是修改过的。如果一个数据库的大部分(超过80%)是修改过的,那么这个数据库写操作非常多。反之如果这个比例接近0,那么该数据库的活动几乎是只读的。读写的比例对磁盘的安排是很重要的。当然还有其他性能数据来获得数据库读写的大概比例,这里限于篇幅就不多谈了。
相关推荐
InnoDB: Initializing buffer pool, size = 120.0G
DB2性能监控和调优入门练习之三(Bufferpool篇)。
41 在Buffer Pool执行完增删改之后,写入日志文件的redo log长什么样.pdf
Mysql执行过程与BufferPool缓存机制.png
12 Buffer Pool这个内存数据结构到底长个什么样子.pdf
什么是BufferPool? **Buffer Pool基本概念** Buffer Pool:缓冲池,简称BP。其作用是用来缓存表数据与索引数据,减少磁盘IO操作,提升效率。 Buffer Pool由**缓存数据页(Page)** 和 对缓存数据页进行描述的**...
14 当我们更新Buffer Pool中的数据时,flush链表有什么用.pdf
21 生产经验:如何通过多个Buffer Pool来优化数据库的并发性能.pdf
13 从磁盘读取数据页到Buffer Pool的时候,free链表有什么用.pdf
22 生产经验:如何通过chunk来支持数据库运行期间的Buffer Pool动态调整.pdf
对Mysql(索引结构/事务/锁/MVCC/BufferPool/优化)总结,包括重要知识点与面试点,xmind源文件
11 从数据的增删改开始讲起,回顾一下Buffer Pool在数据库里的地位.pdf
15 当Buffer Pool中的缓存页不够的时候,如何基于LRU算法淘汰部分缓存.pdf
深入理解MVCC与BufferPool缓存机制
MySQL性能优化InnoDB buffer pool flush策略
MySQL中读页缓冲区buffer pool.doc
23 生产经验:在生产环境中,如何基于机器配置来合理设置Buffer Pool.pdf
浅析在线调整 innodb_buffer_pool_size 作者:zhou mysql版本:5.7 先介绍一下 buffer pool: 在innodb存储引擎中数据访问以page为单位,page也是innodb管理数据库的最小磁盘单位,每个page的默认大小为16KB(可以通过...
07-VIP-深入理解MVCC与BufferPool缓存机制.pdf
行业-12 Buffer Pool这个内存数据结构到底长个什么样子.rar