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.

How to Know If Index is Forced on Query? - Interview Question of the Week #246 forced0-800x154

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.

Solarwinds

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.

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

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));

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

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;

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

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)

Solarwinds
, , , ,
Previous Post
What is SCHEMABINDING in SQL Server Views? – Interview Question of the Week #245
Next Post
Retrieve TOP 10 Rows Without Using TOP or LIMIT? – Interview Question of the Week #247

Related Posts

Leave a Reply

Menu