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