Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Oct 27 '12 at 21:14
    
Yes, it's identical. –  GaTechThomas Oct 27 '12 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 '12 at 23:23
2  
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 '12 at 14:40
4  
add comment

2 Answers

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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