Share via

  • Article

Key Lookup Showplan Operator

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 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. If the WITH PREFETCH clause appears in the Argument column, the query processor has determined that it is optimal to use asynchronous prefetching (read-ahead) when looking up bookmarks in the clustered index. For more information about read-ahead, see Reading Pages.

In earlier versions of SQL Server 2005, the bookmark lookup functionality is shown by using the Clustered Index Seek operator with the keyword LOOKUP. In SP2, the Key Lookup operator icon displays in the graphical execution plan; however, when displaying the execution plan in XML or text format, the output shows the **Clustered Index Seek **operator with the keyword LOOKUP.

The use of a Key Lookup operator in a query plan indicates that the query might benefit from performance tuning. For example, query performance might be improved by adding a covering index.

Bookmark lookup operator iconGraphical execution plan icon

Examples

The following example runs a simple query, modifies a nonclustered index to cover the query, and then re-runs the query to observe the difference in the query execution plan. By displaying the graphical execution plan for this query, you can see that the plan for the first execution of the query shows the Key Lookup operator is used to retrieve the GroupName data from the clustered index on the HumanResources.Department table.

The plan also shows that the nonclustered index AK_Department_Name is used to satisfy the WHERE clause predicate of the query. By modifying this index to include the GroupName column, the index can cover the query and the query plan contains only an index seek operation. The time to execute the query is significantly reduced.

USE AdventureWorks2008R2;
GO
SELECT GroupName
FROM HumanResources.Department
WHERE Name = 'Engineering';
GO

-- Create a covering index by adding GroupName to the nonclustered index.
USE AdventureWorks2008R2;
GO
CREATE UNIQUE NONCLUSTERED INDEX AK_Department_Name
    ON HumanResources.Department ( Name ASC, GroupName)
    WITH (DROP_EXISTING = ON);
GO

USE AdventureWorks2008R2;
GO
SELECT GroupName
FROM HumanResources.Department
WHERE Name = 'Engineering';
GO