Slow Query – How to Eliminate Index Seek/Key Lookup

ijabit 2015-03-16 18:56:07

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!

SQLkiwi 2015-03-18 06:43:12
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.