SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Buffer cache hit ratio down..


Buffer cache hit ratio down..

Author
Message
Jitendra Padhiyar
Jitendra Padhiyar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 674
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217217 Visits: 46278
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jitendra Padhiyar
Jitendra Padhiyar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 674
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217217 Visits: 46278
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4808 Visits: 2969
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
https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-buffer-cache-hit-ratio/
Jitendra Padhiyar
Jitendra Padhiyar
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 674
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 ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217217 Visits: 46278
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217217 Visits: 46278
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4808 Visits: 2969
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"
Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46503 Visits: 10844
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search