index hint ignored in SQL Server _mssql

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

Copy Code code as follows:

SELECT Id,name from TB with (index (Ix_xttrace_bal)) where bal<100

In a production environment, the optimizer generally does not consider other indexes because of the reason for this index, and that sometimes this index hint may cause the query to slow down

After your test, it turns out that the relationship between the index hints caused the query to slow down, but the SQL Server has already cached the execution plan for the SQL statement, and if you modify the SQL statement, it might affect

Also, there may not be a single SQL statement that uses index hints, and other SQL statements are indexed, and you can't immediately modify these SQL statements by using a trace flag in SQL Server

This trace flag can ignore the index hints inside the SQL statement and the index hints inside the stored procedure

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

Run the following script to create the database and related indexes

Copy Code code as follows:

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, ' cnblogs.com/lyhabc ')
Go 10000
CREATE PROC Uspfirst
As
SELECT ID,
Name
From xttrace8602 TF with (INDEX (Ix_xttrace8602_bal))
WHERE Bal < 100
Go

Now execute the following code

Copy Code code as follows:

--No use tracking Peugeot
EXEC Uspfirst
Go

Copy Code code as follows:

--Using the trace flag
DBCC Traceon (8602,-1)
Go
DBCC Freeproccache
Go
EXEC Uspfirst
Go

As you can see, after opening the Traceon (8602,-1) trace flag, SQL Server ignores the index hint 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 have SQL Server ignore the index hint

Before using this 8602 tracking flag, remember to test the development environment to see if you need to ignore the index hints to make a performance problem

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