SQL Server Management Studio only shows you the first missing index recommendation in a plan.
Not the best one. Not all of them. Just whichever one happens to show up first.
Using the public Stack Overflow database, I’ll run a simple query:
SELECT c.CreationDate, c.Score, c.Text, p.Title, p.PostTypeId
FROM dbo.Users me
INNER JOIN dbo.Comments c ON me.Id = c.UserId
INNER JOIN dbo.Posts p ON c.PostId = p.ParentId
WHERE me.DisplayName = 'Brent Ozar';
Here’s the execution plan – and ah-ha, it needs an index!
When you right-click on that query plan and click Missing Index Details, Clippy’s excited to tell you about an index on the Comments table that would improve performance by about 23%:
Missing Index Details from SQLQuery1.sql
The Query Processor estimates that implementing the following index
could improve the query cost by 22.8252%.
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Comments] ([UserId])
23%. Remember that number. Now go back to the plan, right-click on it, and click Show Execution Plan XML. Look what we have here:
There are not one, but TWO missing indexes – one on the Comments table, and another on the Posts table. The Posts missing index is estimated to improve performance by 76%. Now, I’m no data scientist, but I think 76% is higher than 23%.
In Pinal Dave’s Practical Performance Tuning class, he likes to call missing index requests in the plan appetizers: they’re a good place to start, and if you like the first one you see, you should probably dig deeper and keep going. But they’re just appetizers – they’re not the main course.
sp_BlitzCache calls ’em right out to you in the list of warnings so that before you look at the query plan, you know that you need to dig deeper:
In PasteThePlan, we show you these right at the top, too – here’s that plan:
Shout out to Justin Pealing, the developer of html-query-plan – we’ve been sponsoring his open source development work for a couple of years now. This is the same query plan viewer Microsoft is using in SQL Operations Studio, too, so as Microsoft updates their version of html-query-plan, you’ll be able to see missing indexes in SOS, too.