When data is not in buffer cache so all reads will be physical read , requesting row by row data from disk is very expensive so here new mechanism call read ahead reads. It anticipates bunch of rows that might be requested by query so it place pages into buffer even before requested by query. This mechanism allows the Database Engine to read up to 64 contiguous pages (512KB) from one file. The read is performed as a single scatter-gather read to the appropriate number of (probably non-contiguous) buffers in the buffer cache
Read ahead read is available on SQL 2016 and upper version
of enterprises edition.
Running the below query.
Before running the query clearing the buffer cache.
|
DBCC DROPCLEANBUFFERS; |
Now running the query
|
select * from Production.TransactionHistory where ProductID=329 |
