June 14, 2013 at 5:35 am
Hello Masters,
I had SQL Server 2005 SP3 on my server and from yesterday I got an alert that "Buffer cache hit ratio is less than 10%" I monitored it for 2-3 hours, also checked memory which is more than sufficient (32GB). AWE is also enabled. Checked Error logs and event logs not seen any errors too. Whats the factor that get down this buffer cache hit ration down ?
How can i up this to its normal count ? Please advice.
BR,
Jitendra
June 14, 2013 at 5:40 am
Investigate the queries running against the instance. Identify why they're reading so much data
Edit: Sorry, originally read that as plan cache hit ratio, ignore what was here before...
Low buffer cache hit ratio means that queries are reading huge amounts of data, more than will fit into memory. Hence SQL has to keep discarding what's in memory and reading more and more from disk.
Identify the queries that are doing the most reads. See if you can identify why. Tune those queries to do less reads.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2013 at 5:53 am
Thanks Gila for the quick response!
I use sp_who2 to find out current running processes. I can lots of process running, how can i identify that specific apps those wants more memory ?
One more thing, as I mentioned I enabled AWE, how much time it took to take in effect ? According to my knowledge its hot swap dont need restart.
June 14, 2013 at 6:01 am
Above post edited heavily, take another read through it.
One more thing, as I mentioned I enabled AWE, how much time it took to take in effect ? According to my knowledge its hot swap dont need restart
Huh?
AWE has nothing to do with hot swapping memory. AWE is used for 32-bit processes to access memory above the 4GB limit. Unless this is a 32-bit instance, AWE is useless.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2013 at 6:09 am
jitendra.padhiyar (6/14/2013)
Hello Masters,I had SQL Server 2005 SP3 on my server and from yesterday I got an alert that "Buffer cache hit ratio is less than 10%" I monitored it for 2-3 hours, also checked memory which is more than sufficient (32GB). AWE is also enabled. Checked Error logs and event logs not seen any errors too. Whats the factor that get down this buffer cache hit ration down ?
How can i up this to its normal count ? Please advice.
BR,
Jitendra
A bit off topic but I stay away from using BCHR as a performance metric - It's extremely unreliable.
Rather use Page Life Expectancy
Have a look here
June 14, 2013 at 6:15 am
Sorry Gila I did not read edited data.
My current SP is SP3 on MSSQL Server 2005, My one of collegue told me that upgrading SP3 to SP4 Or upgrading 32bit to 64 bit will solve the issue .. !!
Is it ? Upgrading Service pack can solve issue ?
June 14, 2013 at 6:21 am
Service pack, no, probably not. Is this a 32-bit instance?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2013 at 6:24 am
SQLSACT (6/14/2013)
A bit off topic but I stay away from using BCHR as a performance metric - It's extremely unreliable.
It's not that it's unreliable, it's that it can have a very high value even while the server is under severe memory pressure. In essence, the counter gives false negatives as an indication of memory pressure, it indicates that everything's fine when it's not.
If however it is very low (as in the OP's situation), then the server is exceedingly likely to be under horrid, severe memory pressure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2013 at 6:31 am
GilaMonster (6/14/2013)
SQLSACT (6/14/2013)
A bit off topic but I stay away from using BCHR as a performance metric - It's extremely unreliable.It's not that it's unreliable, it's that it can have a very high value even while the server is under severe memory pressure. In essence, the counter gives false negatives as an indication of memory pressure, it indicates that everything's fine when it's not.
If however it is very low (as in the OP's situation), then the server is exceedingly likely to be under horrid, severe memory pressure.
Completely agree Gail
As Jonathan Kehayias suggested "To put it another way, the BCHR early warning alert is only raised once the house is already burning down"
June 14, 2013 at 6:40 am
I don't know if we had the 10% hit problem you're having, but I do remember a 32-bit 2005 instance that kept having log entries saying that it was under memory pressure. After researching it, we decided to upgrade to SP4 and that did actually solve the problem.
I was never able to figure out exactly what was causing the memory pressure. While I would have preferred to solve the problem instead of just installing SP4, I had my own pressure to "fix it" sooner rather than later.
June 17, 2013 at 5:50 am
Gila Monster, Can you please answer my questions ?
========================================
Hello Guys,
I analysed one thing, the machine which is facing low cache hit ratio issue is in cluster and its currently in passive mode. On active node everything is fine which should be. In the past we did not recieved any such alerts from any cluster.
Now my question is
What should be the cache hit ratio of Passive node ? should be 0% ?
why we recieved low cache hit ratio alerts for PASSIVE NODE ?
Is cache hit ratio of Passive node should be low OR 0% ?
Thanks in advance,
Jitendra
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply