What is a Read-Ahead Read?

What are read-ahead reads and how do they impact SQL Server performance? Read-ahead reads allow SQL Server to think ahead to pull pages into the buffer cache before they are actually requested for a query. Up to 64 contiguous pages from a file can be read and the ability to read-ahead can be used for both data pages and index pages. Once data is in the buffer cache, it will not need to be pulled in for future queries unless it has been pushed out by other SQL Server tasks.

Read-Ahead Read in Action

To begin, we want to make sure that statistics IO is on. With this on, we can check out scan counts, logical reads, etc. but more importantly for us we’ll be able to see counts for read-ahead reads:

SET STATISTICS IO ON;
GO

We can also run the following for our example to clean buffers from the buffer pool before we run any SELECT statements. This ensures that we’ll see read-ahead reads when we run our first query.

DBCC DROPCLEANBUFFERS;

Let’s say that we want to query the StackOverFlow2013 database for the top 10000 users where the Id is less than 10000. If we run a SELECT query to view that data:

I get 5888 rows. If we click over to the Messages tab and read through our stats, we’ll see 289 read-ahead reads which means SQL Server pulled in 289 pages.

What happens if we run the same query again? Will we have any read-ahead reads this time?

No read-ahead reads the second time around because the data is already loaded in the buffer cache.

Disabling Read-Ahead Reads

If you are in a situation where you do not want read-ahead reads to occur, you can turn on trace flag 652 to disable them:

You can see above that even when we ran DBCC DROPCLEANBUFFERS before our SELECT statement, our read-ahead reads count was 0 due to trace flag 652 being turned on.

Do You Have Read-Ahead Reads Enabled?

Read-ahead reads are helpful in improving query performance and enabled by default. More than likely they’ve always been occurring and never caused a problem.

Thanks for reading!