Why Missing Index Hints Are Missing

This article first appeared in the SQLServerGeeks Magazine.
Author: Amit Bansal
Subscribe to get your copy.

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, example, SARGABILITY.

This article first appeared in the SQLServerGeeks Magazine.
Author: Amit Bansal
Subscribe to get your copy.