Help understanding the buffer cache and logical reads

I am trying to find out the memory usage for a particular query, and to
understand how data pages can be re-used in the buffer cache. I am using the
Total Server Memory counter (in performance monitor) and dbcc memusage in
analyzer.
I am trying to prove if the following is right. If I run a SELECT * FROM
Contact and cache the data pages. If I run subsequent queries involving the
same table will I incur any more overall memory usage because the necessary
data pages will already be in cache.
I suppose this test is difficult to do because of the inherent memory usage
associated with users connecting and and compiling and executing queries.
However, I am going down this route of restarting SQL Server to clear out
the buffer, and so that it starts off very small. Then, in query analyzer I
run a dbcc memusage (to see the top 20 items in cache and to check that it
hasn't got any tables in it)
dbcc dropcleanbuffers (so the cache is clean)
then I run a query, e.g. SELECT * FROM Contact
the results after running a dbcc memusage is that there are contact table
pages are in fact in the cache because the total buffer pool page count has
increased and you can see it in the table (by it's ID)
However, I've noticed that the logical reads is very high and physical reads
is 0. To me this indicates that the data is already in cache (i.e. the
contact table) because no physical reads had to be done to get them into
cache in the first place? How is this possible?
Table 'CONTACT'. Scan count 1, logical reads 5045, physical reads 0,
read-ahead reads 4704.
I would have expected to see that the physical reads would be 5045?
Does anyone know more about this and if there is a better way to understand
the memory usage for a particular query and the effects of caching?
Read ahead is occurring, and when read ahead turns on it does larger reads
( perhaps an extent and maybe larger)...Logical reads is in pages, since
read ahead is reading larger blocks, there are reads.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E73CC96C-B913-4C1F-A58B-46A39E046838@.microsoft.com...
> I am trying to find out the memory usage for a particular query, and to
> understand how data pages can be re-used in the buffer cache. I am using
the
> Total Server Memory counter (in performance monitor) and dbcc memusage in
> analyzer.
> I am trying to prove if the following is right. If I run a SELECT * FROM
> Contact and cache the data pages. If I run subsequent queries involving
the
> same table will I incur any more overall memory usage because the
necessary
> data pages will already be in cache.
> I suppose this test is difficult to do because of the inherent memory
usage
> associated with users connecting and and compiling and executing queries.
>
> However, I am going down this route of restarting SQL Server to clear out
> the buffer, and so that it starts off very small. Then, in query analyzer
I
> run a dbcc memusage (to see the top 20 items in cache and to check that it
> hasn't got any tables in it)
> dbcc dropcleanbuffers (so the cache is clean)
> then I run a query, e.g. SELECT * FROM Contact
> the results after running a dbcc memusage is that there are contact table
> pages are in fact in the cache because the total buffer pool page count
has
> increased and you can see it in the table (by it's ID)
>
> However, I've noticed that the logical reads is very high and physical
reads
> is 0. To me this indicates that the data is already in cache (i.e. the
> contact table) because no physical reads had to be done to get them into
> cache in the first place? How is this possible?
> Table 'CONTACT'. Scan count 1, logical reads 5045, physical reads 0,
> read-ahead reads 4704.
> I would have expected to see that the physical reads would be 5045?
> Does anyone know more about this and if there is a better way to
understand
> the memory usage for a particular query and the effects of caching?
|||Robert,
You will most likely confuse yourself by going that route. Look up "free
buffer pages" in BooksOnLine for details on how the cache works in more
detail. But in a nutshell sql server will keep data and indexes in cache as
long as you have enough free memory to do so. At some point when it runs
out of cache it will start to reuse the pages. Each page gets a counter
that determines which ones should be forced out to make room for new ones
over the others. This is determined by several factors including how often
you access it. So it is likely that if you run select * and read in a whole
table you can actually reuse portions of cache for the end of the table that
you just read in for the beginning of the table. Other times it will cache
the whole table. It depends. SQL Server does a really good job of
determining what should stay and what should get overwritten so you
shouldn't concern yourself with it. What you need to look at are the Buffer
Cache Hit Ratio and Page Life Expectancy counters. If the Cache hit ratio
is above say 95% your doing good. If the Page Life expectancy is low you
have a lot of memory pressure and could probably benefit from more ram or
most likely more optimization. Inside SQL Server 2000 also has a great
section covering this topic.
Andrew J. Kelly SQL MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E73CC96C-B913-4C1F-A58B-46A39E046838@.microsoft.com...
> I am trying to find out the memory usage for a particular query, and to
> understand how data pages can be re-used in the buffer cache. I am using
the
> Total Server Memory counter (in performance monitor) and dbcc memusage in
> analyzer.
> I am trying to prove if the following is right. If I run a SELECT * FROM
> Contact and cache the data pages. If I run subsequent queries involving
the
> same table will I incur any more overall memory usage because the
necessary
> data pages will already be in cache.
> I suppose this test is difficult to do because of the inherent memory
usage
> associated with users connecting and and compiling and executing queries.
>
> However, I am going down this route of restarting SQL Server to clear out
> the buffer, and so that it starts off very small. Then, in query analyzer
I
> run a dbcc memusage (to see the top 20 items in cache and to check that it
> hasn't got any tables in it)
> dbcc dropcleanbuffers (so the cache is clean)
> then I run a query, e.g. SELECT * FROM Contact
> the results after running a dbcc memusage is that there are contact table
> pages are in fact in the cache because the total buffer pool page count
has
> increased and you can see it in the table (by it's ID)
>
> However, I've noticed that the logical reads is very high and physical
reads
> is 0. To me this indicates that the data is already in cache (i.e. the
> contact table) because no physical reads had to be done to get them into
> cache in the first place? How is this possible?
> Table 'CONTACT'. Scan count 1, logical reads 5045, physical reads 0,
> read-ahead reads 4704.
> I would have expected to see that the physical reads would be 5045?
> Does anyone know more about this and if there is a better way to
understand
> the memory usage for a particular query and the effects of caching?
|||"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E73CC96C-B913-4C1F-A58B-46A39E046838@.microsoft.com...
>I am trying to find out the memory usage for a particular query, and to
> understand how data pages can be re-used in the buffer cache. I am using
> the
> Total Server Memory counter (in performance monitor) and dbcc memusage in
> analyzer.
.. . .
> Table 'CONTACT'. Scan count 1, logical reads 5045, physical reads 0,
> read-ahead reads 4704.
>
The thing you're missing is the read-ahead. There is a thread devoted to
read-ahead caching. When SQL Server decides that it may need to tablescan a
table, it sets the read-ahead thread to reading the pages into the cache.
Then when the execution plan of the query needs the pages, they may already
be in the cache.
Point is, the read-ahead reads are physical reads, they are just performed
by another thread.
David
|||I see, great.
So in fact physical reads are occuring, but this is shown by the read-ahead
read value. So if I start off with an empty cache or nearly empty cache, pin
a table and do a select * from table, then it might not appear as physical
reads, but as read-ahead reads (and from a previous reply these aren't shown
in pages, but could be extents, so the value is larger than the logical
reads)...and overall this achieves the same thing by placing the data in
cache for future anticipated reuse.
Next, I was going to find the total buffer page count before and after a
query and multiply this value by 8. Does this sounds feasible? Perhaps, is
there a better way to measure the memory use for a query?
"David Browne" wrote:

> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:E73CC96C-B913-4C1F-A58B-46A39E046838@.microsoft.com...
> .. . .
> The thing you're missing is the read-ahead. There is a thread devoted to
> read-ahead caching. When SQL Server decides that it may need to tablescan a
> table, it sets the read-ahead thread to reading the pages into the cache.
> Then when the execution plan of the query needs the pages, they may already
> be in the cache.
> Point is, the read-ahead reads are physical reads, they are just performed
> by another thread.
> David
>
>