disk IO and buffer cache hit ratio

Hi Everyone,
I always notice that the buffer cache hit ratio suddenly dips down if
there's a spike in disk IO (indicated by % disk time or disk queue
length) and immediately recovers. What's the connection between the
two or the possible explanation of this behavior?
Thanks.
Aramid
If the buffer hit cache ratio dips down that means you're getting cache
misses (cache misses/hits are inversely proportional). That is, the
data page(s) you're looking for in cache is not there so it has to be
retrieved from disk (hence the disk spikes).
This indicates to me, if it's happening quite often, that you're a bit
short on memory. You should also watch the "Buffer Manager | Page Life
Expectancy" perfmon counter (measured in seconds). This will give you
an idea of roughly how long SQL Server expects the average data page to
remain in cache before needing to be swapped out (usually due to memory
pressure). I'd want to keep that figure to be at least 5 minutes but
ideally it should be higher, like an hour or more, IMO.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Aramid wrote:

>Hi Everyone,
>I always notice that the buffer cache hit ratio suddenly dips down if
>there's a spike in disk IO (indicated by % disk time or disk queue
>length) and immediately recovers. What's the connection between the
>two or the possible explanation of this behavior?
>Thanks.
>Aramid
>
|||Thanks Mike, that was really helpful. But is it also possible for an
increase in disk IO (not related to cache lookups) to affect the
buffer cache ratio?
Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
and I noticed that it is constantly increasing over time, then there
would be a sudden surge of disk IO activity accompanied by a sudden
drop of the Page Life Expectancy value. Does this indicate SQL's
process of swapping out infrequently accessed information from cache
to disk?
Thanks again.
Aramid
On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:

>If the buffer hit cache ratio dips down that means you're getting cache
>misses (cache misses/hits are inversely proportional). That is, the
>data page(s) you're looking for in cache is not there so it has to be
>retrieved from disk (hence the disk spikes).
>This indicates to me, if it's happening quite often, that you're a bit
>short on memory. You should also watch the "Buffer Manager | Page Life
>Expectancy" perfmon counter (measured in seconds). This will give you
>an idea of roughly how long SQL Server expects the average data page to
>remain in cache before needing to be swapped out (usually due to memory
>pressure). I'd want to keep that figure to be at least 5 minutes but
>ideally it should be higher, like an hour or more, IMO.
|||That too indicates you are short on memory. When SQL Server needs to bring
in more data from disk it has to free up existing cache to hold it. This is
reflected in the large dip in PLE as it clears out portions of the cache.
This is normal on an occasional basis but if you see it often that is a sure
sign of lack of memory or poorly tuned queries and database.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com...
> Thanks Mike, that was really helpful. But is it also possible for an
> increase in disk IO (not related to cache lookups) to affect the
> buffer cache ratio?
> Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
> and I noticed that it is constantly increasing over time, then there
> would be a sudden surge of disk IO activity accompanied by a sudden
> drop of the Page Life Expectancy value. Does this indicate SQL's
> process of swapping out infrequently accessed information from cache
> to disk?
> Thanks again.
> Aramid
> On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
> <mike.hodgson@.mallesons.nospam.com> wrote:
>
|||Aramid wrote:

>Thanks Mike, that was really helpful. But is it also possible for an
>increase in disk IO (not related to cache lookups) to affect the
>buffer cache ratio?
>
No. The buffer cache hit ratio only indicates cache hits or misses -
memory only. Sudden non-related disk I/O would probably affect the
response time from your SQL server but it shouldn't affect the hit/miss
ratio of the buffer cache.

>Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
>and I noticed that it is constantly increasing over time, then there
>would be a sudden surge of disk IO activity accompanied by a sudden
>drop of the Page Life Expectancy value. Does this indicate SQL's
>process of swapping out infrequently accessed information from cache
>to disk?
>
Pretty much, yeah.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
|||Hi Andrew,
Thanks for your reply.
In short, sudden clearing of the cache is not really done in regular
intervals (or perhaps in very small amounts?), but rather on an ad-hoc
basis, whenever SQL needs to move in chunks of data from disk. Is
this correct?
How often then is acceptable? Or how can I make this a basis for lack
of memory?
Aramid
On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>That too indicates you are short on memory. When SQL Server needs to bring
>in more data from disk it has to free up existing cache to hold it. This is
>reflected in the large dip in PLE as it clears out portions of the cache.
>This is normal on an occasional basis but if you see it often that is a sure
>sign of lack of memory or poorly tuned queries and database.
|||Thanks Mike, your answers are really helpful.
On Wed, 14 Sep 2005 22:35:21 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:

>Aramid wrote:
>No. The buffer cache hit ratio only indicates cache hits or misses -
>memory only. Sudden non-related disk I/O would probably affect the
>response time from your SQL server but it shouldn't affect the hit/miss
>ratio of the buffer cache.
>Pretty much, yeah.
|||Well it is normal to see sudden dips occasionally but not on a regular
basis. And the closer the PLE counter gets to 0 when it dips the more dire
the situation. If the buffer cache hit ratio and PLE have frequent dips or
average low values it is a sure sign of memory pressure. But there are lots
of things that can cause this. One is poorly tuned database and queries.
Another is poor plan reuse so you get internal pressure from the procedure
cache. And some others. You need to investigate to see what your system is
really doing. You may have plenty of memory just need to optimize your code
and db calls.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:tkagi1l91cga3tr04s5ropji289drmljjg@.4ax.com...
> Hi Andrew,
> Thanks for your reply.
> In short, sudden clearing of the cache is not really done in regular
> intervals (or perhaps in very small amounts?), but rather on an ad-hoc
> basis, whenever SQL needs to move in chunks of data from disk. Is
> this correct?
> How often then is acceptable? Or how can I make this a basis for lack
> of memory?
> Aramid
> On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>