10

In one of my production SQL server instances, a query is taking a long time to run. While checking the query plan, I found that it was giving a 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 somehow not getting used.

Maintenance plans (for 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?

The index suggested is exactly the same which is present in the table.

Index usage stats:

OBJECT NAME INDEX NAME USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
Lea_ex1_hs IX_Lea_ex1_ID 357533 0 0 380386

SQL Server 2008 R2 SP3

0

1 Answer 1

8

Based on the information in your question, I think you've hit a bug that is referenced in Missing index DMVs bug that could cost your sanity… by Paul Randal, 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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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