Question: What is Read Ahead Read in SQL Server?
Answer: The read-ahead mechanism is SQL Server’s feature which brings data pages into the buffer cache even before the data is requested by the query.
Whenever we run a query, SQL Server Engine tries to optimize the query by various different methods. One of the methods is Read Ahead Read (RAR). When query asks for data from the storage engine, SQL Server Optimizer engine first looks for the data in the buffer cache. If the data is not in the buffer cache, it will directly go to physical storage and move the data to the buffer cache. At this point of time, if storage engine anticipates that SQL Server may need more data, it will initiate the RAR mechanism, which loads additional data to SQL Server buffer cache. In simple possible words, it is just another mechanism to make SQL Server query run faster. Once the data is pulled into the buffer cache, when we run the same query again, the same query does not do read ahead unless the data is moved out of the buffer cache.
Let us run the following demonstration to understand how actually RAR works with SQL Server.
To simulate the demonstration, on your demo machine run the following command.
Please note that you should not run the above command on your production system as it will hurt the performance of the production server.
Next, run the following command for AdventureWorks database.
USE AdventureWorks2014 GO SET STATISTICS IO ON GO SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID < 88 GO
After running the above statement, I suggest you go to the messages tab where you will be able to see the message something in the following manner.
Table ‘SalesOrderDetail’. Scan count 1, logical reads 578, physical reads 2, read-ahead reads 296, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
You can clearly see in the example above that the SQL Server engine is implementing RAR mechanism for the above query. Now if you run the same query again next time you will not see any value for RAR as that value is already cached in the buffer cache. If you want to see the same results again you will have to run the command to DBCC DROPCLEANBUFFERS.
Reference: Pinal Dave (https://blog.sqlauthority.com)