August 17, 2011 | SQL Server

How to find missing indexes quickly in SQL Sentry Plan Explorer

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.

 

4 comments on this post

    • JNK - August 17, 2011, 4:35 PM

      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.

    • Scott Newman - August 17, 2011, 5:22 PM

      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.
      Scott

    • AaronBertrand - August 17, 2011, 5:27 PM

      Thanks guys.
      @Scott, I complained about that one too:
      http://web.archive.org/web/*/http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/615766
      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.

    • KB - September 2, 2011, 1:50 PM

      cromulent:
      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.

Comments are closed.