What is Read Ahead Read in SQL Server? – Interview Question of the Week #197

Question: What is Read Ahead Read in SQL Server?

Answer: The read-ahead mechanism in 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 the query asks for data from the storage engine, the 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 in time, if the 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 queries 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.

DBCC DROPCLEANBUFFERS

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 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 the 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)