Mickey's T-SQL Ponderings

My good friend Rob Farley (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.  The topic this month is on Plan Operators that are used in execution plans to tell us how the Optimizer is going to run our query statements.

What is a Key Lookup Operator?

The Optimizer uses indexes to retrieve the fields needed from a table. If there are missing fields in the index being used, then the Optimizer has to go back to the Clustered Index to get the other fields. This has to be done for every row in the table. This action is noted in the execution plan by the Key Lookup Operator. The RID Lookup Operator is used instead of the Key Lookup Operator when a Clustered Index is not used and a Heap is used instead.

Show Me the Money

For my example I used the AdventureWorks2008R2 database. I ran the following query and looked at the execution plan.

SELECT
	c.CustomerID
   ,c.PersonID
   ,c.StoreID
   ,c.TerritoryID
   ,c.AccountNumber
   ,s.BusinessEntityID AS StoreID
   ,s.Name AS StoreName
   ,s.SalesPersonID
   ,st.Name AS TerritoryName
   ,st.CountryRegionCode
   ,st.[Group]
   ,st.SalesYTD
   ,st.SalesLastYear
   ,st.CostYTD
   ,st.CostLastYear
FROM
	Sales.Customer AS c
	JOIN Sales.Store AS s ON c.StoreID = s.BusinessEntityID
	JOIN Sales.SalesTerritory AS st ON c.TerritoryID = st.TerritoryID
WHERE
	c.StoreID IN (1284, 994, 1356, 1282, 992, 1358, 1280)

Two indexes were used to retrieve all the fields needed from the Sales.Customer table. The ix_Customer_StoreID index was missing the TerritoryID field so the Optimizer had to go to the PK_Customer_CustomerID Clustered Index to retrieve it. If you add the cost of both operators, then 66% of the cost of the query was used to retrieve fields from the Sales.Customer table.

For reference I removed the Clustered Index to show you what the execution plan would look like when a Heap is involved.

Since there was already a good index for the Customer table, I added the TerritoryID to the INCLUDE part of the index script. This turned the index into a covering index. A covering index is an index that contains all the fields from a table that are needed by a query statement. The INCLUDE part of an index allow extra fields to be part of the index, without the overhead of the data being sorted. Any fields that are part of predicates or filters should be part of the index, all other fields from the table should be part of the INCLUDE. Be cautious though, don’t throw the whole kitchen sink there. Those fields still take up space.

When I ran the execution plan again, I saw that the Key Lookup Operator was removed and the total cost to retrieve the fields from the Customer table was now reduced to 12%. This would also be true if the table was using a Heap instead of a Clustered Index.

The Bottom Line

When you see a Key Lookup Operator or a  RID Lookup Operator, look to see if it makes sense to modify the corresponding index to be a covering index.

Thanks for all the fish

Thanks go out to Rob Farley for hosting this month’s T-SQL Tuesday blog party. Please visit Rob’s blog at http://sqlblog.com/blogs/rob_farley/default.aspx.