笔记236 SQL Server为啥使用了这么多内存 2013-4-13

笔记236 SQL Server为啥使用了这么多内存 2013-4-13

  1 --SQL Server为啥使用了这么多内存 2013-4-13
  2 --http://support.microsoft.com/gp/anxin_techtip6/zh-cn
  3 
  4 --SQL Server的用户,常常会发现SQL进程使用了很多内存。这些内存大多数都是用来缓存用户要访问的数据,以达到最优的效率。那怎么能够知道哪些数据现在正缓存在内存中呢?其实,数据库管理员跑几句查询,就能得到答案。
  5 --谁占用了我的Buffer Pool?
  6 --我在做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里面一个K 的data page的下列属性:
  7 --(1)该页属于哪个数据库
  8 --(2)该页属于数据库哪个文件
  9 --(3)该页的Page_ID
 10 --(4)该页的类型。可以根据这个来判断此页时索引页还是数据页
 11 --(5)该页内有多少行数据
 12 --(6)该页有多少可用空间。
 13 --(7)该页从磁盘读取以来是否修改过。
 14 --有了上面的信息,我们就可以很方便的统计出几种很有用的数据,如下
 15 
 16 -------------------------------------------------------------------------
 17 --1. Buffer Pool的内存主要是由那个数据库占了?
 18 SELECT count (*)*8  as cached_pages_kb,CASE database_id
 19 
 20         WHEN 32767 THEN 'ResourceDb'
 21 
 22         ELSE db_name (database_id)
 23 
 24         END AS Database_name
 25 
 26 FROM sys.dm_os_buffer_descriptors
 27 
 28 GROUP BY db_name( database_id) ,database_id
 29 
 30 ORDER BY cached_pages_kb DESC;
 31 
 32 --从上面的结果可以看到数据库 AdventureWorks占用了大概MB左右的缓冲池空间。
 33 --注意该DMV 并不返回Buffer Pool里面有关非数据页 (如执行计划的缓存等)的信息。也就是说这个 DMV并没有返回Buffer Pool里面所有页面的信息。
 34 
 35 -----------------------------------------------------------------------
 36 --2. 再具体一点,当前数据库的哪个表或者索引占用 Pool缓冲空间最多?
 37 SELECT count (*)*8 AS cached_pages_kb
 38 
 39     ,obj. name ,obj .index_id, b.type_desc ,b. name
 40 
 41 FROM sys.dm_os_buffer_descriptors AS bd
 42 
 43     INNER JOIN
 44 
 45     (
 46 
 47         SELECT object_name (object_id) AS name
 48 
 49             ,index_id , allocation_unit_id,object_id
 50 
 51         FROM sys.allocation_units AS au
 52 
 53             INNER JOIN sys.partitions AS p
 54 
 55                 ON au. container_id = p .hobt_id
 56 
 57                     AND ( au.type = 1 OR au.type = 3)
 58 
 59         UNION ALL
 60 
 61         SELECT object_name (object_id) AS name 
 62 
 63             ,index_id, allocation_unit_id,object_id
 64 
 65         FROM sys.allocation_units AS au
 66 
 67             INNER JOIN sys.partitions AS p
 68 
 69                 ON au. container_id = p .partition_id
 70 
 71                     AND au. type = 2
 72 
 73     ) AS obj
 74 
 75         ON bd. allocation_unit_id = obj .allocation_unit_id
 76 
 77         LEFT JOIN sys.indexes b on b. object_id = obj.object_id AND b.index_id = obj.index_id
 78 
 79  
 80 
 81 WHERE database_id = db_id()
 82 
 83 GROUP BY obj.name , obj. index_id ,b .name, b.type_desc
 84 
 85 ORDER BY cached_pages_kb DESC;
 86 --从上面的结果可以看到表 Individual 在Pool 内存里面缓冲最多,可能这个就是经常访问的热表,或者是比较大的表。注意 Pool里面的缓冲页是经常变化的。你如果再跑一次语句,出现在头条的可能是另外一个表了。
 87 ---------------------------------------------------------------------------------------
 88 --3. Buffer Pool缓冲池里面修改过的页总数大小。这个比较容易:
 89 SELECT count (*)*8  as cached_pages_kb, 
 90        convert(varchar (5), convert(decimal (5, 2),(100 -1.0*( select count (*) from  
 91  
 92 sys.dm_os_buffer_descriptors b where b .database_id= a.database_id and  
 93  
 94 is_modified=0 )/count(*)* 100.0)))+'%' modified_percentage 
 95         ,CASE database_id 
 96         WHEN 32767 THEN 'ResourceDb' 
 97         ELSE db_name (database_id)  
 98         END AS Database_name 
 99 FROM sys.dm_os_buffer_descriptors a 
100 GROUP BY db_name( database_id) ,database_id 
101 ORDER BY cached_pages_kb DESC;   
102 
103 结果:
104 --从上面的结果可以看到, AdventureWorks数据库大概有%的数据是修改过的。如果一个数据库的大部分(超过 %)是修改过的,那么这个数据库写操作非常多。反之如果这个比例接近,那么该数据库的活动几乎是只读的。读写的比例对磁盘的安排是很重要的。当然还有其他性能数据来获得数据库读写的大概比例,这里限于篇幅就不多谈了。

 

posted @ 2013-08-04 19:48 桦仔 阅读(...) 评论(...)  编辑 收藏