Thursday, February 26, 2015

Sqlserver 2012 Cache Hit Ratio

Hi,


I am not sure how you are calculating BCHR but below query would give you correct value,Source



SELECT cast((CAST(A.cntr_value1 AS NUMERIC) / CAST(B.cntr_value2 AS NUMERIC))*100 as decimal(10,2)) AS Buffer_Cache_Hit_Ratio

FROM (

SELECT cntr_value AS cntr_value1

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:Buffer Manager'

AND counter_name = 'Buffer cache hit ratio'

) AS A,

(

SELECT cntr_value AS cntr_value2

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:Buffer Manager'

AND counter_name = 'Buffer cache hit ratio base'

) AS B



if you read the article by Jonathan it clearly says don't rely on BCHR for gauging memory pressure




Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it



My Technet Wiki Article


MVP



No comments:

Post a Comment