T-SQL Buffer Cache Hit Ratio

Is this script producing an accurate BCHR?  Thanks in advance.

select test1, test2, test1/test2 test3
from      (SELECT cast(cntr_value*100 as float) test1
            FROM master.dbo.sysperfinfo
            WHERE counter_name = 'Buffer cache hit ratio'
            ) a,
            (SELECT cntr_value test2
            FROM master.dbo.sysperfinfo
            WHERE counter_name = 'Buffer cache hit ratio base'
            ) b
Who is Participating?
SharathData EngineerCommented:
try this sql. Refer this for more details: http://sqlserverperformance.wordpress.com/2010/11/18/sql-server-2008-and-2008-r2-diagnostic-information-queries-2/
SELECT ( a.cntr_value * 1.0 / b.cntr_value ) * 100.0 AS [Buffer Cache Hit Ratio] 
  FROM sys.dm_os_performance_counters AS a 
       INNER JOIN (SELECT cntr_value, 
                     FROM sys.dm_os_performance_counters 
                    WHERE counter_name = N'Buffer cache hit ratio base' 
                          AND [OBJECT_NAME] = 
                              N'SQLServer:Buffer Manager') 
                                             AS b -- Modify this if you have named instances 
         ON a.[OBJECT_NAME] = b.[OBJECT_NAME] 
 WHERE a.counter_name = N'Buffer cache hit ratio' 
       AND a.[OBJECT_NAME] = N'SQLServer:Buffer Manager'; -- Modify this if you have named instances 
-- Shows the percentage that SQL Server is finding requested data in memory 
-- A higher percentage is better than a lower percentage 
-- Watch the trend, not the absolute value. 

Open in new window

Jagdish DevakuSr DB ArchitectCommented:

I am unable to check this query as I my SQL Server is on maintenance.

The query seems to be OK.

Please check the below links for more info:



donnatroniousAuthor Commented:
Sharath_123 query gives me the same results as my query.  Thanks for helping me confirm  my query!
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.