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?

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) Oct 27, 2012 at 21:14
  • Yes, it's identical. 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?
    – 孔夫子
    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
    Oct 28, 2012 at 14:40
  • 5

2 Answers 2

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

0

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

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.