| Author |
Topic |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
 Posted - 2008-06-19 : 10:34:01
|
| I am wondering if 100% buffer cache hit ratio is considered not good in general? Are there instances that it is actually bad and can contribute to server performance degradation?Any thoughts on the topic most welcome :)--------------------keeping it simple... |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-06-20 : 03:31:37
|
| Personally I would consider it execellent. 100% cache hit ratio means that SQL can read all the data pages from memory, never having to go to disk. Seeing as disk is many times slower than memory, is good.Dunno if anyone else has a different experience.I admit, I've never seen a system with 100% hit ratio. 99.8% is the highest I've ever had.--Gail Shaw |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-20 : 08:07:54
|
| I have never seen too. Its excellent. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-06-20 : 09:34:12
|
@GilaMonster:you haven't seen jen's systems yet  _______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
contrari4n
Starting Member
27 Posts |
Posted - 2008-06-20 : 15:53:30
|
| I've seen systems with high buffer cache hit ratio performing quite badly. I always look at the "Page Life Expectancy" counter. It seems to be a much better indicator of memory pressure.Richardhttp://www.sql-server-pro.comSQL Server Articles and Tips |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-20 : 23:38:49
|
| >> I've seen systems with high buffer cache hit ratio performing quite badly. Agree, especially sql does read ahead. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-06-21 : 07:36:03
|
| rmiao, can you explain more about this? or post some helpful links?_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-21 : 10:58:19
|
quote:
Originally posted by GilaMonster
99.8% is the highest I've ever had.
Personal best: 100.7%
E 12°55'05.25"N 56°04'39.16" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-06-21 : 11:05:36
|
| ok peter now you're just messing with us _______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-21 : 11:08:39
|
Nope. I managed to put whole 1.9 gig database in RAM memory.So I cheated, so to speak.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-21 : 16:47:12
|
| >> can you explain more about this?You may find that on your servers too, buffer cache hit ratio is almost 100% but server still has very high disk i/o. Because of read ahead, chance of buffer cache hit will be higher but pages may not stay in cache long. That's why I rely more on page life expectancy for memory issues. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-06-23 : 01:50:46
|
quote:
Originally posted by Peso
Personal best: 100.7%
I really love perfmon sometimes. I had a disk 120% idle the other week. (Not a san or array, a single disk).--Gail Shaw |
|
|
|
|
100% buffer cache hit ratio not always optimal?