October 26, 2002 at 1:12 pm
I want to test queries with an empty buffer cache. I run DBCC DROPCLEANBUFFERS and then SET STATISTICS IO ON and then run my query.
The results of SET STATISTICS shows Logical Reads and no Physical Reads. How could it be reading from cache when I cleaned it out?
October 26, 2002 at 5:34 pm
When a data page is not in the cache, the next request for the page will cause the disk to be read, and then the page will be written to the cache. Once the data is written to the cache, the data will then be read to return values for the query. See the below example using the pubs database:
DBCC DROPCLEANBUFFERS
set statistics io on
use pubs
select * from stores
/*
Table 'stores'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.
*/
select * from stores
/*
Table 'stores'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
*/
You will notice the second execution results in zero physical reads.
Sincerely,
Mark Cudmore, MCP
Sincerely,
Mark Cudmore, MCDBA
October 27, 2002 at 12:23 am
Yes that is what I expect to see. However, below is what I am seeing. Now this table has no Primary Key and no Clustered Index on it(for testing purposes). Thus the data is stored in a Heap.
I expect to see Physical Reads here:
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SELECT COL1, COL2, COL3, COL4 FROM TBL WHERE COL1 = 30000
Results:
(1 row(s) affected)
Table 'TBL'. Scan count 1, logical reads 935, physical reads 0, read-ahead reads 935.
Why no Physical Reads???
October 27, 2002 at 4:38 am
I'd guess that once it knows its a heap and/or that its going to do a table scan, the read ahead reader picks everything up from disk, making the query itself only do logical reads.
Andy
October 27, 2002 at 10:06 am
So how can I force a Physical Read?
October 27, 2002 at 12:37 pm
I always combine it with DBCC FREEPROCCACHE, to force the server to clear all buffers. The only thing I can believe is that the results of the query are associated with the sql and thus returned from the cache.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 27, 2002 at 3:28 pm
Why does it need to be physical reads? Read ahead reads will drive the disk just as well.
Andy
October 27, 2002 at 6:23 pm
yes. Thats fine then.
What other things should I be looking at here?
(What I am doing is running queries to determine where to place indexes.)
October 27, 2002 at 9:51 pm
Not sure you should be looking at physical reads at all then, just logical. Have you tried the index tuning wizard?
Andy
October 28, 2002 at 6:40 am
Why just logical reads?
October 28, 2002 at 7:14 am
I know this doesn't answer the original question but, if you are trying to tune a query, looking at logical reads should be good enough.
Whether they are physical reads or logical reads will be governed by many other factors beyond your control (how much memory the Server has, how many other users are on the machine etc).
Tuning the query to achieve minimum logical I/O will translate to minimum physical I/O for the environment it is being run in.
October 28, 2002 at 7:47 am
Good answer! If you have sufficient memory, you only pay for the physical reads once. Testing with no data in cache is the worst case. It will happen sometimes, but not everytime unless you're RAM starved. I tune based on logical reads and cpu usage. Ideally want both to be minimal, sometimes you have to trade one for the other - usually I want lowest disk reads even if higher CPU cost.
Andy
October 28, 2002 at 7:58 am
so if a full Table resides in Buffer Cache and the Optimizer does a full table scan for example, the results will be a Logical Read only - and the Estimated Execution Plan will still show a "Table Scan" correct?
October 28, 2002 at 8:36 am
Correct!
As far as I'm aware, the Optimizer doesn't know what will be cached at the time it compiles the query, and therefore doesn't take this into consideration when compiling a plan.
October 31, 2002 at 9:28 am
Whenever I am performance testing query changes, I always use both FREEPROCCACHE and DROPCLEANBUFFERS (sp?) Otherwise, you end up getting data from memory, which will not reflect the true benefit (or pitfall) of your modificaitons.
Also, on a side note, if you want to do reads from your buffer cache, you might want to "PIN" a small table into the memory. I think the command is DBCC PINTABLE... but thats a different thread
quote:
so if a full Table resides in Buffer Cache and the Optimizer does a full table scan for example, the results will be a Logical Read only - and the Estimated Execution Plan will still show a "Table Scan" correct?
Edited by - cdharma on 10/31/2002 09:30:17 AM
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply