sql server hit ratio

Hi,

I run the sql below every 0.5 hours to get the hit ratio for my sql server database. The results are generally around 0.99 which is fine but now and again i get values like 5 or 150 - how is this possible - surely i cannot get a value above 1 for the hit ratio - please help

thanks
h

DECLARE @value decimal(25,2)
      , @basevalue decimal(25,2)
      , @timestamp datetime
     
SELECT @timestamp=GETDATE()
SELECT @value = cntr_value FROM master..sysperfinfo WHERE counter_name = 'Buffer cache hit ratio'
SELECT @basevalue = cntr_value FROM master..sysperfinfo WHERE counter_name = 'Buffer cache hit ratio base'

insert into systemcachehitratio select @timestamp,  @value / @basevalue

LVL 1
hraja77Asked:
Who is Participating?
 
Daniel_PLDB Expert/ArchitectCommented:
That's because you need to get all values at the same time.
Try following approach :
 
SELECT
  c.object_name,
  c.counter_name,
  CASE
    WHEN b.cntr_value = 0 THEN 0
    ELSE CONVERT(numeric(38,2), c.cntr_value * 1.0 / b.cntr_value)
  END AS value
FROM sys.dm_os_performance_counters AS c
INNER JOIN sys.dm_os_performance_counters AS b
ON c.object_name = b.object_name
AND b.counter_name LIKE RTRIM(c.counter_name) + '%'
AND c.instance_name = b.instance_name
AND b.cntr_type = 1073939712
WHERE c.[object_name] LIKE '%Buffer Manager%'
AND c.counter_name = 'Buffer cache hit ratio';

Open in new window

Code is taken from Pawel Potasinski's blog - Polish SQL Server MVP:
SQL Server DBA-sys for dm _os_performance_counters
0
 
AlexeyKretovCommented:
kklj
0
 
hraja77Author Commented:
?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
hraja77Author Commented:
thanks for that - you could be right - just one last question i need to store the buffer cache hit ratio with as many decimal places as possible - can you help with this

thanks
H
0
 
Daniel_PLDB Expert/ArchitectCommented:
Increase conversion scale?:
ELSE CONVERT(numeric(38,20), c.cntr_value * 1.0 / b.cntr_value)
0
 
hraja77Author Commented:
thanks alot
0
 
Daniel_PLDB Expert/ArchitectCommented:
You're welcome ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.