Low Cache hit ratio in sql server

Question

Low Cache hit ratio in sql server

k.satya2

Old Hand

Points: 306
More actions
April 14, 2014 at 12:20 pm

#292274

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

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 Old Hand Points: 306 More actions · Answer 2

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 One Orange Chip Points: 28294 More actions · Answer 3

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 Old Hand Points: 306 More actions · Answer 4

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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 Old Hand Points: 306 More actions · Answer 6

Thanks for the information. i will ignore these alerts.