最近一个客户找到我说是所有的 SQL Server 服务器的内存都被用光了,然后截图给我看了一台服务器的任务管理器。如图
这里要说明一下任务管理器不会完整的告诉真的内存或者 CPU 的使用情况,也就是说这里只能得到非精确的信息,有可能就是一个假警报。
为了让我的客户放心,我检查了服务器并且查看了很多性能指标。我所看到的就是 CPU 和硬盘使用都是很低的只有内存是高的,这恰恰是我们期望的 SQLServer 服务器的状态。SQL Server 会尽可能的使用内存,通过缓存尽可能多的磁盘来改善性能。当然如果 OS 需要它也会立即释放资源回来。
SQL Server 对内存是 "贪得无厌" 的,它会持有所有分配给它的内存,不论是否使用。而这也是我们想要它去做的。因为它会存储数据和执行计划在缓存中,然后当使用完这些内存时,它不会释放这些内存,缓存到内存中,除非两种情况才会释放缓存的数据内存:1) SQL Server 重启或者内存不足 2) 操作系统需要内存
默认的内存设定就是使用所有内存(安装时设置),当操作系统需要内存时,它也会大量释放内存。然后等到有内存时在重新大量持有。但是这种不是最佳实践,最好还是设定一个最大内存限制,这样操作系统就会保证一定量的内存永远为 SQL Server 使用。
当看到资源管理器,Available MB 的内存有两部分组成 Standby-- 备用和 Free-- 可用,这 Standby 的空间系统已经把它缓存了,而 Free 的内存意味着没有被使用。它们都叫做可利用内存。因此针对一开始那个客户担忧我们大可不必太担心。当然我们还需要健康其他的性能计数器,查明是否存在内存影响性能的隐患。需要关注的指标如下:
介绍下这些性能参数:
这个性能计数器记录了数据页(非锁定)在缓冲池中的平均时间。在生产高峰这个数值可能比较低,但是一般要保持这个数据在 300s 以上,数据待在缓冲中时间越长,那么 SQL 的 IO 操作越少。
如果长期这个数值在 300s 以下,可以考虑增加内存,当然由于现在内存越来越大,这个值也变得不那么重要了,但是对于中小系统依然可以作为一个标准阈值。
由于这个阈值基于 32 位系统的 4G 内存,那么标准算法可以大致可以推算: 内存大小(GB)/4*300。
也可以使用下面的语句来查询该计数器:
- SELECT[cntr_value] FROM sys.dm_os_performance_counters WHERE[object_name] LIKE' % Buffer Manager % 'AND[counter_name] = 'Page life expectancy'
该计数器监测还有多少可用内存,是否操作系统存在内存压力。一般我们调查是否这个计数器持续在 500MB 以下,这说明内存过低。如果持续低于 500 则说明你需要增加更多的内存。
这个计数器不能通过 T-SQL 查询,只能通过性能监视器观察。
缓冲命中率,这个计数器记录平均多少频率从缓冲池中取得数据。我们在 OLTP 数据库中一般这个比率是 90%-95%(该数值经由 @ MSSQL123 指出发现是错误的,再次进行修改)。由于 sqlserver 把预读也作为缓冲比例,所以导致该值很高,所以该计数器只做理解,不能作为真实性能瓶颈参考了。如果该计数器持续低于 90%,则需要增加内存。
在可以使用下面的 T-SQL 语句查询:
- SELECT[cntr_value] FROM sys.dm_os_performance_counters WHERE[object_name] LIKE' % Buffer Manager % 'AND[counter_name] = 'Buffer cache hit ratio'
服务器当前总内存(buffer)以及目标内存,在缓冲池初始化增加内存的时候,总内存会比目标内存稍低一点。这个比例会逐渐接近 1,如果总内存没有增长很快,就会显著低于目标内存,这就表示如下两点:
1) 你可以分配尽可能多的内存,SQL 能缓存整个数据库到内存中,然后如果数据库小于机器内存,内存不会完全用光,在这种情况下,总内存将永远小于目标内存。
2) SQL 不能增加缓冲池,比如系统内存有压力。如果这种情况你需要增加最大服务器内存,或者增加内存来改善性能。
- SELECT[cntr_value] FROM sys.dm_os_performance_counters WHERE[object_name] LIKE' % Memory Manager % 'AND[counter_name] IN('Total Server Memory(KB)', 'Target Server Memory(KB)')
这个计数器测量等待内存授予的 SQL 的进程数量。一般推荐阈值为 1 或者更少。如果大于 1 这说明内存不足按顺序等待内存释放再操作 SQL。
一般工作中出现这种等待可能是由于糟糕的查询,缺失索引,排序或者哈希引起的。为了查明原因可以查询 DMV --sys.dm_exec_query_memory_grants 这个视图,将会展示哪一个查询需要内存授予执行。
如果不是以上原因引起的内存等待,则需要增加内存来解决这个问题。此时就有理由增加硬件了。查询的 T-SQL 语句如下:
- SELECT[cntr_value] FROM sys.dm_os_performance_counters WHERE[object_name] LIKE' % Memory Manager % 'AND[counter_name] = 'Memory Grants Pending'
这里也使用数据库级别计数器:当需要读取或写入的页不在内存中,需要到磁盘中读取时计数。这个计数器是一个记录读和写的总和并且不能直接在内存中获取只能从因盘中读取(导致 resulting in hard page faults),这个问题是由于操作系统必须交换文件在磁盘上,当访问内存时,内存不足则需要交换文件到磁盘上,由于磁盘读写速度远低于内存,性能就会受到严重影响。
对于这个计数器,推荐阈值为 < 50(或者某个稳定值), 如果看到高于这个值,不过需要注意,只要这个值能够稳定在一个较低的水平,没有持续性的大批量数据的写入(磁盘)于读取(从磁盘载入内存),都可以接受。相反,如果长期在一个高位水平,并且观察到 PLE 不能稳定在参考值范围内,说明内存可能存在瓶颈。当然,如果数据库备份或者还原,包括导出、导入数据以及内存中映射文件等等这些也会导致性能计数器超出某个稳定值。
该计数器包含两个检查
如果 Compilations/sec 是 25% 或者相对 Batch Requests/sec 更高,则执行计划将被放到缓存中,但是永远不会重用执行计划。宝贵的内存就被浪费了,而不是缓存数据。这是糟糕的实践,我们要做的就是阻止这种情况,
如果 Compilation/sec 很高比如 100,表示有大量的即席查询正在运行。这时可以启用 "optimize for ad hoc" 把执行计划缓存,但是只有在第二次查询时才能被使用。
使用如下 T-SQL 可以得到相应的指标:
- SELECT[cntr_value] FROM sys.dm_os_performance_counters WHERE[object_name] LIKE' % SQL Statistics % 'AND[counter_name] = 'Batch Requests / sec';
- SELECT[cntr_value] FROM sys.dm_os_performance_counters WHERE[object_name] LIKE' % SQL Statistics % 'AND[counter_name] = 'SQL Compilations / sec';
同样可以获得比率:
- SELECT ROUND(100.0 * (SELECT[cntr_value] FROM sys.dm_os_performance_counters WHERE[object_name] LIKE' % SQL Statistics % 'AND[counter_name] = 'SQL Compilations / sec') / (SELECT[cntr_value] FROM sys.dm_os_performance_counters WHERE[object_name] LIKE' % SQL Statistics % 'AND[counter_name] = 'Batch Requests / sec'), 2) as[Ratio]
推荐阈值:一般来说,我都是采用 10% 用于操作系统其它 90% 分配给数据库。当然如果内存很大可以调整这个比例小于 1/9,对于内存较小的通常我都预留 4-6G 左右给操作系统。
在性能监视器中看一下这个计数器,我们可以看到这个服务器处于健康状态下,有 11GB 的可用空间,没有 PageFaults(I/O 只从缓存中没有交换到磁盘),缓冲的比率为 100%,PLE 超过 20000s,没有内存等待,充足的总内存和较低的编译比率(编译数 / 查询数).
这个测量数据很容易理解,这要比任务管理器更具有作用,能依据此做出判断是否有足够的内存在这台 SQL Server 服务器上。
如果只根据任务管理器来做出判断,我们很容易出现错误决定。因为不管系统多少内存,SQL Server 会尽可能的使用占用内存,这不是 bug。缓存数据在内存中有很好的效果,意味着服务器是健康的,也为用户提供了更好的执行效率。在实际数据库环境中,一般突然遇到的性能问题多半是因为 T-SQL 语句引起的,就如我前面提到糟糕的查询(缺失索引、排序、哈希等等),这个时候通过语句优化可以很好的解决突发问题,这里就不详解了。如果服务器普遍存在文章中出现的内存性能计数器问题,那就写报告提交内存增加需求吧。
来源: http://www.bubuko.com/infodetail-2450667.html