Statement to calculate hit ratio

• christian-411818

  Grasshopper

  Points: 16

  More actions

  March 8, 2007 at 7:57 am

  #194483

  Hello I'm quite new in sql-server, does anybody knows a way to calculate memory hit ratio in sql server, just by a sql or T-SQL statement?

• Christopher Ford-327546

  SSCommitted

  Points: 1622

  More actions

  March 8, 2007 at 11:56 pm

  #694003

  DECLARE

  @Base FLOAT

  SET

  @Base = (SELECT cntr_value FROM sys.dm_os_performance_counters

  WHERE object_name = 'SQLServer:Buffer Manager'

  AND counter_name = 'Buffer cache hit ratio base')

   

  select

  p1.object_name, p1.counter_name, p1.instance_name, RTRIM(p1.cntr_value / @Base) as 'Percent'

  FROM

  sys.dm_os_performance_counters p1 WHERE p1.counter_name = 'Buffer cache hit ratio' and p1.object_name = 'SQLServer:Buffer Manager'

   

  That will give you the percent for your Buffer Cache hit ratio...basically the Buffer cache hit ratio is divided by it's base to get the percentage. 

  Christopher Ford