Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 691
  • Last Modified:

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

0
hraja77
Asked:
hraja77
  • 3
  • 3
1 Solution
 
AlexeyKretovCommented:
kklj
0
 
hraja77Author Commented:
?
0
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now