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 is SQL Server’s feature which brings data pages into the buffer cache even before the data is requested by the query.

What is Read Ahead Read in SQL Server? - Interview Question of the Week #197 read-ahead-read

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.

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

, , , ,
Previous Post
Do Index Reorganization Update Statistics? – Interview Question of the Week #196
Next Post
How to Find Size of the Index for Tables? – Interview Question of the Week #198

Related Posts

1 Comment. Leave new

  • great colleaction of qutions for interview thanks for sharing this….

    Reply

Leave a Reply

Menu