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

   

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 →

4 Comments on “sql server trace flag 652”

  1. if you will enable this trace flag then pages will not be fetched before the consumption by SQL Server. There may be some queries which are using read ahead and doing well from performance point of view, can generate performance issue if you will disable read ahead. This is the reason, lots of testing is recommended in your environment, before using this trace flag.

Leave a Reply

Your email address will not be published.