Why are Missing Index Hints Missing

This blog post first appeared on SQLMaestros

Let’s understand a few reasons why SQL Server (sometimes) does not show missing index hints.

We are using AdventureWorks2016 database for the demo.

Let’s make a copy of Sales.Customer table as Sales.CustomerDup

SELECT *
INTO sales.customerdup
FROM sales.customer

Before jumping into the actual query, have a look at AccountNumber column which is of VARCHAR data type.

SP_help 'sales.customerdup'

Note that this table is a heap, we just created it. There are no indexes on the table.

SP_helpindex 'sales.customerdup'

Let’s execute a simple query with the Actual Execution Plan turned ON (Ctrl + M). Observe the execution plan. We can see a table scan there.

SELECT StoreID FROM Sales.CustomerDup
WHERE AccountNumber= N'AW00029594'

I was expecting that since the table is a heap and there is a predicate on AccountNumber, SQL Server will hint me to create an index on AccountNumber column. But there are no missing index hints here.

Going by the Microsoft documentation on missing index hints, SQL Server will not show missing index hints for trivial execution plans.

What does that mean? Right-click on the SELECT operator from the execution plan and click on properties, and observe the Optimization Level being Trivial

There are multiple phases in SQL Server query optimization and one such phase is called Trivial Plan Evaluation. For all the queries that fall under the Trivial phase, SQL Server will not show up missing index recommendations. To put it in simple words, the query is too simple for SQL Server to get into the headache of evaluating missing index hints.

Let’s look at another example. Here, the prefix ‘N’ in the WHERE clause plays an important role. We already know AccountNumber column is of VARCHAR data type. Now NVARCHAR data (due to prefix ‘N’) is being compared with VARCHAR, so SQL Server will perform an implicit data type conversion. The moment we try to join two or more tables, SQL Server will perform a full optimization.

SELECT Per.FirstName, Per.LastName, Per.BusinessEntityID,
Cust.AccountNumber, cust.StoreID 
FROM Sales.CustomerDup as Cust
INNER JOIN Person.Person AS Per ON Cust.PersonID = 
    Per.BusinessEntityID
WHERE AccountNumber = N'AW00029594'

Once we execute the query, you will observe that there is still no missing index recommendation despite the Optimization Level being FULL.

We can observe that the optimization level is FULL.

Going by the previous conclusion, when the optimization level is FULL, we should get missing index recommendations. But that does not happen as SQL Server is performing implicit conversion. If we hover over the SELECT operator in the execution plan (also observe the warning symbol on the SELECT operator), the warning clearly says that there is type conversion happening which may affect the plan choice.

Let’s execute the query again, this time without the prefix ‘N’.

SELECT Per.FirstName, Per.LastName, Per.BusinessEntityID,
Cust.AccountNumber, cust.StoreID 
FROM Sales.CustomerDup as Cust
INNER JOIN Person.Person AS Per ON Cust.PersonID = 
    Per.BusinessEntityID
WHERE AccountNumber = 'AW00029594'

Now there will be no implicit conversion. No more warnings, optimization level is FULL and SQL Server comes up with the missing index recommendation, and as expected, the recommendation is on the AccountNumber column.

There are the other cases too when SQL Server does not show up missing index hints, for example, SARGABILITY.

Video Tutorial

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Thanks for reading & watching. If you liked the content, do share it. I am on Twitter if you wish to connect.

You can also subscribe to my exclusive newsletter ConnectWithAB.

Data Platform Virtual Summit 2020

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published. Required fields are marked *