德瑞克：SQL Server 學習筆記: [SQL Server] Buffer cache hit ratio over 100% ?

2017-07-29

[SQL Server] Buffer cache hit ratio over 100% ?

觀察 Buffer cache hit ratio 超過 100 ?

解決方案是： Buffer cache hit ratio 與 Buffer cache hit ratio base 一併列入計算。

對於Buffer cache hit ratio 的建議值，多半是 90%、95% 以上。
因為這表示不需讀取磁碟即可在緩衝區快取中找到之頁面的百分比。從快取讀取遠比從磁碟讀取節省成本，因此此比率越高越好。
但實際去觀察此比率值時，卻是超過 100% ？

示範版本
SQL Server 2014、SQL Server 2016

-- 001_Buffer cache hit ratio over 100_SQL2016

-- 002_Buffer cache hit ratio over 100_SQL2014

查詢效能計數器 Buffer cache hit ratio

-- Query: Buffer Cache Hit Ratio
USE  master
GO
SELECT object_name, counter_name, cntr_value, @@VERSION
FROM sys.dm_os_performance_counters 
WHERE [counter_name] = 'Buffer cache hit ratio';
GO

為什麼 Buffer cache hit ratio 值會破百?

應使用 Buffer cache hit ratio 搭配 Buffer cache hit ratio base 一併列入計算

計算 Buffer cache hit ratio(%)

-- Calculate: Buffer cache hit ratio(%)
/*
Calculate: Buffer cache hit ratio(%) = 100.0 * Buffer cache hit ratio / Buffer cache hit ratio base
SQL Server 2012 edition
*/
USE  master
GO
DECLARE @bchr INT, @bchrb INT
SET @bchr = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Buffer cache hit ratio')
SET @bchrb =(SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Buffer cache hit ratio base')

SELECT 100.0 * @bchr/@bchrb 'Calculate: Buffer cache hit ratio(%)', 
 @bchr 'Counter: Buffer cache hit ratio', 
 @bchrb 'Counter: Buffer cache hit ratio base'
GO

-- 003_計算_Buffer cache hit ratio(%)

觀察兩個計數器 Buffer Cache Hit Ratio, Buffer cache hit ratio base

-- Counter: Buffer Cache Hit Ratio, Buffer cache hit ratio base                                                                                                     
USE  master
GO
SELECT object_name, counter_name, cntr_value, @@VERSION
FROM sys.dm_os_performance_counters 
WHERE [counter_name] LIKE 'Buffer cache hit ratio%';
GO

-- 004_觀察這兩個計數器

-- 009_效能監視器的Buffer cache hit ratio是正確的

SQL Server 2012 Memory Manager 的改變

這是因自 SQL Server 2012 版本開始，在記憶體的管理應用以及DMV上有許多改變。

-- 005_SQL Server 2012 Memory_Manager

在 SQL Server 的 Buffer Manager 物件裡的 Buffer cache hit ratio，需要搭配 Buffer cache hit ratio base 一併列入運算。

Calculate: Buffer cache hit ratio(%) = 100.0 * Buffer cache hit ratio / Buffer cache hit ratio base

Memory Manager surface area changes in SQL Server 2012

Interpreting the counter values from sys.dm_os_performance_counters

SQL Server 線上說明

尚未找到與此相關的說明

Buffer cache hit ratio
表示不需讀取磁碟即可在緩衝區快取中找到之頁面的百分比。
此比率是過去數千個分頁存取中，快取叫用總數除以快取查閱所得的結果。
時間一久，比率的變動會越來越小。從快取讀取遠比從磁碟讀取節省成本，因此您會希望此比率越高越好。
通常，您可以藉由增加 SQL Server 可用的記憶體數量或是使用緩衝集區擴充功能，來提高緩衝區快取叫用比率。

-- 010_線上說明_僅Buffer cache hit ratio