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.
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.