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

Low Cache hit ratio in sql server Expand / Collapse
Author
Message
Posted Monday, April 14, 2014 12:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:48 PM
Points: 4, Visits: 34
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.
Post #1561627
Posted Monday, April 14, 2014 3:01 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:57 AM
Points: 1,194, Visits: 2,234
Counters look good. Did you experience any memory pressure or had any memory related error messages during the time of that alert ?

--
SQLBuddy
Post #1561675
Posted Monday, April 14, 2014 3:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:48 PM
Points: 4, Visits: 34
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.
Post #1561679
Posted Monday, April 14, 2014 3:42 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:57 AM
Points: 1,194, Visits: 2,234
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
Post #1561682
Posted Monday, April 14, 2014 3:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:48 PM
Points: 4, Visits: 34
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.
Post #1561685
Posted Monday, April 14, 2014 5:00 PM


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:14 PM
Points: 40,615, Visits: 37,081
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 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 #1561696
Posted Monday, April 14, 2014 5:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:48 PM
Points: 4, Visits: 34
Thanks for the information. i will ignore these alerts.
Post #1561701
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse