sql server trace flag 652

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.

USE [master]
GO
CREATE DATABASE [Trace652]
ON  PRIMARY 
( NAME = N'Trace652_1', FILENAME = N'E:\DATA\Trace652_1.mdf' , SIZE = 4 MB , MAXSIZE = 512 MB, FILEGROWTH = 2 MB ),
( NAME = N'Trace652_2', FILENAME = N'E:\DATA\Trace652_2.ndf' , SIZE = 4 MB , MAXSIZE = 512 MB, FILEGROWTH = 2 MB ),
( NAME = N'Trace652_3', FILENAME = N'E:\DATA\Trace652_3.ndf' , SIZE = 4 MB , MAXSIZE = 512 MB, FILEGROWTH = 2 MB ),
( NAME = N'Trace652_4', FILENAME = N'E:\DATA\Trace652_4.ndf' , SIZE = 4 MB , MAXSIZE = 512 MB, FILEGROWTH = 2 MB )
 LOG ON 
( NAME = N'Trace652_log', FILENAME = N'E:\LOG\Trace652_log.ldf' , SIZE = 4096KB , MAXSIZE = 4096MB , FILEGROWTH = 4096KB )
GO
use [Trace652]
go
create table xtTrace652
(
id int not null identity(1,1) primary key,
TestData char(8000)
)
go
insert into xtTrace652 values('Welcome to SQLServerGeeks.com')
go 20000

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

--Clean the buffer first and do not run this on production systems
DBCC DROPCLEANBUFFERS
go
SET STATISTICS IO ON
Select id,TestData from xtTrace652 
SET STATISTICS IO OFF
Go

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:

DBCC TRACEON(652,-1)
go
--Clean the buffer first and do not run this on production systems
DBCC DROPCLEANBUFFERS
go
SET STATISTICS IO ON
Select id,TestData from xtTrace652
SET STATISTICS IO OFF
go
DBCC TRACEOFF(652,-1)
Go

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