Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Low Cache hit ratio in sql server


Low Cache hit ratio in sql server

Author
Message
k.satya2
k.satya2
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 35
Hi,
recently i received alerts regarding for Low cache hit ratio(50%)for user database.. I verified all ways to getting solution. i checked buffer cache and Page Life Expectancy and server memory utilization as mention below.

1) Buffer cache hit ratio is 100%.
2) Page Life Expectancy is more than 2000 Sec
3) sql server memory utilization is 65%.

Please see below SQL Server version details.

Version: SQL Server2008R2(RTM)
Edition: Enterprise Edition
OS: 64 Bit
Total RAM: 16GB.
i have restricted server min and max memory from 0 to 8GB.

Please help me, how to increase Log cache Hit ratio in sqlserver.

Thanks in advance.
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
Counters look good. Did you experience any memory pressure or had any memory related error messages during the time of that alert ?

--
SQLBuddy
k.satya2
k.satya2
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 35
Yes, all counter are good and showing good result. but sql server did not hit any memory related error's.
i have some input's in mind.

1) i have to apply SP2 for this server.
2) Again restrict server max memory limited up to 10GB.

Is it good performance above mention inputs?
does it work to avoid the log cache hit ratio issues?

if i wrong, please advise me.

Thanks.
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
k.satya2 (4/14/2014)
Yes, all counter are good and showing good result. but sql server did not hit any memory related error's.
i have some input's in mind.

1) i have to apply SP2 for this server.
2) Again restrict server max memory limited up to 10GB.

Is it good performance above mention inputs?
does it work to avoid the log cache hit ratio issues?

if i wrong, please advise me.

Thanks.


You can do both of them. But you should keep monitoring for any memory pressures. Based on the memory pressures, you can re-adjust the max memory setting.

Did you enable Lock Pages in Memory and Optimize for Adhoc workloads options ?

--
SQLBuddy
k.satya2
k.satya2
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 35
No, both options are not enabled. just now i checked in server setting in advanced options both values is set mention below's.

1) Locks value is 0.
2) Optimize for Ad hoc work load is False.

I have one quick question on this.

If i suppose to do enable these options, what outputs will come.
is it caused to any server performance, if i enabled those options?

Thanks.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47299 Visits: 44392
Which counter exactly?

If you really do mean the 'log cache hit ratio' then that's got nothing to do with the buffer pool, nothing to do with optimise for ad-hoc usage and nothing to do with locked pages.

The log cache hit ratio is describes as
"Percentage of log cache reads that were satisfied from the log cache."

Now, reading from the transaction log is not a common activity, I would guess you have replication perhaps? It's also not something I'd expect to see hitting cache, you don't typically read the same log records multiple times.
To be honest, that's a counter I wouldn't even bother looking at, let alone worrying about.


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


k.satya2
k.satya2
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 35
Thanks for the information. i will ignore these alerts.
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