专注,勤学,慎思。戒骄戒躁,谦虚谨慎

just do it

导航

Buffer cache hit ratio性能计数器真的可以作为SQL Server 内存瓶颈的判断指标吗?

 

SQL Server中对于Buffer cache hit ratio的理解:

 

  Buffer cache hit ratio官方是这么解释的:“指示在缓冲区高速缓存中找到而不需要从磁盘中读取的页的百分比。”

 

Buffer cache hit ratio被很多人当做判断内存的性能指标之一(当然没说仅仅只看这个计数器的值,实际上现在都不怎么看这个值了),
也有不少给给出了具体的参数,诸如(OLTP)要大于95%,或者是大于98%之类的,我不知道给出具体参考值的人是不是真是地区测试过这个参数的值,是作为经验总结还是复制粘贴?
当我去服务器上观察这个值的时候,似乎发现一个规律,
不管服务器的负载如何,即便是存在较重的业务负载的时候,这个值一直是接近所谓的“理想值”(99%),难道这个值真的可以去作为衡量内存瓶颈的指标吗,
实际上被这个问题困惑了好多天,
我在测试的时候,尽管不断地去压缩SqlServer的最大内存限制,
然后做压力测试,
尽管Page life expectancy可以底到十几二十几毫秒,也就是内存已经存在很严重的瓶颈了,却发现Buffer cache hit ratio这个计数器的值是99%左右,
于是开始怀疑这个计数器的算法,如果说缓冲命中率达到99%左右,能否说明没有内存瓶颈呢?
其实如果做过实际测试,应该不难发现这个问题,对于这个值,早就有人怀疑过了,明明是存在内存的瓶颈,缓存命中率却显示为99%
只是没发现有人提供满意的答案,具体问题可以参考这个 http://bbs.csdn.net/topics/330018239

 

下面演示一下测试步骤,测试过程可能比较粗粗略,说明其中原理即可

 

1,首先限制SqlServer的最大内存为1G,然后依次读取容量大于1G空间的不同的表,看看性能计数器给出的结果

,

 

2,创建一张测试表,往里面写入将近1G的数据量

  然后再创建跟这个表一样大小的表,目前,这两个表的数据都接近于1G的空间

  select * into DBTEST2.dbo.TestBufferCacheHitRatio_BAK from TestBufferCacheHitRatio

 

3,我们知道SqlServer读取数据的时候,粗略地讲,(如果数据不在缓存中)是现将数据读取到内存,然后再将数据返回给客户端,

测试是我在本机完成的,本机数据库服务器没有任何负载,测试的两个库也是新建的空数据库
造完测试数据之后,
测试之前我先清除所有缓存,dbcc dropcleanbuffers,
然而,由于限制了SqlServer的最大内存限制而1G,忽略SqlServer非数据缓存占用的内存空间,可以粗略地认为,
当对第一个表读取完后,这个表基本上占据满了SqlServer可用内存空间,
如果继续读取另外一张类似表的数据的时候,就要从磁盘上读取了
(实际上已经清除缓存了,主要是为了说明,第一次查询占满了内存,第二次查询必然要从磁盘读取到内存,内存中没有第二次查询的数据的缓存,即便是不清除缓存,也是一个效果),
此时观察Buffer cache hit ratio计数器的值,
理论上说,此时第二张表的数据是直接从磁盘上读取的,也就不存在所谓的缓存,缓存命中率应该是一个非常低的值,甚至是0,
如果实际来观察所谓的“缓存命中率”的值,看看是什么结果

 

截图是第一个查询执行完成之后,执行第二个查询的时候,Buffer cache hit ratio性能计数器的情况,
第二个查询执行完成之后,我暂停了计数器监控,
这个结果应该是不受外界因素影响的(再次说明,我本机数据库没有任何负载,纯粹本机做测试的一个实例,也不用反复测试,我反复测试了N次了,下面会说明原因所在)
从截图可以看到,在第一个查询执行完成之后,
第二个查询执行的过程中,缓存命中率竟然没有明显的下降,最小值也是96%,平均值高达99%,第二个表的数据命名是从磁盘读取的,当然通过IO也可以观察出来,纯粹的预读
这不扯淡吗,测试之前清空过缓存,并且,现有内存已经被第一个查询占据满了,
明明第二张表的数据纯粹第是从硬盘空间读取的,为什么缓存命中率Buffer cache hit ratio竟然高达99%,
难怪之前我观察任何一台服务器的缓存命中率(Buffer cache hit ratio),即便是业务高峰期,都是在99%以上,原因在哪里?

 

何为Buffer cache hit ratio?

原来是Buffer cache hit ratio这个计数器在计算缓存命中率的时候,
把read ahead read,也即预读读取出来的数据,也算是“缓存”了,只有物理读也即physical read算作非缓存,难怪Buffer cache hit ratio总是有这个高的值
那么就来说说预读,实际上预读是什么?
预读是指在在查询执行之前,预估查询可能要用到的数据,在查询执行之前将数据读取到内存中,
所以,也不难理解,为什么没有把预读产生的数据作为缓存数据来处理。
真正在查询的时候,发现数据不在缓存中,再次去磁盘上读取数据,此时为物理读,而真正没有在“缓存”中命中的数据,就是这部分物理读,
所以缓存命中率中所谓“命中”的缓存的部分,是包含了已缓存的数据和预读的数据。
但是预读所读取出来的数据,虽然是从磁盘上读取出来的,但是在计算缓存的时候,是把这部分数据当做了缓存的
那么怎么证明呢?
可以通过652这个TRACE禁用预读(read ahead read),再同样的测试,看看现在的缓存命中率
执行DBCC TRACEON(652, -1)之后的测试截图

 


可以看到,本次同样的测试,第一个查询完成之后,第二查询开始,缓存命中率有一个断崖式的下跌,大多数时间是0 ,
平均值也不过是3%的样子(至于为什么存在瞬时缓存命中率的非0的高点,个人猜测是SqlServer缓存的一些进程读取到的元数据缓存)
如果观察IO的话,发现现在的第二个查询没有了预读(read ahead read),全部是物理读(physical read)
这也说明,对于Buffer cache hit ratio这个性能计数器的算法,是把预读读取出来的数据也算作是“缓存”了,如果拿这个值去判断内存瓶颈,是没有参考意义的,当然对于内存瓶颈的判断,可以用其他计数器

 

  问题自己理解起来容易,但是是一边测试一边截图,要做到恰到好处,把问题说明清楚,表达出来真不容易。以后多写些东西锻炼,

 

总结:

在进行内存瓶颈判断的时候,
Buffer cache hit ratio这计数器的值,是不具备参考意义的,即便是观察到Buffer cache hit ratio命中率很高,也不一定代表服务器上没有内存瓶颈,
如果Buffer cache hit ratio命中率很低,极有可能说明存在内存瓶颈,此时还要借助于其他计数器来判断是否存在内存瓶颈,单纯一个Buffer cache hit ratio无法判断内存瓶颈。

 

 

后记,对于自己写的东西,经常是诚惶诚恐,生怕误导了别人,同时发现网上有非常多的文章,提到Buffer cache hit ratio,说的似乎是言之凿凿,具体的参考值都给到了,不知道到底有没有去手动验证一下?

 

最后补上用SQL查询查询缓存命中率的脚本

SELECT 
CAST(CAST((a.cntr_value * 1.0 / b.cntr_value)*100 as int) AS VARCHAR(20)) as BufferCacheHitRatio
FROM (
        SELECT * FROM sys.dm_os_performance_counters
        WHERE counter_name = 'Buffer cache hit ratio'
        AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
        THEN 'SQLServer:Buffer Manager'
        ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +
        ':Buffer Manager' END 
    ) a
CROSS JOIN
(
    SELECT * from sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio base'
    and object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
    THEN 'SQLServer:Buffer Manager'
    ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +
    ':Buffer Manager' END 
) b  

 

posted on 2016-03-13 18:48  MSSQL123  阅读(5046)  评论(3编辑  收藏  举报