asked
Key lookup with no output list
Recently doing some query performance review, when I came across this segment of an execution plan ![alt text][1] hovering over the key lookup I get ![alt text][2] so, I'm thinking, I've got a good candidate for some query tuning here. Generally key lookups are expensive, and can be easily avoided by introducing a covering index, or including the 'lookup' fields in another index. Which is what I did - I added the field ProductViews to the non-clustered index IX_EAN. So now the query should only need to do the index seek, and get all the fields it needs from the index! Hmm...... but here's what I get ![alt text][3] still got a key lookup on the plan!?!? Hover over the index seek, and sure enough the new included field is present ![alt text][4] and when I hover over the key lookup, I have no output list ? ![alt text][5] Is this really still doing a key lookup? If so what for? And how do I tune it? [1]: /upfiles/execplan1.png [2]: /upfiles/execplan2.png [3]: /upfiles/execplan1.png [4]: /upfiles/execplan4.png [5]: /upfiles/execplan3.png
How does [Offers].[Id] fit in with the query? Something in the WHERE or ON clause? Is it covered by IX_Ean? Also, it looks like there are several columns in the predicate... It may have to do the key lookup to filter out rows based on columns that may not be covered by IX_Ean
I'm guessing that not all of these columns are covered by IX_Ean Offers.Price Offers.Status Offers.MainCat Offers.SubCat Offers.ChildCat IX_Ean will have to cover all of these columns to get rid of the key lookup I just created the same situation on one of my tables. The entire select list was covered, like yours is, as well as the first column of the where clause, but I had another column in the where clause that wasn't in my index. The query used the first column in the where clause to do an index seek and get the select list from the index. Once it had this limited subset from the index, it did a key lookup against the table followed by the nested loops join to filter out rows it had retrieved from my index based on the non-indexed column in the where clause.
+1 ahh right so the key lookup doesn't list things in the output list that it needs in the predicate! Long day!
+1 it is exactly as @KenJ wrotes. The columns mentioned are not covered by the non clustered index and SQL Server decided to use the non clustered index to retrieve the clustered keys as probably using this index probably limits the number of records extremly.
