Why is 'physical reads' less than 'read-ahead reads' & 'logical reads' in SQL Server for first time execution of query?

I restarted SQL Server 2005 and ran a query with statistics IO on.

I got these results: Table 'xxx'. Scan count 1, logical reads 789, physical reads 3, read-ahead reads 794, ...

read-ahead reads is the number of pages read and placed in the cache. Shouldn't then the physical reads at least be the same number?

Also because it's a first run for the query, shouldn't logical reads be 0? Or is SQL Server reading the pages phy开发者_C百科sically, putting them in the cache and then reading from the cache? Why is there a count for logical reads when the cache should be empty?

All reads are logical reads.

Some also incur physical reads and read ahead reads in addition to bring pages into cache.

Presumably it read ahead a few pages that were never actually required by your query hence the fact that this number is slightly larger than the actual logical reads.

The docs from MS say that "Read-Ahead Reads" are reading pages into the cache. That's not very helpful... one would assume that "Physical Reads" also place their resulting page into the cache.

My re-wording of what MS is saying is:

• Physical Read - The query is blocked waiting for the page to be read from disk into the cache for immediate use.
• Read-Ahead Read - The page is being read before it blocks the query and is read into the cache as are all reads. Read-Aheads are possible when you are scanning an index, in which case the next leaf pages in the index can assume to be needed and the read can be initiated for them before the query actually says it needs them. This allows the disk to be busy while the db engine is examining the contents of previously fetched pages.

I can't find them saying that anywhere, but read-ahead would be pointless if that's not what they were really trying to say.

I don't know the definitive answer here but update stats can also spike some of these numbers.

What does your query look like? What is the SQL Server edition?

Different JOIN methods [Nested Loop, Merge Join, Hash Join] can add extra logical reads. Enterprise edition has good read-ahead optimizations compared to other editions.