Management Studio Hides Missing Indexes From You.

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:

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%:

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.