Read-Ahead Reads in SQL Server

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)

DBCC DROPCLEANBUFFERS  --Do not run this on Prod
DBCC FREEPROCCACHE()   --Do not run this on Prod
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[SpatialLocation]
      ,[rowguid]
      ,[ModifiedDate] FROM PERSON.Address

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

DBCC TRACEON(652)
DBCC DROPCLEANBUFFERS  --Do not run this on Prod
DBCC FREEPROCCACHE()   --Do not run this on Prod
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[SpatialLocation]
      ,[rowguid]
      ,[ModifiedDate] FROM PERSON.Address

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

Session ID – 56

(19614 row(s) affected)

Table 'Address'. Scan count 1, logical reads 361, physical reads 1, read-ahead reads 359, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 16 ms,  elapsed time = 377 ms.

Session ID – 57

(19614 row(s) affected)

Table 'Address'. Scan count 1, logical reads 360, physical reads 49, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 15 ms,  elapsed time = 788 ms.

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:

Untitled

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

HAPPY LEARNING!

Regards:

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

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *