September 1, 2009 at 11:52 am
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
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
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy