How to ignore index hints in SQL Server

Original: How to ignore index hints in SQL Server

How to ignore index hints in SQL Server

When we want a query statement to take advantage of an index, we typically add index hints in the query statement, like this

SELECT   from TB with   (indexwhere bal<

In a production environment, because of this index hint, the optimizer will not normally consider other indexes, and sometimes this index hint may cause the query to become slower.

After your tests, it was found that the relationship between the index hints caused the query to slow down, but the SQL Server has cached the execution plan for the SQL statement, which may have an effect if you modify the SQL statement.

And, maybe not just one SQL statement with index hints, and other SQL statements with index hints, you can't immediately modify these SQL statements to use a trace flag in SQL Server

This trace flag ignores the index hints inside the SQL statement and the index hints inside the stored procedure.

You do not need to modify the SQL statement to perform performance troubleshooting

Run the following script to create the database and related indexes

 UseMasterGOIF db_id('Trace8602') is  not NULL    DROP DATABASETrace8602GOCREATE DATABASETrace8602GO UseTrace8602GOCREATE TABLExttrace8602 (IDINT IDENTITY(1,1)             PRIMARY KEY, BalINT, nameVARCHAR( -)    )GOCREATE nonclustered INDEXIx_xttrace8602_bal_name onxttrace8602 (bal,name)GOCREATE nonclustered INDEXIx_xttrace8602_bal onxttrace8602 (BAL)GOINSERT   intoxttrace8602VALUES(RAND()* 786,'CNBLOGS.COM/LYHABC' )GO 10000CREATE PROCUspfirst as    SELECTID, name fromxttrace8602 TF with(INDEX(Ix_xttrace8602_bal))WHEREBal<  -GO

Now execute the following code

-- no use tracking Peugeot EXEC Uspfirst   GO

-- trace flag used DBCC TRACEON (8602,-1)goDBCC  freeproccacheGo  EXECGO

As you can see, after opening the Traceon (8602,-1) trace flag, SQL Server ignores the index hints and uses the composite index ix_xttrace8602_bal_name to isolate the data

Without the need for additional key lookup

This trace flag does not require you to modify your SQL statement to let SQL Server ignore the index hint

Before using this 8602 trace flag, remember to test it in the development environment to see if you need to ignore index hints to make performance problems

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o

How to ignore index hints in SQL Server