SQL SERVER中与内存相关的计数器

SQL SERVER中与内存相关的计数器

SQL Server在理论上能够使用的最多的内存量。如果在SQL Server中设置了 Max Server Memory，则会使用该值作为内存上限。

一、在SQL Server中通过如下SQL语句查询与内存相关的计数器的使用情况

select * from sys.dm_os_performance_counters where object_name like '%Buffer Manager%' or object_name like '%Memory Manager%'

				

查询结果如图

二、下面介绍常用的计数器

1、Page Life Expectancy

(1)、含义是内存页面在内存中停留的平均时间，在系统视图sys.dm_os_performance_counters中可以查到，单位是秒

(2)、需要注意的是它不是指某一个page的最大值或者最小值，而是所有由所有页面停留在buffer pool中的时间计算出来的一个平均值

(3)、如果这个值越大，说明Sql Server在检索数据时候直接从buffer pool中获取数据的概率越大

(4)、这个值可以通过sys.dm_os_performance_counters 这个系统视图直接查询得出：

select * 
from sys.dm_os_performance_counters 
where object_name like '%Buffer Manager%' and counter_name='Page life expectancy'

		

(5)、那么这个值多少位正常呢？粗略的计算方式为：Max Buffer Pool(GB)/4*300(S)

(6)、这个值是作为判断内存是否存在瓶颈的最重要的指标之一

2、Buffer Cache hit ratio

(1)、就是缓存命中率，指直接从内存中读取出来的比例占所有数据的百分比

(2)、由于表现出来的值受到其算法的制约，反倒是在内存压力诊断的时候并不具备太多的参考意义

3、Page reads（writes） / sec

(1)、这两个计数器分别是对应的平均每秒钟的物理读/写的数据量，这个计数器的是一个累计值，单位为page，而每个page又是8Kb，可以换算成一个基于kb或者mb位单位数据

(2)、这两个值可以通过如下sql查询得到：

select *
from sys.dm_os_performance_counters
where object_name like '%Buffer Manager%' and (counter_name='Page reads/sec' or counter_name='Page writes/sec' )

		

(3)、如果一台服务器上经常发生大批量的物理性IO操作，你就要注意是否存在内存问题

(4)、只要这个值能够稳定在一个较低的水平，没有持续性的大批量数据的写入（磁盘）于读取（从磁盘载入内存），都可以接受

(5)、如果长期在一个高位水平，并且观察到PLE不能稳定在参考值范围内，说明内存可能存在瓶颈。

4、Lazy writes / sec

(1)、是每秒被缓冲区管理器的惰性编写器（lazy writer）写入的缓存区的数据page信息

(2)、Lazy writer是一个系统进程，用于批量刷新内存中的脏页到磁盘，并且将原来脏页占用的内存空间清理的一个动作。

(3)、如果存在内存压力，Lazy writer会被触发，将脏页和长时间没有用到的计划缓存清理出内存，如果经常被触发，那么说明内存可能存在瓶颈

(4)、通过如下 sys.dm_os_performance_counters 查询出来的Lazy writes/sec值是一个累计值：

select * 
from sys.dm_os_performance_counters 
where object_name like '%Buffer Manager%' and counter_name='Lazy writes/sec'

				

(5)、Lazy Write是在面临内存压力的情况下触发的

(6)、如果某一个时间间隔内，Lazy Write持续不为零，就要结合PLE以及Page reads（writes） / sec 来判断分析内存是否存在不够用的情况了

5、Total Server Memory/Target Server Memory

(1)、Total Server Memory是Sql Server内存管理器“已提交”内存，就是已经占用了的内存

(2)、Target Server Memory是Sql Server内存管理器可用的最大内存

(3)、这两个值也可用通过sys.dm_os_performance_counters 查询出来：

select * 
from sys.dm_os_performance_counters 
where object_name like '%Memory Manager%' and counter_name in ('Target Server Memory (KB)','Total Server Memory (KB)')

		

(4)、当Total Server Memory小于Target Server Memory的时候，Sql Server还知道系统还有可用内存，在需要内存中的时候，直接跟系统申请，此时Total Server Memory会逐渐变大。

(5)、当Total Server Memory接近于或者等于Target Server Memory的时候，Sql Server会意识到已经用完了系统的可用内存，如果在需要内存的时候，系统已经无法继续分配新的内存，它就需要清理已用的内存空间，将新清理出来的空间给新的数据使用。

(6)、数据库经过一段时间的运行后，Total Server Memory总是接近于或者等于Target Server Memory的

(7)、如果Total Server Memory持续性接近或者等于Target Server Memory，而PLE明显低于计算出来的参考值,低到几分钟甚至一两分钟，同时观察到内存跟磁盘之间频繁地、大量地物理性交换数据，这也说明，内存极有可能存在瓶颈。