SQL Server's Missing Index Recommendations: What Aren't They Telling You?

SQL Server's missing-index recommendations can be pretty convincing. Especially when an execution plan tells you that adding an index could improve a query by more than 99%.

But how much can we really trust those recommendations? Are they telling us everything we need to know about a query's indexing needs, or are we only seeing part of the picture?

To find out, I put SQL Server 2025 through a simple test using the same query against several different index configurations. I wanted to see not just what SQL Server recommended, but also what was happening when it didn't recommend anything at all.

Starting With No Nonclustered Index

I created a test database with a 500,000-row SalesOrders table. Five columns and a clustered primary key on OrderID, but no nonclustered indexes.

CREATE TABLE dbo.SalesOrders (
    OrderID INT IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_SalesOrders PRIMARY KEY CLUSTERED,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    OrderStatus CHAR(1) NOT NULL,
    OrderTotal DECIMAL(12,2) NOT NULL
);
GO

;WITH Numbers AS
(
    SELECT TOP (500000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b
)
INSERT INTO dbo.SalesOrders
(
    CustomerID,
    OrderDate,
    OrderStatus,
    OrderTotal
)
SELECT
    (n % 10000) + 1,
    DATEADD(DAY, -(n % 1095), '20261101'),
    CASE WHEN n % 10 < 8 THEN 'C' ELSE 'O' END,
    (n % 5000) + 0.99
FROM Numbers;
GO

Then I ran this query with the actual execution plan enabled and STATISTICS IO turned on.

SET STATISTICS IO ON;

SELECT OrderID, OrderDate, OrderTotal
FROM dbo.SalesOrders
WHERE CustomerID = 43
  AND OrderStatus = 'C'
ORDER BY OrderDate DESC;

The query returned 50 rows, required 1,867 logical reads, and performed a Clustered Index Scan followed by a Sort.

SQL Server also offered a missing-index recommendation with an estimated impact of 99.3463%.

The recommendation was essentially this:

CREATE NONCLUSTERED INDEX idx_SalesOrders_Recommended
ON dbo.SalesOrders (CustomerID, OrderStatus)
INCLUDE (OrderDate, OrderTotal);

That's a reasonable recommendation. CustomerID and OrderStatus are used in the WHERE clause, and the included columns help cover the SELECT list.

But the estimated impact isn't a measurement of what actually happened. It's the optimizer's estimate of the potential reduction in query cost.

Following SQL Server's Advice

I created the recommended index and ran the same query again.

This time, SQL Server used a Nonclustered Index Seek. Logical reads dropped from 1,867 to just 3.

Table 'SalesOrders'. Scan count 1, logical reads 3,
physical reads 0, read-ahead reads 0.

And here's the execution plan:

That's a 99.8% reduction in logical reads. I'd call that a good recommendation -- but there was still a Sort operator because OrderDate was an included column rather than part of the index key.

This made me wonder whether moving OrderDate into the index key would eliminate the Sort. So I replaced the recommended index with this alternative and ran the SELECT again:

DROP INDEX idx_SalesOrders_Recommended
ON dbo.SalesOrders;
GO

CREATE NONCLUSTERED INDEX idx_SalesOrders_Alternative
ON dbo.SalesOrders
(
    CustomerID,
    OrderStatus,
    OrderDate DESC
)
INCLUDE (OrderTotal);

This time, the execution plan showed only an Index Seek. The Sort was gone.

The alternative maintained the same 3 logical reads while eliminating the Sort.

Table 'SalesOrders'. Scan count 1, logical reads 3,
physical reads 2, read-ahead reads 0.

For this small of a result set, it's difficult to establish a meaningful elapsed-time improvement from removing the Sort. But it does demonstrate that SQL Server's recommendation wasn't the only useful index definition.

Now for the Interesting Part

I removed the covering index and created a narrower one:

CREATE NONCLUSTERED INDEX idx_SalesOrders_Customer
ON dbo.SalesOrders (CustomerID)
INCLUDE (OrderDate, OrderTotal);

The index supports the CustomerID predicate and includes the columns we're returning, but OrderStatus is missing. That was intentional. I wanted to reproduce the kind of partially useful index I've encountered countless times in customer databases.

I ran the exact same query again.

This time, the execution plan showed a Nonclustered Index Seek, a Nested Loops operator, 50 Key Lookups and a Sort. The missing index recommendation? Gone.

I find it interesting that the index requires 50 key lookups and 165 logical reads -- but the server offers no missing-index recommendation.

 Table 'SalesOrders'. Scan count 1, logical reads 165, 
physical reads 0, page server reads 0, read-ahead reads 2

I checked the missing-index DMVs as well, but nothing was returned for the table.

We now had a query that could complete with 3 logical reads using a covering index, but was performing 165 logical reads with the narrower index. That's 55 times as many logical reads. Yet SQL Server wasn't recommending another index.

I questioned whether it may need more executions before generating another recommendation. So I ran the query 10 more times, each time using OPTION (RECOMPILE) to force a fresh optimization each time. But still, no missing-index recommendations.

Putting the Results Together

Index Configuration	Logical Reads	Sort	Missing Index?
Clustered index only	1,867	Yes	Yes
Recommended covering index	3	Yes	No
Alternative covering index	3	No	No
Narrower existing index	165	Yes	No

One query. Four indexing configurations. Quite different results.

The narrower index was a substantial improvement over the original clustered scan, but it still required 55 times as many logical reads as the covering index. And SQL Server had nothing more to recommend.

What the Missing-Index Recommendation Didn't Tell Us?

Missing-index recommendations are based on optimizer estimates, not a complete evaluation of your indexing strategy. SQL Server found an index it could use, even though that index required 50 Key Lookups and 55 times as many logical reads as the covering index.

Even after 10 recompilations, SQL Server didn't generate another missing-index recommendation. The existing index was sufficient for the optimizer to choose an execution plan, but our measurements showed there was still considerable room for improvement.

Where I Landed

SQL Server's original recommendation was good. But this test showed me something equally important: an existing index can be good enough to satisfy the optimizer without being the best choice for the query.

I'll continue using missing-index recommendations as part of my investigations. But I'll also continue looking at execution plans, logical reads, and the indexes already in place before deciding what needs to change.

No missing-index recommendation doesn't mean no indexing opportunity.

More to Read

Microsoft Learn: Tune Nonclustered Indexes with Missing Index Suggestions
Microsoft Learn: sys.dm_db_missing_index_details