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

Question: How to Know If Index is Forced on Query?

Answer: This was indeed a very interesting question received during the recent Comprehensive Database Performance Health Check by my client. Let me tell you a brief story so you understand the context of the question.

The client was continuously facing poor performance for their 8 of different queries. They hired me to help them with tuning their system. Unfortunately, due to some networking issues, my DBA was not able to login to the system where the database was hosted. After some struggle, he was able to send me an execution plan for the trouble making queries.

After looking at the execution plans, it was clear that their SQL Developers are forcing Indexes on the query. I responded back saying that the developer should not force index hint in the query and let SQL Server decide the natural course of the execution plan. They internally looked at the queries and found that most of the queries had index hints. Once they removed it, their performance was restored.

After the engagement, the DBA asked me how did I figure out that queries had forced index hints. Honestly, it was very simple for me to do that. Let us see the execution plan for the following two queries.

First, run the following query with index hint and check the execution plan by right-clicking on the properties of the index scan operator.

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

Now, run the following query without any index hints and right-click on the properties of the index scan operator.

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

You will notice that one time the property Forced Index is True and another time it is marked as False.

This is how from the execution plan, I figured it out that their developers are forcing indexes on the query, which is preventing the SQL Server engine to build a better execution plan.

Reference: Pinal Dave (https://blog.sqlauthority.com)