Slow Query - How to Eliminate Index Seek/Key Lookup

I have a slow query and I think I could fix it by creating the right index. It seems to be doing an Index Seek on a FK, then using a clustered index Key Lookup to get the rest of the data. I tried creating a new index that I think covers all of the columns being used but the query doesn't seem to use the new index. Can someone tell me which columns I need to include in the index to eliminate the slow Key Lookup's?

Thanks!

avatar image By ijabit 33 asked Mar 16, 2015 at 06:56 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

To eliminate Key Lookups, you need to provide an index that covers all the columns needed. You need to look at the Output List and any columns referenced in the Key Lookup Predicate. Most commonly, people forget to look at the column(s) needed to evaluate the predicate, and the optimizer chooses not to use the index.

I doubt the lookups are the primary source of poor performance in this plan, though it is hard to be sure based on an estimates-only anonymized plan. As an example of other things that will need looking at, you should also check the Eager Index Spools to identify other missing indexes (check the Seek Keys).

Ultimately though, a proper analysis would require a post-execution non-anonymized plan, ideally captured directly using Plan Explorer so we can see I/O statistics and other runtime metrics.

avatar image By SQLkiwi ♦ 6.6k answered Mar 18, 2015 at 06:43 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x166
x117
x109
x24

asked: Mar 16, 2015 at 06:56 PM

Seen: 167 times

Last Updated: Mar 18, 2015 at 06:43 AM