SQL Server Performance

Buffer Cache Stats - varying results - help!

Discussion in 'Performance Tuning for DBAs' started by DrZol, Mar 6, 2004.

  1. DrZol New Member

    Hello:

    I'm having a problem reconciling the results of two scripts, using sysperfinfo the return the percentage of Buffer Cache Hit Ratio. MOst often they are close within a 1/2-1 percentage, but sometimes I get a huge discrepancy. If I run them in the same instance of sql query analyzer (so its at the same time), I can get 65% from one result, and 99% from the other. You will probably recognize the script and formulas below. I need to provide on-going accurate results. Can someone analyze these scripts and help me figure out why they (sometimes) yield different results? TIA!

    SCRIPT#1
    ---------
    select convert(decimal(15,2),(t1.cntr_value*1.0/t2.cntr_value*1.0 *100.0))
    from master.dbo.sysperfinfo t1,
    master.dbo.sysperfinfo t2
    where t1.object_name = 'SQLServer:Buffer Manager'
    and t2.object_name = 'SQLServer:Buffer Manager'
    and t1.counter_name = 'Buffer Cache Hit Ratio'
    and t2.counter_name = 'Buffer Cache Hit Ratio Base'
    and t1.instance_name = t2.instance_name
    and t2.cntr_value > 0

    --------------------------------------------------------------------------

    SCRIPT#2 this gets total buffer cache, used, free, and the percentage (this is what I need)
    ---------
    DECLARE @total_memory_kb float,
    @buffer_cache float,
    @buffer_cache_free_kb float,
    @free_pages float,
    @buffer_cache_used float

    select @total_memory_kb = cntr_value
    from master.dbo.sysperfinfo
    where object_name = 'SQLServer:Memory Manager' and
    counter_name = 'Total Server Memory (KB)'

    select @free_pages=(isnull(cntr_value,0) * 8192)/1024--/1024
    from master.dbo.sysperfinfo
    where object_name = 'SQLServer:Buffer Manager' and
    counter_name = 'Free Pages'

    select @buffer_cache=(isnull(cntr_value,0) * 8192)/1024--/1024
    from master.dbo.sysperfinfo
    where object_name = 'SQLServer:Buffer Manager' and
    counter_name = 'Total Pages'

    select @buffer_cache_used=@buffer_cache-@free_pages

    select bcu=(@buffer_cache-@free_pages)/1024,tbc=@buffer_cache/1024,fbc=@free_pages/1024,bc_pct=(@buffer_cache_used/@buffer_cache)*100
  2. satya Moderator

    Suggestion is not to query the system tables directly and have you tried to collecting the stats using PERFMON(SYSMON) for this counter, which gives accurate results I believe.

    From the tips page on this website :
    Unlike many of the other counters available for monitoring SQL Server, this counter averages the Buffer Cache Hit Ratio from the time the last instance of SQL Server was restarted. In other words, this counter is not a real-time measurement, but an average of all the days since SQL Server was last restarted. Because of this, if you really want to get an accurate record of what is happening in your Buffer Cache right now, you must stop and restart the SQL Server service, then letting SQL Server run several hours of normal activity before you check this figure (in order to get a good reading).



    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. DrZol New Member

    Thank you Satya, for this informative reply. Are you saying that that SCRIPT#2, might be more accurate? So, if it is an average of the last time the instance was started, that means over time, (if you don't frequently restart), the result becomes less accurate?.
    Mmm.

    My goal was to run such a script in a scheduled job, and alert if the Buffer Cache Hit Ratio drops below a certain number? (Not using perfmon)

    Any further advice? TIA!


    quote:Originally posted by satya

    Suggestion is not to query the system tables directly and have you tried to collecting the stats using PERFMON(SYSMON) for this counter, which gives accurate results I believe.

    From the tips page on this website :
    Unlike many of the other counters available for monitoring SQL Server, this counter averages the Buffer Cache Hit Ratio from the time the last instance of SQL Server was restarted. In other words, this counter is not a real-time measurement, but an average of all the days since SQL Server was last restarted. Because of this, if you really want to get an accurate record of what is happening in your Buffer Cache right now, you must stop and restart the SQL Server service, then letting SQL Server run several hours of normal activity before you check this figure (in order to get a good reading).



    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. satya Moderator

    As I said in the future releases MS may change the table structure without any warning and that may result your alert inaffective (a futuristic thought <img src='/community/emoticons/emotion-5.gif' alt=';-)' /> ).<br /><br /><br />In your case yes the second query should be fine, and you may also schedule a PERFMON trace and export to excel on daily basis to monitor, for the accurate result.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of knowledge sharing.</font id="size1"></font id="teal"></center>
  5. DrZol New Member

    Ok, totally understood. Final Q?: So, if the second query yields say 65%, while the sysperfinfo (first one) yields 99%, should that be a concern? I mean should I be more concerned about the average (query one), than the (maybe) more real time one, query two?<br /><br />Appreciated! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />As I said in the future releases MS may change the table structure without any warning and that may result your alert inaffective (a futuristic thought <img src='/community/emoticons/emotion-5.gif' alt=';-)' /> ).<br /><br /><br />In your case yes the second query should be fine, and you may also schedule a PERFMON trace and export to excel on daily basis to monitor, for the accurate result.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of knowledge sharing.</font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote">
  6. satya Moderator

    In my case I will go with real one rather than depending on Average and confirm with PERFMON results too.

    BTW what are memory settings on SQL?
    Any parallel applications sharing resources?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. DrZol New Member

    The memory on most sql servers, are dynamic, and are dedicated sql servers.

    This kinda shatters my world, in a sense, b/c I remember articles (possibly on this site) that use the sysperinfo queries, and tell you if this number is below say 90%, then it means possibly add new memory, etc. But, if I go with the other one, it may reflect a point in time, and appears not at all optimal buffer cache? Does this make sense[:0]


    quote:Originally posted by satya

    In my case I will go with real one rather than depending on Average and confirm with PERFMON results too.

    BTW what are memory settings on SQL?
    Any parallel applications sharing resources?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. satya Moderator

    Yes its true and you would go with query #1 to check its affected.
    In this case consider other factors such as memory-pages/sec, processor-processor time%, process, SQL server locks & sql server -cachehit ratio counters.

    Any chance of implementing third party tools such as BMC or Spotlight in your case.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. DrZol New Member

    I was trying to stay away from 3rd pty tools at this time, but trying to come up with some acceptable in-house solution. I am beginning to understand the connection between all the other factors (which are a little confusing)

    If I use both queries, to an average user, who would see my results, would say "whoa, why the discrepancy - which do we believe?" But, I suppose, I can say, one is the average over-time, the other is the point-in-time stat. However, which one should be the one I use to alert (just to confirm your opinion, based on our recent postings)

    Satya, you are best! Thx


    quote:Originally posted by satya

    Yes its true and you would go with query #1 to check its affected.
    In this case consider other factors such as memory-pages/sec, processor-processor time%, process, SQL server locks & sql server -cachehit ratio counters.

    Any chance of implementing third party tools such as BMC or Spotlight in your case.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. DrZol New Member

    Some more follow-up:
    Based on our discussion, I tried the following:
    I restarted SQLServer and immediately ran SCRIPT#2, it consistently show a number of about 80% (over about 10 minutes), SCRIPT #1, immediately (as well as perfmon) showed 99%?
    It never hit that with SCRIPT#2. So, I still am very confused. Please advise[B)]


    quote:Originally posted by satya

    Yes its true and you would go with query #1 to check its affected.
    In this case consider other factors such as memory-pages/sec, processor-processor time%, process, SQL server locks & sql server -cachehit ratio counters.

    Any chance of implementing third party tools such as BMC or Spotlight in your case.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. satya Moderator

    As I said above I would go with Script #2 rather than going for Average values.
    And the same to tally with PERFMON results, for assurance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. DrZol New Member

    Hi, again:

    I've given this some thought, and I hate to beat a dead horse, but how can I have confidence in SCRIPT#2, if SCRIPT #1 looks like PerfMon, which is supposed to be accurate. I mean, for ex, if I were to set up a condition/performance alert to fall under 95% via perfmon, and according to it is 98%, and SCRIPT#2 gives me 80%, why go w/ that one.......I'm sorry, I seem to be going in circles. I guess what I really want to know, is SCRIPT#2 formula a correct representation.? Thx, much!


    quote:Originally posted by satya

    As I said above I would go with Script #2 rather than going for Average values.
    And the same to tally with PERFMON results, for assurance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. satya Moderator

  14. brinster New Member

    Hello

    When i make the second request all the values are null
    Why ?

    regards
  15. satya Moderator

    When was the server last started?
    How about PERFMON stats?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  16. DrZol New Member

    I also on occasion on some servers, get negative numbers (consistantly) with the second script? How can you explain that one?



    quote:Originally posted by satya

    When was the server last started?
    How about PERFMON stats?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  17. satya Moderator

    This is the one of the reason I suggested above:Suggestion is not to query the system tables directly and have you tried to collecting the stats using PERFMON(SYSMON) for this counter, which gives accurate results I believe.

    To be frank I never attempt query against system tables until unless its must and should.
    I would suggest you to go with PERFMON results if in doubt with the script.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page