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.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 32 other followers