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
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
