When I query the missing index dmvs for suggested indexes I have one table that has a single index suggested. The table is 8 columns wide and has a FK to the customers table. This table contains information about each customer and whether certain attributes are true of false and when the attribute was set and changed. Each customer can be flagged in this table any number of times but only once per 'flag' ID. Anyway, the dmvs suggests an index on the customer reference (equality) and the flag ID (inequality) columns with no included columns suggested. However, an index like this already exists and has stats of Lookups:0, Scans:0, Seeks:194k The PK on the identity column in the table has 188k lookups but that isnt surprising as the ID wont be used (AFAIK) in the app. I cant make wholesale changes as its 3rd party but I am curious as to why the dmvs would suggest an index that clearly is already in place. The stats are up to date and there is no history that may pre-date the index being created. Has anyone encountered this scenario?
A wild guess, maybe the optimizer wants the column in a different sort order. Maybe the sort order for flag ID should be descending and the customer reference should be ascending. > *When the metadata for a table changes, > all missing index information about > that table is deleted from these > dynamic management objects. Table > metadata changes can occur when > columns are added or dropped from a > table, for example, or when an index > is created on a column of a table.* Quote from book online.
Honestly, don't trust the missing index DMOs. They're inaccurate sometimes. could be that it was placed in there before you created the index and now that you have one, doesn't mean it's going to clear.