In the last post you saw that the missing index feature does not gather statistics for more than 500 missing index groups.
This time I am going to reveal another limitation which is not even documented – the missing index feature does not recommend missing indexes for trivial queries.SQL Server has to create an execution plan before executing a query because it has to evaluate the different available physical iterators depending on the cardinality estimation, supportive indexes, available hardware resources and instance settings to find a good enough plan. Query optimization is a CPU, memory and especially a time expensive process. Therefore Microsoft implemented several rules and thresholds to avoid situations where it takes too long to find a query execution plan. SQL Server even can skip the query optimization (phase 0-2) if only one possible safe execution plan exists for the query – these queries are called trivial.

To give you a better idea of a trivial plan just think about a table which does not has any index. If you ask SQL Server to find a specific record in any column of this table the only way to present the record is scanning the entire table and apply a filter according to your search argument (residual predicate). Even if you persuade SQL Server to spend a few hours finding a better plan you will not get any different one.

Let us execute a trivial query to get an index recommendation from the missing index feature.

USE AdventureWorks2017;
GO

DROP TABLE IF EXISTS dbo.SalesOrderDetail;
GO

-- Create a table based on Sales.SalesOrderDetail
SELECT * INTO dbo.SalesOrderDetail FROM Sales.SalesOrderDetail;
GO

SET STATISTICS IO ON;
GO

-- Visualize the residual predicate
DBCC TRACEON(9130);
GO

-- No index recommendation from the missing index feature 
-- Optimization Level : Trivial
-- Costs : 1,24488
-- Table 'SalesOrderDetail'. Scan count 1, logical reads 1497
SELECT * FROM dbo.SalesOrderDetail WHERE ProductID = 897;
GO

SQL Server does not recommend any index because the plan is considered to be trivial which you can see in the properties of the execution plan. I enabled the Trace flag 9130 to visualize the filter to find the records after the entire table scan.

To see the optimization level of the execution plan click on the Select operator.

Just to summarize, we are looking for two records out of 121,317 records and SQL Server scans the entire table which costs 1,497 page reads and the query engine does not complain about a missing index even it is obvious that it definitely makes sense to have an index on ProductID.

I am sure you agree that these kind of queries are pretty common for OLTP systems.

It seems that SQL Server recommends missing indexes as part of the full query optimization only. So let us force SQL Server to go into the full optimization by using the query trace flag 8757.

-- To get missing indexes recommendations we need to force SQL Server to optimze
-- the query.
-- The Query Processor estimates that implementing the following index could 
-- improve the query cost by 99.34%.
SELECT * FROM dbo.SalesOrderDetail WHERE ProductID = 897
OPTION ( QUERYTRACEON 8757 );
GO

This time SQL Server suggests creating an index which will save 99,3054%. Be aware that the full optimization leads to the same plan as before but because we forced SQL Server to fully optimize the query it took more CPU, memory and time to compile the query.
We can see that this plan is the result of a full query optimization in the properties of the execution plan.

If we implement the index we can see that SQL Server prefers an index seek which costs 4 logical reads instead of scanning all 1,497 pages of the table and the query costs are down to 0,0033652.

CREATE NONCLUSTERED INDEX [test]
ON [dbo].[SalesOrderDetail] ( [ProductID] )
INCLUDE (
[SalesOrderID]
, [SalesOrderDetailID]
, [CarrierTrackingNumber]
, [OrderQty]
, [SpecialOfferID]
, [UnitPrice]
, [UnitPriceDiscount]
, [LineTotal]
, [rowguid]
, [ModifiedDate]
);
GO
-- Costs: 0,0033652 (1,24488)
-- Table 'SalesOrderDetail'. Scan count 1, logical reads 4 (1497)
SELECT * FROM dbo.SalesOrderDetail WHERE ProductID = 897
GO

I guess you agree that it was worth to implement the index but unfortunately you do not get any hint from SQL Server that this index was missing without forcing SQL Server to fully optimize the query at the expensive of more CPU, memory and time consumption.

I will continue to show the limitations of the missing index feature in the next post.