January 25, 2009 at 8:57 am
Hi,
I've sql server 2005 with sp2 and i've setup the alert for the
"MSSQL$PRI2K5:Buffer Manager\Buffer cache hit ratio"
false below 90.
this alert was never fire before.
after that i install SQL SERVER 2005 SP3 this alert firing all the time.
i've looked in the Perfmon and everything looks good.
is it a bug in the sp3?
THX
January 25, 2009 at 9:43 am
Has anything else changed? Is this right after a reboot or has it been continuing for some time since you installed the SP?
January 25, 2009 at 10:05 am
right after installing sp3 and reboot the server,this alert start to come up.
has anyone that have sp3 install and setup this alert in the SQL alert doesn't suffer from this event?
THX
January 25, 2009 at 10:11 am
The first few queries after a reboot should have a lower ratio.
I haven't seen anything reported on this before. Do you know what the ratio was before, 91%? 95%?
January 25, 2009 at 10:19 am
no the cache hit ratio is always above 99%.
and the alerts continue.
for now i disable this alert.
January 25, 2009 at 10:34 am
Mad-Dog (1/25/2009)
right after installing sp3 and reboot the server,this alert start to come up.has anyone that have sp3 install and setup this alert in the SQL alert doesn't suffer from this event?
THX
After a stop/start of SQLserver buffers need to be loaded, so until the instances load is back to "regular", buffer cache may jump around.
Are your regular applications already up and running ?
Have a little patience, if it keeps on firing these alerts after a normal day of work, then you'll have to figure out what's wrong.
Maybe have a look at SP3 CU1 if it refers to buffer issues.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 25, 2009 at 10:57 am
no guys it's not relate to the server that was stop or start or anything like that.
January 25, 2009 at 7:25 pm
I haven't heard anyone reporting memory issues from SP3 at all. The only thing I've seen with SP3 relates to installing with a cluster.
My guess is that something else must have changed near the same time.
Personally, I wouldn't set an alert on this. No reason for it. Some large load or query could easily trip this down and it wouldn't be cause for concern. As a matter of course, most memory counters aren't worth setting alerts on. You watch them periodically with benchmarks and look for changes, but not spot values dropping.
January 25, 2009 at 11:48 pm
I've installed this sp3 on 2 sql servers and both making this same issue.
right after installing sp3 and restart the server those alerts continue firing all the time.
January 26, 2009 at 12:06 am
how long are they up and running after SP3 ?
Keep in mind SP3 stops and starts sqlserver a number of times, so buffer cache will be cleared !
(our clustered instances all have a nice 99,8 buffer cache hit ratio :Whistling: )
/*
* http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=107424
*/
/* Query That Returns Calculated SQL Server Performance Counter Values
*
* SQL2005 variant using the DMV
*/
SELECT dmv_cntr.[object_name]
, dmv_cntr.counter_name
, dmv_cntr.instance_name
, dmv_cntr.cntr_value
, dmv_cntr.cntr_type
, CASE dmv_cntr.cntr_type
WHEN 537003264 -- This counter is expressed as a ratio and requires calculation. (Sql2005)
THEN CONVERT(FLOAT,dmv_cntr.cntr_value) /coalesce(RatioBaseValue.cntr_RatioBaseValue,-1)
ELSE dmv_cntr.cntr_value -- The values of the other counter types are already calculated.
END as Calculated_Counter_value
, CASE dmv_cntr.cntr_type
WHEN 537003264 -- This counter is expressed as a ratio and requires calculation. (Sql2005)
THEN RatioBaseValue.cntr_RatioBaseValue
ELSE 1 -- The values of the other counter types are already calculated.
END as cntr_RatioBaseValue
FROM sys.dm_os_performance_counters dmv_cntr
left join ( SELECT CASE cntr_value
WHEN 0 THEN 1
ELSE cntr_value
END as cntr_RatioBaseValue
, counter_name
, [object_name]
, instance_name
, cntr_type
FROM sys.dm_os_performance_counters
WHERE cntr_type = 1073939712
) RatioBaseValue
on dmv_cntr.counter_name + ' '= SUBSTRING(RatioBaseValue.counter_name, 1, PATINDEX('% Base%', RatioBaseValue.counter_name))
AND dmv_cntr.[object_name] = RatioBaseValue.[object_name]
AND dmv_cntr.instance_name = RatioBaseValue.instance_name
WHERE dmv_cntr.cntr_type <> 1073939712 -- Don't display the divisors. (RatioBaseValue)
-- and dmv_cntr.instance_name like '%key%'
order by 1,2,3
/* SQLServer instance uptime */
SELECT @@servername as ServerName
, login_time
, datediff(mi, login_time, getdate()) as SQLServer_UpTime_Minutes
FROM sys.dm_exec_sessions
WHERE session_id = 1
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 26, 2009 at 1:16 am
what i'm trying to say that the alert is firing continually after that the server runs for a couple of days.
i've opened a bug in MS SQL SERVER.
let's see what they will say about it.
January 26, 2009 at 1:36 am
I didn't experience that overhere.
- do you rebuild indexes on a regular basis ?
- Are there any "missing indexes" reported ? (large numbers)
This is a very informative article:
MS best practise article "SQLserver 2005 waits and queues"
It states:
Memory rule 4
"SQL cache hit ratio" | < 90% | if <90% for sustained periods of time greater than 60sec | It is likely that large scans have to be performed, which in turn flushes out the buffer cache.
So IMO the response interval for this alert should be set to at least 60 seconds.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 19, 2009 at 8:04 am
This is really a problem. After SP3 I was getting the very same error:
DESCRIPTION:The SQL Server performance counter 'Buffer cache hit ratio' (instance 'N/A') of object 'SQLServer:Buffer Manager' is now below the threshold of 98.00 (the current value is 1.00).
I found a workaround here: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=405505
Just set the threshold with 0. before the value. In my case the threshold is 0.98. Seems to work, but I can't assure it's right.
Regards,
Lero
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply