SQL Server Performance

Buffer Cache Hit Ratio

Discussion in 'General DBA Questions' started by chetanjain04, Jul 14, 2007.

  1. chetanjain04 Member

    Hello,

    Is it possible to capture buffer cache hit ratio using t-sql script:

    I am going to try this:

    declare @num float
    declare @dec float
    select @num = cntr_value from master..sysperfinfo where object_name = 'SQLServer:Buffer Manager' and counter_name = 'Buffer cache hit ratio'
    select @dec = cntr_value from master..sysperfinfo where object_name = 'SQLServer:Buffer Manager' and counter_name = 'Buffer cache hit ratio base'

    select cast( @num*100/@dec as numeric(5, 2))

    Let me know if anybody has tried this way of capturing BCHR.




    Best Regards,

    Chetan
    "Calm seas can never make skillful sailors".
  2. MohammedU New Member

  3. chetanjain04 Member

    Hello ,

    I have written a SP to capture the data of sysperfinfo from each SQL server (we have about 150 instances). However, for some instances there is no data at all in the sysperfinfo table. What could be the problem?

    The query is very simple:

    select * from openquery ( [linkedserver], ''select datepart(hh, getdate()) as db_hour, convert(varchar(10),getdate(), 121), cast ((SELECT cast(cntr_value*100 as float) FROM master.dbo.sysperfinfo WHERE counter_name = ''''Buffer cache hit ratio'''' ) / (SELECT cntr_value FROM master.dbo.sysperfinfo WHERE counter_name = ''''Buffer cache hit ratio base'''') as numeric(5, 2))'' ) '

    Best Regards,

    Chetan
    "Calm seas can never make skillful sailors".
  4. MohammedU New Member

  5. satya Moderator

    If you are on pre-sp3 then better as soon as get on to SP4 as it is not supported anymore, missing data sounds like few PERFMON counters missing on those servers. IN this case you have to contact MS PSS for a bug fix solution: PSS ID Number: 812915 , BUG#: 363762 (SHILOH_bugs)



    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page