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.

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


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.

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.


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