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