Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Buffer cache hit ratio down.. Expand / Collapse
Author
Message
Posted Friday, June 14, 2013 5:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:51 AM
Points: 203, Visits: 538
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

Post #1463488
Posted Friday, June 14, 2013 5:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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

Post #1463492
Posted Friday, June 14, 2013 5:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:51 AM
Points: 203, Visits: 538
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.
Post #1463496
Posted Friday, June 14, 2013 6:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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

Post #1463500
Posted Friday, June 14, 2013 6:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:44 AM
Points: 1,371, Visits: 2,597
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/
Post #1463504
Posted Friday, June 14, 2013 6:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:51 AM
Points: 203, Visits: 538
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 ?
Post #1463512
Posted Friday, June 14, 2013 6:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
Service pack, no, probably not. Is this a 32-bit instance?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1463515
Posted Friday, June 14, 2013 6:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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

Post #1463520
Posted Friday, June 14, 2013 6:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:44 AM
Points: 1,371, Visits: 2,597
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"
Post #1463525
Posted Friday, June 14, 2013 6:40 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 3,323, Visits: 2,373
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
Best practices on how to ask questions
Post #1463530
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse