This just peeks into the dynamic management views and does the joins for you, possibly replaces the comma delimiters with another of you chossing (if you pick something other than the default comma) for csv export, computes a ranking score, and sorts it. The indexes that come out will match sys.dm_db_missing_index_details . The numbers come from sys.dm_db_missing_index_group_stats . The query optimizer puts the missing indexes in when it finds that it has to scan the table. I don't know what causes them to get taken out.
resetting the missing index DMVs 'may' fix the problem of the false positives, if they are not being taken out correctly. Otherwise I don't know what will get around the problem.
DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR) appears to work
Does ot appear to work.
The only way to reset the missing_index DMVs appears to be restarting the SQL Server instance.
Here are other limitations of the missing index DMVs from BOL (The 500-limit, and the non-coverage of trivial plans - queries without joins - are the biggies)
*It is not intended to fine tune an indexing configuration. (Only good for quick and dirty tuning with a small timeframe, though it spots terrible offenders pretty good)
*It cannot gather statistics for more than 500 missing index groups. (Requires a DMV reset; only by server restart; to get around)
*It does not specify an order for columns to be used in an index. (You can derive this a little by merging entries)
*For queries involving only inequality predicates, it returns less accurate cost information.
*It reports only include columns for some queries, so index key columns must be manually selected.
*It returns only raw information about columns on which indexes might be missing.
*It does not suggest filtered indexes.
*It can return different costs for the same missing index group that appears multiple times in XML Showplans.
*It does not consider trivial query plans.
These are the 3 main ways I use to tune indexes from quickest/least accurate to most time consuming/most accurate; accuracy affects primarily include column selection and effectiveness of composite indexes):
*The missing index DMVs (Util_Missing Indexes)
*Analyze query plans of SQL profiler trace worst offender aggregate reports
*Trace and crawl through the application, weighing by user-traffic if possible to know.
Another think that can cause an otherwise perfect index to get ingnored, especially in databases without foreign key constraints (at least in the Dev/QA environments): incompatible types in JOIN and WHERE predicates. If you provide a int predicate for a varchar column (where the varchar values is all int-castable), it will want to cast the varchar to int, wrecking any possibility of index usage. Casting your predicates will get around it for a quick-fix, but having your FK-links be identical in type (as required by FK constraints) will eliminate that problem, at least for JOINs. I don't recall this scenario adding missing index entries and creating 'false postives' But it is something that the missing index DMVs won't find.