Tuesday, January 11, 2011

Why my Buffercache hit ratio is High but Page Life expectancy is very low?

Buffer cache it ratio and Page Life expectancy are the two important counters which helps to identiy SQL Server memory issue.I have often seen the scenarios where these two counters conflict each other.

Recently I was monitoring one production SQL Server using spotlight and constantly I was getting pagelifeexpectancy alert but when i sneaked into the BufferCacheHit ratio counter, it was 99.6%.This is especially in the case of 32 bit SQL Server.

So the question came to my mind was "is their really Memory bottleneck?"

Answer is "Yes". What this mean is logical reads are still using buffer  but the pages are not staying their long enough also.

I would prefer PageLifeExpctancy alert over BufferCachHitRatio.Ideally only buffer cache hit ratio is not enough to conclude memory bottleneck.

Also in this kind of scenario i would look at IO wait as well.

No comments:

Post a Comment