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.
AramidIf 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 i
s
>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 sur
e
>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:
>
>

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:
>

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.
AramidThis is a multi-part message in MIME format.
--000001080309010709050007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
>
--000001080309010709050007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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).<br>
<br>
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.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Aramid wrote:
<blockquote cite="mid4k2gi1hl9m577tgoh4vo0n230hg3qes43g@.4ax.com"
type="cite">
<pre wrap="">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
</pre>
</blockquote>
</body>
</html>
--000001080309010709050007--|||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:
>>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.
>|||This is a multi-part message in MIME format.
--050004050204040201060302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
--050004050204040201060302
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
Aramid wrote:
<blockquote cite="mid5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com"
type="cite">
<pre wrap="">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?
</pre>
</blockquote>
<tt>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.<br>
</tt><br>
<blockquote cite="mid5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com"
type="cite">
<pre wrap="">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?
</pre>
</blockquote>
<tt>Pretty much, yeah.</tt><br>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--</font></span><br>
<span lang="en-au"></span> <b><span lang="en-au"><font face="Tahoma"
size="2">mike hodgson</font></span></b><span lang="en-au"></span><br>
<span lang="en-au"> <font face="Tahoma" size="2">blog:</font><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span></p>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span></p>
</div>
<blockquote cite="mid5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com"
type="cite">
<div class="moz-signature">
<p> </p>
</div>
<pre wrap=""></pre>
</blockquote>
</body>
</html>
--050004050204040201060302--|||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:
>>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.|||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:
>>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.
>

Disabling cache

One of the tables in my DB has a BLOB field that retrieved quite rare, so I
do not really need to have it in cache and would prefer to keep in the RAM
cache more useful data instead. Is it possible to disable caching of BLOB
values?
Message posted via http://www.droptable.com
Hi
There is no way to turn it off, but since it is rarely used, it will fall
out the cache very quickly as SQL Server is very good at tuning it's cache.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alex via droptable.com" <no@.spam.pls> wrote in message
news:58af54c993dcc@.uwe...
> One of the tables in my DB has a BLOB field that retrieved quite rare, so
> I
> do not really need to have it in cache and would prefer to keep in the RAM
> cache more useful data instead. Is it possible to disable caching of BLOB
> values?
> --
> Message posted via http://www.droptable.com

Disabling cache

One of the tables in my DB has a BLOB field that retrieved quite rare, so I
do not really need to have it in cache and would prefer to keep in the RAM
cache more useful data instead. Is it possible to disable caching of BLOB
values?
--
Message posted via http://www.sqlmonster.comHi
There is no way to turn it off, but since it is rarely used, it will fall
out the cache very quickly as SQL Server is very good at tuning it's cache.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alex via SQLMonster.com" <no@.spam.pls> wrote in message
news:58af54c993dcc@.uwe...
> One of the tables in my DB has a BLOB field that retrieved quite rare, so
> I
> do not really need to have it in cache and would prefer to keep in the RAM
> cache more useful data instead. Is it possible to disable caching of BLOB
> values?
> --
> Message posted via http://www.sqlmonster.com

Disabling cache

One of the tables in my DB has a BLOB field that retrieved quite rare, so I
do not really need to have it in cache and would prefer to keep in the RAM
cache more useful data instead. Is it possible to disable caching of BLOB
values?
Message posted via http://www.droptable.comHi
There is no way to turn it off, but since it is rarely used, it will fall
out the cache very quickly as SQL Server is very good at tuning it's cache.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alex via droptable.com" <no@.spam.pls> wrote in message
news:58af54c993dcc@.uwe...
> One of the tables in my DB has a BLOB field that retrieved quite rare, so
> I
> do not really need to have it in cache and would prefer to keep in the RAM
> cache more useful data instead. Is it possible to disable caching of BLOB
> values?
> --
> Message posted via http://www.droptable.com

Disable local report cache

Does anyone know how to disable caching of local reports with
Reporting Services 2005?On Dec 9, 4:12 pm, lachlan.h...@.gmail.com wrote:
> Does anyone know how to disable caching of local reports with
> Reporting Services 2005?
This article should be helpful.
http://www.databasejournal.com/features/mssql/article.php/3695721
Regards,
Enrique Martinez
Sr. Software Consultant

disable buffer cache - very urgert !

I would like to disable the buffer cache. How to do that?
We have setup load test where connections with "same user activities" are
concurrently hitting the database. The problem with the "set of activities"
are same for all connections and worried whether the data will be buffered?
and it wont be real test for performance?
Thanks,
RamuYou can't disable it, but you can minimize it (sp_configure and "max server
memory") and/or you can
flush the buffer (DBCC DROPCLEANBUFFERS).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:6113D3F2-8409-4874-A043-9E31A0D00213@.microsoft.com...
>I would like to disable the buffer cache. How to do that?
> We have setup load test where connections with "same user activities" are
> concurrently hitting the database. The problem with the "set of activities
"
> are same for all connections and worried whether the data will be buffered
?
> and it wont be real test for performance?
> Thanks,
> Ramu
>|||"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:6113D3F2-8409-4874-A043-9E31A0D00213@.microsoft.com...
>I would like to disable the buffer cache. How to do that?
> We have setup load test where connections with "same user activities" are
> concurrently hitting the database. The problem with the "set of
> activities"
> are same for all connections and worried whether the data will be
> buffered?
> and it wont be real test for performance?
>
Testing with a cold cache is _very_ unrealistic. Testing with an unusually
warm cache may be somewhat unrealistic, but no load test is perfect.
Without real production data and a real production workload, you can only
use your load-test results for analyzing performance bottlenecks and
generating rough estimates of capacity and throughput.
David|||Ramu wrote:
> I would like to disable the buffer cache. How to do that?
> We have setup load test where connections with "same user activities" are
> concurrently hitting the database. The problem with the "set of activities
"
> are same for all connections and worried whether the data will be buffered
?
> and it wont be real test for performance?
> Thanks,
> Ramu
>
Query results ARE NOT CACHED, only the raw data pages from the
underlying tables are cached. SQL Server uses some very complex
algorithms for caching data to minimize disk reads in order to improve
performance. Since that's part of normal operation, it seems like you
would want that reflected in your load test. Regardless, you can't
disable it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com