6

In one of my production SQL server instance, I am facing an issue. A query is taking long time to run. While checking SQL query plan, I found that it was giving suggestion for creating a missing index. I went to the recommended table and found that the suggested index was already present in the table but was some not getting used. Maintenance plans (of rebuilding indexes, updating statistics etc) are done on a regular basis.I am not sure why the index is not getting used ? and why query plan keeps on giving suggestion for missing index when it already exists? Any help would be really appreciated.

  • 2
    Could you be encountering this? sqlskills.com/blogs/paul/… – Scott Hodgin Sep 13 '16 at 11:44
  • check through the index to ensure you don't have a filter on it or it is disabled, run select * from sys.indexes where name = 'name_of_index' and have a look through. I've found indexes that should never be filtered but have been which means they're useless in the instance they we re actually created for – Ste Bov Sep 13 '16 at 11:49
  • We would need more info .. is the index suggested exactly same as the one you have ? Can you upload actual plan xml (use pastebin and link it here )? – Kin Shah Sep 13 '16 at 12:24
  • You can check how the index is used by querying SYS.DM_DB_INDEX_USAGE_STATS, for instance SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE I.name = [YourIndexName] . – a1ex07 Sep 13 '16 at 14:17
  • what is output of select @@Version, this used to be bug in SQL Server and was fixed in subsequent service pack – Shanky Sep 13 '16 at 16:50
7

Based on the information in your question, I think you've hit a bug that is referenced in this blog (Missing index DMVs bug that could cost your sanity…) and addressed in this Connect item

Per the blog post: The bug is this: the missing index code may recommend a nonclustered index that already exists. Over and over again. It might also recommend an index that won't actually help a query.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.