How to Know If Index is Forced on Query? – Interview Question of the Week #246

Question: Can an execution plan tell you that an index was forced? Yes. Inspect the index operator’s Forced Index property, shown as ForcedIndex in plan XML.

During a health check, a client had eight troublesome queries. A networking issue kept me from logging in immediately, so the DBA sent their execution plans. The Forced Index property caught my attention. The developer confirmed index hints in the code; reviewing and removing those hints helped that particular investigation.

Here is the original WideWorldImporters comparison:

SELECT [OrderID], [CustomerID], [SalespersonPersonID],
       [ContactPersonID], [OrderDate]
FROM [WideWorldImporters].[Sales].[Orders] AS o WITH (INDEX(1));

SELECT [OrderID], [CustomerID], [SalespersonPersonID],
       [ContactPersonID], [OrderDate]
FROM [WideWorldImporters].[Sales].[Orders] AS o;

In SSMS, select the index access operator, right-click and choose Properties. The original screenshots show where to look and the two values.

These captures match the original two queries. Their similar plan shapes aren’t proof that the hint is harmful. The client story concerned a separate workload: test the effects with its actual data, estimates and measurements.

INDEX(1) means index ID 1 on this object, not a universal index name. For reviewed application code, an explicit index name makes the intent easier to follow. False here also doesn’t rule out every form of plan forcing, such as a Query Store forced plan; this is an operator property with a specific meaning.

I prefer to understand why the optimizer’s unrestricted choice differs before keeping an index hint. A once-useful restriction can become costly when data, statistics or indexes change.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.