SQL Server ignores the index prompt. SQL Server ignores the index

SQL Server ignores the index prompt. SQL Server ignores the index

When we want a query statement to use an index, we usually add an index prompt to the query statement, as shown in the following figure:

Copy codeThe Code is as follows: SELECT id, name from TB with (index (IX_xttrace_bal) where bal <100

In the production environment, due to the index prompt, the optimizer generally does not consider other indexes. Sometimes this index prompt may cause slow query.

After your test, it is found that the query is slow due to the relationship indicated by this index, but the SQL server has cached the execution plan of this SQL statement. If you modify the SQL statement, it may affect the query.

In addition, not only does one SQL statement use the index prompt, but other SQL statements also use the index prompt, you cannot modify these SQL statements immediately using a trace flag in SQLSERVER.

This trace flag can ignore index prompts in SQL statements and index prompts in stored procedures.

Performance troubleshooting can be performed without modifying SQL statements.

Run the following script to create a database and related indexes.

Copy codeThe Code is 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 1, 10000
Create proc uspFirst
AS
SELECT id,
Name
FROM xttrace8602 tf with (INDEX (IX_xttrace8602_bal ))
WHERE bal <1, 100
GO

Run the following code:

Copy codeThe Code is as follows:
-- No tracing for Peugeot
EXEC uspFirst
GO

Copy codeThe Code is as follows:
-- The tracking flag is used.
Dbcc traceon (8602,-1)
GO
DBCC FREEPROCCACHE
GO
EXEC uspFirst
GO

As you can see, after the trace mark TRACEON (8602,-1) is enabled, SQLSERVER ignores the index prompt and uses the composite index IX_xttrace8602_bal_name to check the data

No additional key search

This tracking flag allows SQLSERVER to ignore index prompts without modifying your SQL statement.

Before using this 8602 tracking flag, remember to test it in the development environment and check whether you need to ignore the index prompt to solve performance problems.

If anything is wrong, you are welcome to make a brick o