I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book
My Blog Feed via Email
Follow MladenPrajdic on Twitter
Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff
<< Advanced SQL Server 2005 Express Job Scheduling | Home | The simplest way to delete duplicates and compare two result sets in SQL Server >>

SQL Server Management Studio 2008 suggests missing indexes with actual execution plan

This is something I haven't noticed before but I think it's mighty cool!

When you choose to Include the Actual Execution Plan in SSMS 2008 it suggest any missing indexes it thinks that are needed. Plus it also calculates the performance benefit from adding them.

I'm guessing the number (green 84.7361 in the picture) is in percent... So it's like a Database Tuning Advisor "Lite" :))

And best of all this also works when you're connected to a SQL Server 2000 or 2005.

 

This is what it looks like:

SuggestIndexExecPlan

 

I'm starting to like this new SSMS more and more.

 

kick it on DotNetKicks.com

Print | posted on Monday, December 29, 2008 8:10 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server Management Studio 2008 suggests missing indexes with actual execution plan

SQL Server 2005 suggested missing indexes also but it wasn't displayed quite the same way. If you open the properties window and highlight the top level select of that above plan from SSMS 2005 against SQL 2005 you should see the list of suggested indexes with an impact number.
1/1/2009 4:00 PM | Mike Walsh
Gravatar

# re: SQL Server Management Studio 2008 suggests missing indexes with actual execution plan

cool!
did not know that.
thanx Mike
1/2/2009 2:33 PM | Mladen
Gravatar

# re: SQL Server Management Studio 2008 suggests missing indexes with actual execution plan

I ran into this by complete accident a couple of days ago - I was really surprised to find the CREATE INDEX code at a mouse click!
1/2/2009 4:52 PM | Ian Kirk
Gravatar

# re: SQL Server Management Studio 2008 suggests missing indexes with actual execution plan

Yeah Suggesting Missing Index would be really helpful. I also haven't noticed that yet.

Thanks
1/5/2009 5:07 AM | Ravi Khanal
Gravatar

# re: SQL Server Management Studio 2008 suggests missing indexes with actual execution plan

really happy to find this ssms 08..

feel relaxed and kool with this
1/11/2009 3:52 AM | prem
Gravatar

# re: SQL Server Management Studio 2008 suggests missing indexes with actual execution plan

Uhm.. Before we all get too excited, don't trust the machines :-)

Always check if the suggestion makes sense. In my case, adding it made no significant difference, and it still complained of a missing index after I added it.

Check the query plans, do the math and determine if there is a need or not. In our app, (oltp type app), adding yet another index would most likely be deterimental to the transaction day-to-day operational side. Need to check all this carefully before accepting a machine's suggestion.

Mike.
11/5/2009 11:13 PM | mike H
Gravatar

# re: SQL Server Management Studio 2008 suggests missing indexes with actual execution plan

For me, when I use this with large queries, when I try to hover over or right-click on the missing index message the Microsoft SQL Management Studio hangs/crashes for like 15 seconds before it brings up a right-click menu. This seems to have something to do with it trying to display the query in a pop-up/overlay balloon. Does anyone know if there is a way to fix that or just turn that ballooon option off?
7/13/2010 3:48 PM | Roman
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET

Copyright © Mladen Prajdić