Post reply

Buffer Cache Hit Ratio Alert Shows Incorrect Value

  • September 1, 2009 at 11:52 am

    #209340

    I added an alert for Buffer Cache Hit Ratio < 90 % to a new installation of SS 2008. The current value when running the following query is 100, but I get emails from the alert showing the current value as 1. Perfmon also shows 100 as the counter value. SELECT
    (CAST(SUM(CASE LTRIM(RTRIM(counter_name)) WHEN 'Buffer cache hit ratio' THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT) /
    CAST(SUM(CASE LTRIM(RTRIM(counter_name)) WHEN 'Buffer cache hit ratio base' THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT)) * 100
    AS BufferCacheHitRatio, SUM(cntr_value)
    FROM
    sys.dm_os_performance_counters
    WHERE
    LTRIM(RTRIM([object_name])) LIKE '%:Buffer Manager' AND
    [counter_name] LIKE 'Buffer Cache Hit Ratio%'

  • May 5, 2010 at 4:24 am

    #1162323

    We use a monitoring application called OpManager and were having a similar problem. I used your query to verify that the cache hit ratio was actually hovering as near to 99/100% as possible (many thanks for that).

    For us to resolve it, we have to re-add the server. Ours was related to the fact that the server was a new box and we'd just moved the old installation to a new box and new SS install (2005 -> 2008).

  • May 5, 2010 at 6:37 am

    #1162383

    I saw a suggestion somewhere to set the alert to .90, instead of 90. That resolved the issue for us.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply