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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:



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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
donnatroniousAuthor Commented:
Sharath_123 query gives me the same results as my query.  Thanks for helping me confirm  my query!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.