How to find missing indexes quickly in SQL Sentry Plan Explorer
August 17th, 20114
How to find missing indexes quickly in SQL Sentry Plan Explorer
August 17th, 20114

This question came up on twitter, and I thought a quick blog post would be better than a series of 140-character snippets. Management Studio does not do the best job at highlighting missing indexes. If you have a simple query plan that represents a single statement, no problem; however, if you have a plan that represents a multi-batch statement, you might see a missing index recommendation on every statement plan, not just the plan that is recommending the index. Here is a screen shot to demonstrate this (click to embiggen):


I also filed a Connect item about it back in 2009:

Connect #518467 : SSMS plans

Our free plan analysis tool, SQL Sentry Plan Explorer, does not have this issue – it will only show missing index information corresponding to the applicable statement. Still, if you are trying to go after this information explicitly, and you have a complex plan like the one above, it may take a few extra seconds to locate any missing indexes. To get there a little quicker, you can add a column to the Statements Tree tab called "Missing Indexes" – you can then sort this column to see the plans that have one or more missing indexes. If the column isn't already present in the Statements Tree tab, right-click any column, and select "Column Chooser":


Scroll down the customization window until you see "Missing Indexes" – click and drag that item onto any spot in the toolbar:


Now you can sort by this column to show any statements where the count is > 0:


And then you can move down to the plan diagram, right-click the head node (which will also have a yellow warning symbol, in this case indicating the missing index), and choose "Missing Index Details…":


This will bring up the script to create the index that the optimizer thought would be useful to help this query (click to embiggen):


Now, to be clear, we aren't doing anything better than SQL Server with these recommendations – we are passing along the same suggestion that they do, with all of its potential faults. We add a pretty heavy disclaimer to remind you that, while the missing index *might* help this specific query, we don't have "the whole story" and can't be sure what impact this index will have. For example, there is a bug which can lead to recommending multiple copies of similar or identical indexes, and it seems this won't be addressed until Denali at the earliest. Never mind that adding an index to a table can cause a lot of extra work for INSERT / UPDATE / DELETE operations – you really need to weigh how frequently the query is run and whether the benefit to the query justifies the costs of maintaining the index. So, like Paul Randal suggests, take all missing index recommendations – regardless of the source – with a huge grain of salt, and do your due diligence before following through on these suggestions.


By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

4 Responses

  1. KB says:

    Origin: Television writer David X. Cohen for The Simpsons episode 3F13, "Lisa the Iconoclast".
    My friend didn't believe me when I told her that meese was the plural of moose, but she admitted that it was entirely cromulent.

  2. AaronBertrand says:

    Thanks guys.
    @Scott, I complained about that one too:*/*/
    If it's the same issue, it's apparently fixed in Denali CTP3. I did not report it against earlier versions, so not sure if there is a fix in the works there too. If you're able to consistently reproduce this on 2008 or 2008 R2, maybe a new Connect item is in order.

  3. Scott Newman says:

    Not to mention that the SSMS tooltip in the query plans sometimes grinds my computer to a halt.  I suspect I'm not the only one.
    This makes it much easier to find missing indexes.  Good job!  A very cromulent post.

  4. JNK says:

    Good info.  The integrated query plan tool really leaves a whole lot to be desired.  I may push for use of your tool at my company for reasons just like this.