Hi Friends,

There is a mechanism known as Read-Ahead Reads in SQL Server to read the pages in a buffer before getting the request from query. By default, this feature is enabled in the SQL Server. Have you ever tested the performance impact in terms of IO and Execution time? So today I am going to show you the impact of Read-Ahead read mechanism on query execution.

I am using an AdventureWorks2016CTP3 database for this test. There is a trace flag 652 to disable the Read-Ahead reads mechanism. So I’ll also use that trace flag to disable it for my session only.

Session 1: (in my case Session ID – 56)

Session 2: (in my case Session ID – 57)

IO and Time Statistics for both the sessions are mention below:

Session ID – 56

Session ID – 57

Session ID – 56 is running the select query, which is using the Read-Ahead while session ID – 57 is running the same select query, where Read-Ahead is disabled using trace flag – 652. From the above statistics output you can see that how Read-Ahead mechanism improves the query performance. I have also traced the wait type information using extended event and the information is mentioned below:


From the above stats you can see the impact on PAGEIOLATCH_SH wait type.

With Read-Ahead read mechanism, # of PAGEIOLATCH_SH wait type = 72

Without Read-Ahead read mechanism, # of PAGEIOLATCH_SH wait type = 163



Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook