专栏首页恰童鞋骚年SQL Server为啥使用了这么多内存?

SQL Server为啥使用了这么多内存?

原文地址: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,那么该数据库的活动几乎是只读的。读写的比例对磁盘的安排是很重要的。当然还有其他性能数据来获得数据库读写的大概比例,这里限于篇幅就不多谈了。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 《大型网站技术架构》读书笔记二:大型网站架构模式

    此篇已收录至《大型网站技术架构》读书笔记系列目录贴,点击访问该目录可获取更多内容。

    Edison Zhou
  • ASP.Net MVC开发基础学习笔记:五、区域、模板页与WebAPI初步

      为了方便大规模网站中的管理大量文件,在ASP.NET MVC 2.0版本中引入了一个新概念—区域(Area)。

    Edison Zhou
  • ASP.Net请求处理机制初步探索之旅 - Part 3 管道

    开篇:上一篇我们了解了一个ASP.Net页面请求的核心处理入口,它经历了三个重要的入口,分别是:ISAPIRuntime.ProcessRequest()、Ht...

    Edison Zhou
  • 约束

    一 介绍 约束条件与数据类型的宽度一样,都是可选参数 作用:用于保证数据的完整性和一致性 主要分为: PRIMARY KEY (PK) 标识该字段为该表的...

    用户1214487
  • Mybatis系列第7篇:各种查询详解

    Mybatis系列目标:从入门开始开始掌握一个高级开发所需要的Mybatis技能。

    路人甲Java
  • python中查看变量内存地址的方法

    本文实例讲述了python中查看变量内存地址的方法。分享给大家供大家参考。具体实现方法如下:

    py3study
  • 省市县,区的查询(通过区开始)(导出的接口实现)

    wfaceboss
  • Bypass 360主机卫士SQL注入防御(多姿势)

    在服务器客户端领域,曾经出现过一款 360 主机卫士,目前已停止更新和维护,官网都打不开了,但服务器中依然经常可以看到它的身影。

    信安之路
  • Bypass 360主机卫士SQL注入防御(多姿势)

    在服务器客户端领域,曾经出现过一款360主机卫士,目前已停止更新和维护,官网都打不开了,但服务器中依然经常可以看到它的身影。从半年前的测试虚拟机里面,翻出了36...

    Bypass
  • 面试题|无索引如何删除亿级数据?

    存在索引的情况下就比较简单,直接利用索引进行删除,写一个for 循环语句 每次删除500行,每次判断delete 影响的行数可以累加计算删除了多少行,直到删除结...

    用户1278550

扫码关注云+社区

领取腾讯云代金券