4

I'm analyzing a query being run inside our system via sp_executesql. I am able to execute the query on the production database and include the actual execution plan. In the execution plan results window, a simple nonclustered index is suggested.

Based upon the suggestion, I have added the index. However, when I re-run the same query, it continues to suggest the same index. Why would it think that the index does not exist?

Improve this question
5
  • Are you sure it's exactly the same index? There aren't other included columns? Remember that the order of the columns being indexed matters too (ON mytable(col1, col2) is not the same as ON mytable(col2,col1)
    – Petter Brodin
    Commented Oct 27, 2012 at 21:14
  • Yes, it's identical.
    – GaTechThomas
    Commented Oct 27, 2012 at 22:08
  • Does this involve a filtered index? Wait, QO probably doesn't suggest filtered indexes... could you post your query plan or some actual code here?
    – 孔夫子
    Commented Oct 27, 2012 at 23:23
  • 3
    This is a known issue in SQL Server (sorry I don't have any references). Essentially there's nothing wrong with what you did; it's the missing index algorithm that's faulty. I've seen this myself a couple of times, too.
    – Jon Seigel
    Commented Oct 28, 2012 at 14:40
  • 5
    Missing index DMVs bug that could cost your sanity...
    – Mark Storey-Smith
    Commented Nov 10, 2012 at 19:36

2 Answers 2

Reset to default
0

The suggested index can differ from your actual index if you miss out INCLUDEd columns or some the direction (ASC/DESC) is different

I've answered similar on SO before

Improve this answer
0

I've seen the same behavior; check the execution plan and make sure it's using the new index, then ignore the recommendation.

Improve this answer

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.