Hi Friends,

As our sqlserver 2012 show  Cache Hit Ratio=   0.66. I want to inquire in order to get 95%  as cache hit ratio ,which is optimal way, or   I have to add more memory to server, while I am using following query.

DECLARE

 @PERF_LARGE_RAW_FRACTION INT ,

@PERF_LARGE_RAW_BASE INT

SELECT
  @PERF_LARGE_RAW_FRACTION = 537003264 ,
@PERF_LARGE_RAW_BASE = 1073939712

SELECT

  dopc_fraction.object_name AS [Performance object],
dopc_fraction.instance_name AS [Counter instance],
dopc_fraction.counter_name AS [Counter name],
--when divisor is 0, return I return NULL to indicate
--divide by 0/no values captured

CONVERT(DECIMAL(38,2), CAST(dopc_fraction.cntr_value AS FLOAT)

/ CAST(CASE dopc_base.cntr_value

WHEN 0 THEN NULL
ELSE dopc_base.cntr_value
END AS FLOAT)) AS [Value]
FROM
   sys.dm_os_performance_counters AS dopc_base
JOIN sys.dm_os_performance_counters AS dopc_fraction
ON dopc_base.cntr_type = @PERF_LARGE_RAW_BASE
AND dopc_fraction.cntr_type = @PERF_LARGE_RAW_FRACTION
AND dopc_base.object_name = dopc_fraction.object_name
AND dopc_base.instance_name = dopc_fraction.instance_name
AND ( REPLACE(UPPER(dopc_base.counter_name), 'BASE', '') =

UPPER(dopc_fraction.counter_name)
--Worktables From Cache has "odd" name where
--Ratio was left off
OR REPLACE(UPPER(dopc_base.counter_name), 'BASE', '') =
REPLACE(UPPER(dopc_fraction.counter_name), 'RATIO', '')
)

ORDER
 BY dopc_fraction.object_name ,

dopc_fraction.instance_name ,
dopc_fraction.counter_name

thank you.

regards,

asad

As per the BOL, Buffer cache hit ratio(BCHR) can increased by increasing the amount of memory available to SQL Server or by using the buffer pool extension feature(introduced in SQL Sever 2014 ).

https://msdn.microsoft.com/en-us/library/ms189628%28v=sql.120%29.aspx
https://msdn.microsoft.com/en-us/library/dn133176.aspx

Some interesting reading about BCHR.
https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-buffer-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 pr

I concur with Shanky. Instead of BCHR, use Page Life Expectancy to check for memor