How to remove Key Lookup on your query plan
Key Lookup was Introduced in SQL Server 2005 Service Pack 2, the Key Lookup operator is a bookmark lookup on a table with a clustered index. The Argument column(Predicate) contains the name of the clustered index and the clustering key used to look up the row in the clustered index. Key Lookup is always accompanied by a Nested Loops operator. Query performance can be improved by adding a covering index on nonclustered index.
When you found a key lookup for a query execution plan window. The easiest way to remove key lookup from the execution plan is to add covering index for the specific column into that non-clustered in the table.
use AdventureWorks
go
sp_helpindex [Sales.SalesOrderDetail]
--Find list of index currently on table [Sales.SalesOrderDetail]
index_name index_description index_keys
AK_SalesOrderDetail_rowguid nonclustered, unique located on PRIMARY rowguid
IX_SalesOrderDetail_ProductID nonclustered located on PRIMARY ProductID
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID clustered, unique, primary key located on PRIMARY SalesOrderID, SalesOrderDetailID
When you found a key lookup for a query execution plan window. The easiest way to remove key lookup from the execution plan is to add covering index for the specific column into that non-clustered in the table.
use AdventureWorks
go
sp_helpindex [Sales.SalesOrderDetail]
--Find list of index currently on table [Sales.SalesOrderDetail]
index_name index_description index_keys
AK_SalesOrderDetail_rowguid nonclustered, unique located on PRIMARY rowguid
IX_SalesOrderDetail_ProductID nonclustered located on PRIMARY ProductID
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID clustered, unique, primary key located on PRIMARY SalesOrderID, SalesOrderDetailID
-- Run the this two queries with display estimated execution plan
Select ProductID
From Sales.SalesOrderDetail
Where ProductID = 776
go
select ProductID, OrderQty
from Sales.SalesOrderDetail
where ProductID =776
go
As you can see Key Lookup operator for second query. How would you simply remove from the query plan.? Add an QrderQty column into INCLUDE on non clustered Index idx_SalesOrderDetail.
As you can see the Key Lookup has been removed from display execution plan
Source : Craig Freedman thoughts from his blog
Bookmark lookup is not a cheap operation. Assuming (as is commonly the case) that there is no correlation between the non-clustered and clustered index keys, each bookmark lookup performs a random I/O into the clustered index. Random I/Os are very expensive. When comparing various plan alternatives including scans, seeks, and seeks with bookmark lookups, the optimizer must decide whether it is cheaper to perform more sequential I/Os and touch more rows using an index scan or a seek with a less selective predicate that covers all required columns or to perform fewer random I/Os and touch fewer rows using a seek with a more selective predicate and a bookmark lookup.