Tuning with Execution Plans - Microsoft SQL Server 2012 Performance Tuning - page 137

If you observe the screenshot provided in step 11, you will come to know that SQL Server is
having proper instruction of using non-clustered index with Key Lookup as against clustered
index scan, because the second query with clustered index scan has taken 37 percent of
query cost as against 31 percent load in first and third query each.
It is now clear that non-clustered index seek along with Key Lookup is faster in current
situation, but it works more efficiently if we remove Key Lookup.
If we remove UnitPrice and ProductID from SELECT list, Key Lookup will be removed
from execution plan, but it may not be a desirable situation as we might need those fields in
the result set. So, now we can go for one of the other options, either create covering index
or create INCLUDE column non-clustered index rather than simple non-clustered index. We
decided to go for INCLUDE column index in step 12 and executed the same SELECT query in
step 13 which worked even better and removed the Key Lookup successfully.
One of the major reasons that invites Key Lookup is to have predicate that meets criteria to
call non-clustered index, and to have fields in SELECT section which neither belong to the
non-clustered index or clustered index. The clustered index has to make the Key Lookup to
find value of those non-key fields.
There's more...
For more details on different types of indexes, please refer to Chapter 9 , Implementing Index
and Chapter 10 , Maintaining Index .
In this chapter only the execution plan is discussed, so we have not covered details about
Indexes here.