asked
Suggested index already exists
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.
ah, good thought. existing index is both columns ASC... Will try a new index with the FLAG ID Desc and check what happens with the usage stats.
It looks like you cracked it here. I have added the index with a DESC attribute for the FLAGID column and its being used for seeks right from the second it was created. There are no recommendations from the DMV now.
yeah, would be good if the recommendations came with an ASC/DESC hint certainly. Also, see @Cyborg's answer below, I'm not sure if the issue that @GFritchey mentions (and Paul Randall's blog is based on it) is related to this confusion.
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.
The missing index views have worked very well when I have used them and have not seen this issue, but the suggestion doesn't mention any sort order and that might be a problem when one column should be descending and others should be ascending.
really? That's disappointing. It's the only index being recommended but the PK is getting huge lookups so I feel there is something out of whack.
there isnt one. I have started on this from the missing indexes dmv. I havent looked in the cache yet, it was last 10 mins in the office!
Is this the well known [bug in Missing index feature][1]? got this info from guru Paul Randal. [Missing index DMVs bug that could cost your sanity...][2] [1]:
https://connect.microsoft.com/SQLServer/feedback/details/416197/the-missing-index-feature-suggests-an-index-which-is-already-present [2]:
http://www.sqlskills.com/BLOGS/PAUL/post/Missing-index-DMVs-bug-that-could-cost-your-sanity.aspx
@Cyborg - thanks for pointing out that blog article, I had read it but forgotten about it. I dont know if this DMV bug has been resolved in a service pack but I cannot follow Paul's example and reproduce the error. Once I create the NCI that he describes the est exec plan uses the new index and no longer suggests an index.
