July 5, 2012 at 2:43 am
Hello,
Has anyone ever seen before where an estimated execution plan shows a suggested missing index, you add it, and then it still keeps telling you that it's needed? Now I'd probably not normally be concerned about it and just ignore it as a 'feature' but this seems to coincide with when the query has suddenly started taking more than five times as long to execute. I've done all the usual checks and steps to see if the table or index are wonky, the stats are scewed and such like and it makes no difference. The Sys.dm_db_missing_index_details view also shows that it's still missing.
I'm sure I'm just missing something obvious....
Thanks in advance
July 5, 2012 at 2:53 am
You may need to clear the buffer cache and let it re-generate excution plan. Or you could try query hint.
July 5, 2012 at 2:54 am
It sometimes keep on showing this.Main thing is to make sure that you have that index and that index has improved your query performance. Also, before creating index make sure the overhead it might cause and is it worth adding the index?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 5, 2012 at 3:03 am
It is a kind of bug as reported here. Even if you have created the index and index is not selected then it will show missing index stats.
Now the issue is that why the created index is not used,First of all, make sure that the statistics are up to date and almost all the columns of index are in where clause with '=' the last column might have some other operator.Did you create the index with same numbe rof columns as suggested by the sql server? Can you post the query and the index you have created?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 5, 2012 at 3:27 am
Thanks both.
I'm not one for just blindly making indexes based on the recommendations of the system as I much prefer to build them based on what I think is best to save over index bloat, but I was just intrigued by the plan as it did suddenly start taking 12 minutes as opposed to 2 and when it suggested a covering index then I figured, "what the hell, this is a test system so lets see what it does?"
I did just manage to finally find a Connect bug log for 2k8 that states "When an index exists but isn't being used by the optimizer for selectivity reasons, the query execution plan will still warn that the index is missing and should be created" so I assume that this is most likely the reason for the behaviour I'm seeing. The bug list can be found here for anyone else seeing it.
I guess I'll just revisit the main query and try and find a way to make it possible without the RBAR that this system is forcing on me, although with it being a data migration from legacy to a new multi-tenant system and the need to maintain ID mappings between the original and new systems while they run in parallel for 6 months. (Thats a management estimate so read 2 years:)) But that's another issue!
Thanks once again
July 5, 2012 at 3:28 am
Ooo snap on the link 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply