March 31, 2017 at 3:30 am
Hi
I have an SP that sometimes creates an inefficient query plan. I've tracked the issue down to index selection. The table it selects from has 2 potential indexes it can use. Neither of the indexes are great and they both have to do RID lookups to pull back additional columns. I don't want to create a covering index for the query and I don't want to add a query hint to force parameters or index selection.
What are the other ways I can make the better index more likely to be selected? Will adding some of the columns from the RID lookup work?
Cheers
Alex
March 31, 2017 at 4:24 am
alex.palmer - Friday, March 31, 2017 3:30 AMHiI have an SP that sometimes creates an inefficient query plan. I've tracked the issue down to index selection. The table it selects from has 2 potential indexes it can use. Neither of the indexes are great and they both have to do RID lookups to pull back additional columns. I don't want to create a covering index for the query and I don't want to add a query hint to force parameters or index selection.
What are the other ways I can make the better index more likely to be selected? Will adding some of the columns from the RID lookup work?
Cheers
Alex
Without seeing everything involved, the query, both plans, the indexes, I'm speculating more than a bit with this answer.
The key to ensuring a particular index is used is to make that index attractive to the optimizer. The most attractive indexes are, generally, covering indexes because they can be the least amount of work. The biggest part of query tuning is to either write your code such it uses the indexes you have, or adjust your indexes (create them or modify them) so that they work better with your code.
In the case of RID lookups, what's going on is that the index is useful, but that the columns being referenced (possibly for additional filtering, possibly just for SELECT criteria) are not all available in the useful index, so you have to go to the cluster to retrieve those additional columns. Now, if the issue is simply the SELECT list, adding part of the columns needed to your non-clustered index is unlikely to help it be selected over another that is too similar in structure. Both will still require the RID. However, if there is additional filtering going on, adding columns to the key of the index could make it more attractive to the optimizer, even though it will still need to do the RID to retrieve the rest of the SELECT list.
That's about all I have based on the information provided.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 31, 2017 at 4:41 am
Thanks for the reply Grant.....
I'm not really looking for a specific answer for this issue, more for suggestions of things I can tweak.
Aside from adding predicates to indexes are there any other things I can do to make one of the indexes more attractive?
March 31, 2017 at 4:57 am
What I outlined is pretty much it. Covering is more attractive. Adequate keys is attractive. Other than that, not sure what else to add. Make sure the statistics are updated and accurate.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply