Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Viewing Missing Indexes in Management Studio 2005


phonebook_2010_02_22.jpg

If you’ve used SQL Server 2008 Management Studio (SSMS 2008), you may have noticed that Microsoft added a pretty neat little feature when looking at Execution Plans, the Missing Index message. This subtle message may appear when you’re looking at either the Actual or Estimated Query Execution Plan in graphical mode. It can be seen written in green just below the query text.

showplan_xml_2010_02_22_b.jpg

The message tells you that the optimizer would have been able to resolve the query faster if only it had another index. It’s even tells you what that index should be. I’ve heeded its advice on more than one occasion with very good results.

But I’m Running SQL Server 2005

Unfortunately, not everyone has access to SSMS 2008. And Management Studio in SQL Server 2005 (SSMS 2005) doesn’t provide this kind of information – at least not in an automatic and graphical. The image below shows the same query as run in SSMS 2005.

showplan_xml_2010_02_22_c.jpg

You can, however, retrieve the missing index information for a query another way – viewing the Execution Plan as XML.

Viewing the Execution Plan as XML

To view the Execution Plan in XML, open a query window in SSMS 2005 and use the SHOWPLAN_XML option as shown below.

SET SHOWPLAN_XML ON;

Execute the statement to set the option. Setting the SHOWPLAN_XML option causes SQL Server to return the estimated execution plan in XML for subsequent queries on this connection instead of actually executing the queries. For more information about the SET SHOWPLAN_XML statement, visit Books Online.

Once the SHOWPLAN_XML option has been set, “execute” the query whose execution plan you wish analyze. You’ll see something similar to the follow image. Note the SHOWPLAN_XML works best when the output is set to “Result to Grid.”

showplan_xml_2010_02_22_d.jpg

Double click the XML hyperlink to open the execution plan. Scroll down until you see (or search for) the MissingIndexes node. In the example below, there’s a single missing index identified, an index on the phone number. In this case, the optimizer is suggesting that simple, nonclustered index would improve performance. The impact attribute estimates the improvement that adding the index would have. The optimizer may also recommend nonclustered indexes with included columns and composite indexes.

showplan_xml_2010_02_22_e.jpg

Use Your Head

The Missing Index feature in Management Studio can be really nice. It’s another tool available to us as database professionals to help us do our job. However, as helpful as it sometimes can be, it’s no substitute for using your own skills and knowledge when considering which indexes to create, or not create.

Consider what the message recommends in a broader context. What effect will the new index have on inserts and updates? On maintenance plans? Then decide if the index is worthwhile.

Additional Information

To learn more about the Missing Index feature, visit some of these site.

Have you used this feature? Have you found it useful? I’d like to hear you experiences.

Comments

Posted by Glenn Berry on 24 February 2010

Good tip, Joe. One nice thing about SSMS 2008, is that the missing index warning in the graphical execution plan even shows up against a 2005 database (while things like Intellisense do not work against a 2005 database).

Posted by Anonymous on 25 February 2010

Pingback from  Blogroll 01/10: Viel Gezwitscher «  Zeugenberg.de

Posted by Anonymous on 26 February 2010

Pingback from  ?????????????? ???? 8 ?????????? » ???????????????? «Do not Disturb!, ????????: ??????????????????. ???????????? L (46) ????????????

Posted by Venkatesan Prabu on 26 August 2010

Unfortunately, am not getting this option in my SSMS 2008.

Cheers,

Venkatesan Prabu .J

Posted by Joe Webb on 27 August 2010

Venkatesan - I don't know if the missing indexes feature can be turned off or not. I'll check.

Posted by Joe Webb on 27 August 2010

Venkatesan - I don't know if the missing indexes feature can be turned off or not. I'll check.

Leave a Comment

Please register or log in to leave a comment.