SQL Server Trace Flag 8602

Hi Friends,

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

This is one of the trace flag which can be used during troubleshooting issues. This trace flag is used to ignore all the index hints specified in query or stored procedure. We can use this trace flag to troubleshooting the query performance without changing index hints. Let me show you practically.

Ran the below TSQL to create database, tables and procedure. Here under procedure we specified an index hint. I’ll show you the performance of query with index hint and without index hint but we will not change the syntax of query here.

use master
go
if DB_ID('Trace8602') IS NOT NULL
	Drop database Trace8602
go
Create database Trace8602
go
Use Trace8602
go
create table xttrace8602
(
id int identity(1,1) Primary Key,
bal int,
name varchar(100)
)
go
create NonClustered Index IX_xttrace8602_bal_name on xttrace8602(bal,name)
go
create NonClustered Index IX_xttrace8602_bal on xttrace8602(bal)
go
insert into xttrace8602 values(RAND()*786,'SQLServerGeeks.com')
go 10000
Create proc spFirst
as
Select id,name from xttrace8602 TF with (index(IX_xttrace8602_bal)) where bal<100
go

Now we will run the below TSQL code with Actual execution plan to compare the proc performance with and without trace flag 8602.

exec spFirst  
go
DBCC TRACEON(8602,-1)
go
DBCC FREEPROCCACHE
go
exec spFirst 
go

traceflag_8602_1

First execution plan is showing the execution with index hint due to that optimizer used key look up because specified index in hint is not sufficient to cover this query.

Second execution plan is showing the execution for same query but now optimizer is not using index specified in hint due to the sql server trace flag 8602. Here performance of query is good without index hint because now it is using the covering index. Here we compare the two execution plans without changing the original query.

Finally do not forget to turn off the trace flag.

use master
go
DBCC TRACEOff(8602,-1)
go

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

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

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 →

Leave a Reply

Your email address will not be published. Required fields are marked *