none
Reading the output of sys.dm_os_performance_counters

    Question

  •  

    Hello all:

     

    I am a little confused with the output of sys.dm_os_performance_counters DMV.

     

    For example, when it comes to the Buffer cache hit ratio, I am used to seeing a value expressed as a percentage.  The cntr_value in the DMV is a bigint value.

     

    If the cntr_value for the Buffer cache hit ratio is "7003" should I interpret that to mean that the Buffer cache hit ratio is 70.03%?

     

    Here is sample of the output for your reference:

     

    object_name                         counter_name              instance_name     cntr_value     cntr_type

    SQLServer:Buffer Manager     Buffer cache hit ratio                                7003             537003264

     

     

    Monday, April 28, 2008 7:36 PM

Answers

All replies

  • I believe that many of the columns is sys.dm_os_performance_counters are what Microsoft calls 'Raw values", which means that you have to grab them more than once, and do some manipulation between the two raw values.

     

    For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used.

     

    Monday, April 28, 2008 8:09 PM
    Moderator
  • You need to measure it twice in succession and the difference is the hit rate:

    http://www.sqlservercentral.com/Forums/Topic479850-360-1.aspx

    I know some people swear by it, but they're just regurgitating outdated info.

     

    Page life expectancy, free list stalls/sec, lazy writes/sec, stolen pages are much more useful indicators of memory pressure.

    Monday, April 28, 2008 8:09 PM
    Answerer
  • Here is how you would query PLE, (which I agree is more useful than Buffer Cache Hit Ratio)

     

    SELECT cntr_value AS 'Page Life Expectancy'

    FROM sys.dm_os_performance_counters

    WHERE object_name = 'SQLServer:Buffer Manager'

    AND counter_name = 'Page life expectancy'

     

    Monday, April 28, 2008 8:12 PM
    Moderator
  • I'm not trying to debate the relevance of one counter versus another, I'm just trying to understand how to use this DMV.  However, you make good points about the use of other pointers to detect memory pressure.

     

    Thanks for the link to the SQLServerCentral forum.  One of the people who replied posted some code showing how to obtain the percentage value:

     

    Code Snippet

    SELECT ROUND(CAST(A.cntr_value1 AS NUMERIC) / CAST(B.cntr_value2 AS NUMERIC),

    3) AS Buffer_Cache_Hit_Ratio

    FROM (

    SELECT cntr_value AS cntr_value1

    FROM sys.dm_os_performance_counters

    WHERE object_name = 'SQLServer:Buffer Manager'

    AND counter_name = 'Buffer cache hit ratio'

    ) AS A,

    (

    SELECT cntr_value AS cntr_value2

    FROM sys.dm_os_performance_counters

    WHERE object_name = 'SQLServer:Buffer Manager'

    AND counter_name = 'Buffer cache hit ratio base'

    ) AS B;

     

     

    Monday, April 28, 2008 8:31 PM
  •  

    That's what I was referring to - didn't want to pass it off as my own work tho, hence the link.

    I just have a (personal!) issue with that counter - see it in a lot of postings as a panacea to prove/disprove memory pressure, and that's plain wrong.

    Monday, April 28, 2008 8:37 PM
    Answerer
  • Here's a link to a good article about the Sysperfinfo system table in SQL Server 2000, which was the predecessor to sys.dm_os_performance_counters:

    http://www.sqlmag.com/Articles/ArticleID/26950/26950.html?Ad=1

    (may have to log into SQL Mag to see it, I'm not sure)

    As far as I can see, sys.dm_os_performance_counters in SQL Server 2005 is identical to Sysperfinfo in SQL Server 2000 so the article should still apply.  In particular the article describes how to calculate ratios, like the buffer cache hit ratio.  The person who gave you the code above appears to be correct (Buffer cache hit ratio divided by Buffer cache hit ratio base), whereas those who say to measure it twice and compare the results are wrong.

    The article is particularly good in explaining exactly why the buffer cache hit ratio is calculated as it is, and how you can use the same knowledge to calculate other ratios.

    Cheers
    Simon
    Thursday, October 22, 2009 8:04 PM
  • When you say in succession, how much time should there be between runs? 1 sec? 5secs? 30 secs?

    DJ Baby Anne's Biggest Fan................

    Thursday, June 28, 2012 9:44 PM
  • So if PLE is a better indicator, what am I looking for? I can select it multiple times to see it change but what is it actually telling me when my PLE = 410, then 842, then 886 for instance over a 5 second interval?
    Wednesday, May 08, 2013 2:05 PM