When using Selective XML Indexes the query plan sometimes reports a missing index on the XML index. - by Mikael Eriksson SE

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 780800 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 3/6/2013 10:09:32 AM
Access Restriction Public


The query plan for a query using Selective XML Indexes can sometimes report a missing index on the internal table for the Selective XML Index. Creating a secondary index on the second path expression can make the missing index report go away but because of the nature of the data in the code provided it does not. 
Sign in to post a comment.
Posted by Mikael Eriksson SE on 4/22/2013 at 11:22 PM
Hi Zlatko! When I first created this connect item I did not know that selective XML indexes was implemented as internal tables. So A hint on a missing index on an index seemed very strange to me. After some more digging I discovered the use of internal tables and that made the hint on missing indexes a bit more understandable. There are cases when the hint is appropriate because creating a secondary selective index on one of the paths can make the hint go away. However, using the sample code I have provided here, the hint does not go away. The reason for that I believe is because of the skewed nature of the data in the XML. It still wants an index on [i_2_value] with the included columns [pk1],[path_1_id],[path_2_id]. Missing indexe hints in query plans is not uncommon and should of course be considered but not blindly implemented. I think this is probably such a case. You don't really want that index on the internal table so we just have to live with a missing index hint in the query plan. Unless you con do something to remove the hint on internal tables when there is nothing that can be done about it. And perhaps you could change the hint so it told you to create a secondary xml index on a path instead of suggesting an index on an internal table. I hope I made things a bit clearer here.
Posted by Microsoft on 4/22/2013 at 5:43 AM
Hi, we are interested in more information around this issue, as we cannot determine what is the problem. Does creating the secondary index on top of the internal table solve the warning? It seems that it does, so it would be good to have more details around why this mitigation is not working.