Performance of Read Ahead Read with Trace Flag 642

When user submit a query to fetch data from SQL server, database engine do a logical read to check if requested data page is holding  the requested data are present in cache if so it will do logical read and sent back to user, but if the requested data pages are not present in buffer cache then it will do a physical read which is reading from disk, this is an expensive operation involving high IO and wait type.

To avoid physical read, SQL Server has something known as Read Ahead Read, this will bring the data(data pages in buffer) even before it is requested from the query. Read Ahead Read operation is a default behavior of SQL Server.

In this post we will check the performance of Read Ahead Read compared to physical read with the help of Trace Flag 642.

I have created two set of Query, one using Read Ahead read to fetch the data and other one using physical read.

With Read Ahead Read: Here I have set the IO on to capture the plan and freed out the cache and buffer

dbcc traceoff(652,-1)

dbcc freeproccache

dbcc dropcleanbuffers

go

set statistics io on

set statistics time on 

  --select * from dbo.person   

  select * from person.address

set statistics io off

set statistics time off

go

Lets check what IO info  is saying

(19614 row(s) affected)

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

(1 row(s) affected)

 SQL Server Execution Times:

   CPU time = 93 ms,  elapsed time = 855 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

Here we can see Read Ahead Read is 150, that means to fetch 19972 records, database storage engine gets 150 eight K pages to cache before plan get executed. Now let’s check Without Read Ahead Read

dbcc traceon(652,-1)

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 * from dbo.person   

       select * from person.address

set statistics io off

set statistics time off

go

Let’s check what IO info is saying

(19614 row(s) affected)

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

(1 row(s) affected)

 SQL Server Execution Times:

   CPU time = 141 ms,  elapsed time = 3041 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

Here clearly we can see the difference, read ahead read give better elapsed time compare to Physical Read.

Conclusion: Read Ahead Read perform well as compared to physical read in our case.

Enjy coding…SQL J 

Post Reference: Vikram Aristocratic Elfin Share