Difference between SQL Server pre-read and physical read

I saw some people discussing the difference between pre-read and physical read on the internet today. I personally think that both physical read and pre-read operations require reading data from the disk to the memory, the reading time is different, And then I checked msdn to confirm this idea.

 

Pre-reading is performed at the same time of generation and execution. This method can improve Io performance. The physical read operation reads data from the disk only after the execution plan is generated and the cached data is read. When all data can be read from the cache, it becomes read.

 

The following is an example:

 

The lineitem table stores approximately million data. We use set statistics Io on:

Set statistics Io on

Select count (*) from lineitem

Table 'lineitem'. Scan count 3, logical reads 22328, physicalreads 3,Read-ahead reads20331, Lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execute this statement repeatedly and you will see that the pre-read has changed to 0, because all the data is already in the cache:

Select count (*) from lineitem

Table 'lineitem'. Scan count 3, logical reads 22328, physicalreads 0,Read-ahead reads 0, Lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

Pre-read explanation:

The Database Engine supports a performance optimization mechanism called "pre-read. Pre-Reading First predicts the data and index pages required for executing the query execution plan, and then reads them into the buffer cache before the query actually uses these pages. In this way, computing and I/O can overlap to make full use of the CPU and disk.

The pre-read mechanism allows the database engine to read a maximum of 64 consecutive pages (Kb) from a single file ). This read is executed as a spread-aggregation read of the corresponding number of (possibly non-adjacent) buffers in the buffer cache. If any page in this range already exists in the buffer cache, when the read is complete, the corresponding page will be discarded. If the corresponding page already exists in the cache, you can also "crop" the page range from any end.

For more information about pre-reading, see msdn:

Http://msdn.microsoft.com/zh-cn/library/ms191475.aspx