It has been nearly ten years since I first read Cary Millsap’s paper “Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok”.
Long enough, one would think, that it is considered a foregone conclusion that BCHR stuff is not a big concern anymore, except for extreme cases (and I would probably stumble into it backwards while looking at other things, like free buffer waits waits). The paper is now slightly difficult to find, if you don’t know the title. It’s not on method-r.com. One could consider BHCR hysteria to be old news, even.
So, today I get email with this in it:
Database Buffer Cache Hit Ratio is still near 90 – 95% (warning).
And someone asks about it (they asked why it was so high, which was rather humorous). I guess the way it was initially worded may have implied that if it went higher we would really be in trouble. Probably accurate.
The response was this (paraphrased):
The warning means that it should be higher. Well-tuned databases have a 95% -> 99% BCHR, the higher the better. Memory is faster than disk so we want to use cache. The nightly flows flushed the data the OLTP users need, so the BCHR should improve as users log in.
If it doesn’t, we will need to tweak the sizes of the 16K and 8K cache sizes (data is in one block size and indexes are in another) and tune the BCHR separately for each cache.
It seems people are starting to come out of the caves from the Y2K scare.
I replied, referencing Cary’s paper, and went about my business. Prior to sending the reply, I deleted the paragraph stating that if they were really concerned, I would happily install Connor McDonald’s “choose a hit ratio” procedure.
Granted, if used properly (such as keeping track of normal usage patterns and deltas) the BCHR can be useful. Do I have time to do that sort of thing in 700 databases? Certainly not.
I suppose my main point (and objection) is the idea that if a database’s BCHR is x, then that requires action, and that BCHR is to be interpreted in a vacuum. That is just so last century.
For the exhaustive discussion, Charles Hooper has pulled everything together for you.