• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    SQL Server 数据页缓冲区的内存瓶颈分析
    SQL Server会把经常使用到的数据缓存在内存里(就是数据页缓存),用以提高数据访问速度。因为磁盘访问速度远远低于内存,所以减少磁盘访问量同样是数据库优化的重要方面。

    当数据页缓存区出现内存不足,则会出现查询慢,磁盘忙等等问题。

    分析方法:主要是用到性能计数器。

    查看如下性能计数器:

    1. SQL SERVER:Buffer Manager-Lazy Writes/sec:内存不足则会频繁调用Lazy Writer把数数据写入磁盘,此值会经常不为0.

    2. SQL SERVER:Buffer Manager-Page life expectancy:内存不足时,此计数器表现为下降趋势或者一直停留在较低值。

    3. SQL SERVER:Buffer Manager-Page reads/sec:内存不足时,则查询那些经常使用但又没有缓存在内存里的数据时,就不需要读取磁盘,这此值表现为持续上升或者停留在较高值。

    4. SQL SERVER:Buffer Manager-Stolen pages:Stolen pages通常用于缓存执行计划,以备重用。内存不足时,SQL Server本身机制会优先清除执行计划缓存,则此值表现为下降或者较低水平。

    查询当前用户任务等待:

    复制代码 代码如下:

    select * from sys.sysprocesses

    如果内存不足则,会看到较多的ASYNC_IO_COMPLETION等待类型。这是因为内存不足时:a.内存和磁盘间会频繁进行交互,磁盘负载增加 b.需要读取磁盘上的数据完成查询,磁盘负载增加。

    也就是说这时候磁盘也出现了性能瓶颈,但是这只是“表面”的,我们要结合多个性能指标来认清根本原因是“内存不足”。

    确定压力来源及解决办法:

    通过前的分析,确定了数据页缓存相关的内存瓶颈。就要分析为什么会这样及解决办法。主要分为如下5个方面:

    1. 外部压力

    如果OS层面或者其它应用服务需要更多的内存,windows会压缩Database Pages的内存量。这时内存压力来自外部。可以查看如下性能计数器确定是否是外部压力:

    1. SQL Server:Memory Manager-Total Server Memory:此计数器值会下降。

    2. Memory:Available Mbytes:此值会下降到较低水平。

    3. 在没有使用AWE或者Lock page in memory前提下,查看Process:Private Bytes-SqlServer和Process:Working Set-SqlServer,两者值会有显著下降。

    解决方法:如果非DB专用服务器,则要权衡各个应用服务之间重要性来分配内存或者加大内存。尽量让服务器只运行SQL Server,成为DB专用服务器。

    2. SQL Server自身对Database Page的使用压力

    当Total Server Memory已经达到设定的Max Server Memory或者无法从OS获得更多内存,但是经常访问的数据量又远大于物理内存用于数据缓存的容量时,SQL Server被迫将内存的数据移入又移出,用于完成当前查询。

    观察如下性能计数器:

    1. SQL Server:Memory Manager-Total Server Memory 和 SQL Server:Memory Manager-Target Server Memory两者值将会相等。但是前者不会大于后者。

    2. 将会出现“分析方法”所述之情况。

    解决方法:既然SQL Server没有足够内存存放Database Page,那就要么增加SQL Server使用的内存量或者减少其使用的内存里。

    增加:可以通增加物理内存,启用AWE等方法。

    减少:可以通过横向扩展,有两台或者多台服务器分别载部分库;优化相关读取量较大的语句等。

    3. Buffer Pool中的Stolen Memory压力

    正常情况下Buffer Pool中的Stolen Memory不会给Database Pages造成压力。因为Database Pages有压力,会触发Lazy Writes,同时SQL Server 会清理Stolen Memory中的执行计划缓存。

    但是,如果用户申明了过多的对象,而没有登出,并且占用内存过多,就会压缩Database Pages.如:游标,自定义引用的执行计划等。

    解决方法:通常是会表现为a)用户提交的请求因内存不足无法完成,701错误;b)需要压缩某些clerk的内存量,来完成用户请求,造成响应延时和缓慢。

    通过查询sys.dm_os_memory_clerks的字段Single_pages_kb,找出是哪个clerk使用了过多内存并分析其原因,然后解决之。

    4. Multi-Page的压力

    multi-page跟Buffer Pool共享OS的虚拟地址空间,如果multi-page使用过多内存,就会压缩Datbase pages。multi-page内存用量一般较小且相对固定,可能发生的情况有:

    a. 未开启AWE的32位SQL Server只有2G地址空间,且用-g启动参数扩展的MemToLeave的上限。

    b. 64位SQL Server调了内存泄露的第三方代码。

    c. 使用带有大量参数或者较长的”IN”语句

    d. 调高了Network Packet Size,大于或等于8KB,并且较多这种连接。

    e. 大量复杂XML查询,或者第三代码。

    解决方法: 通过查询sys.dm_os_memory_clerks的字段multi_pages_kb,找出是哪个clerk使用了过多内存并分析其原因,然后解决之。


    作者:Joe.TJ
    您可能感兴趣的文章:
    • SQL语句实现查询SQL Server内存使用状况
    • 优化SQL Server的内存占用之执行缓存
    • SqlServer如何通过SQL语句获取处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统相关信息
    • SQL Server 2008 R2占用cpu、内存越来越大的两种解决方法
    • 解决SQL Server虚拟内存不足情况
    • 揭秘SQL Server 2014有哪些新特性(1)-内存数据库
    • 浅谈SQL Server 对于内存的管理[图文]
    • SQL Server内存遭遇操作系统进程压榨案例分析
    • SQL Server在AlwaysOn中使用内存表的“踩坑”记录
    • sql server学习基础之内存初探
    上一篇:SQL处理多级分类,查询结果呈树形结构
    下一篇:sqlserver 查询数据库大小的方法
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    SQL Server 数据页缓冲区的内存瓶颈分析 SQL,Server,数据,页,缓冲区,