Mohit (5/1/2009)
I was talking to Microsoft Tier-3 Support guy about SQL Server; he suggested using the Dash Board there is a 90%+ improvement there will definetly be improvement. He said to take care for values below 80... but for above 90 you should consider creating the index.
Few things to note though.
The logic that goes into an index being suggested in the missing index DMV is a subset of what DTA does.
The recommendations are done individually for queries (as they are optimised) and not for an entire workload
Missing indexes will never suggest clustered vs nonclustered.
If will never suggest modifying an existing index or dropping an existing one.
It won't check for similar indexes in the DMV already or similar ones that already exist.
Say you have an existing index on 2 columns - (a,b) however you have two queries (heavily used) that either filter on a, b and c or filter on a and b and select c.
Missing indexes DMV may very well suggest 2 new indexes, one on (a,b,c) and one on a,b include (c). If you create both of those, you've got 2 redundant indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability