Hi Friends,

This is my 26th blog on SQL Server Trace Flag 652 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.

There are so many tasks which sql server does by default. One of them is Read Ahead mechanism. Under this Read ahead mechanism sql server uses various complex algorithms to predict or forecast the pages which can be used in future for query processing. SQL Server read these pages into memory based on this forecast or prediction. This improves the performance of queries.

Now the question is: Can we disable this default Read Ahead mechanism? Yes, we can disable it by using trace flag 652. Let me show you this thing practically.

Run the below TSQL code, to do the setup.

Run the below TSQL code, this will show you the default working with Read Ahead mechanism.

Table ‘xtTrace652’. Scan count 1, logical reads 20076, physical reads 2,

read-ahead reads 19936, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now you can run the below TSQL code with trace flag 652:

Table ‘xtTrace652’. Scan count 1, logical reads 20002, physical reads 2495,

read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

From both the outputs of STATISTICS IO, you can see the values for read-ahead reads values. You will clearly see that the value is zero for trace flag 652 i.e. Trace Flag 652 disabled the read ahead mechanism.

PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.

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