Home, Parent: SQL Server Cost Based Optimizer
AdventureWorks, Index Seek, Key Lookups, Table Scan, Joins, Insert, Update, Delete,
Parallelism I, Parallelism II

Index Seek and Key Lookup

The query below has an execution plan with a single row (nonclustered) index seek followed by a single row key lookup. For this execution plan with exactly the same row estimates, use any CustomerID for which the distribution statistics Range Hi-Key column has one Equal Rows.

SELECT SubTotal FROM Sales.SalesOrderHeader WHERE CustomerID = 11935

SELECT SubTotal FROM Sales.SalesOrderHeader WHERE CustomerID = 13497

Key Lookup

Below are the Index Seek details for the query plan shown above, using SSMS 2005 and 2008. Notice the extra line for Estimated Number of Executions.

Key Lookup Key Lookup

Below are the Key Lookup details using SSMS 2005 on the left and 2008 on the right.

Key Lookup Key Lookup

Below are the Nested Loops details using SSMS 2005 and 2008.

Key Lookup Key Lookup

Right click and select the properties or use F4 to bring up additional execution plan details. Compare this with the details shown for the Select icon.

Key Lookup Key Lookup

The compile cost information and the compile parameter values below are all in the XML plan as shown below. The Query Hash and Query Plan Hash are new properties introduced in SQL Server 2008(?).

Key Lookup

Below is the properties for the Index Seek operation, along with the detail view. All the information from the details is in the properties, with the properties having additional information such as Estimated Rebinds and Rewinds.

Key Lookup Key Lookup

Observe that for both the index seek and the key lookup, the Estimated IO Cost is 0.003125 which is exactly 1/320. Per SQL Server Books On-Line documentation, stating that plan cost represents the estimated elapsed time in seconds on a specific hardware configuration, the interpretation of this is that the cost model is based on reference system performance of 320 IOPS for 8KB random IO. The Estimated CPU Cost is 0.0001581 for both operations. In both the Index Seek and Key Lookup details, the Estimated Number of Rows is 1.

For the index seek, this means one row is expected from the index seek. For the Key Lookup, this means one row is expected from each row of the index seek, not the total rows from the key lookup operation. Below are two lines from the XML plan pertinent to the Key Lookup. The Key Lookup is actually a Clustered Index Seek when the table has a clustered index, as opposed to being heap organized.

<RelOp AvgRowSize="31" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" >

<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false" >

<Object Database=" [AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Index="[PK_SalesOrderHeader_SalesOrderID]" TableReferenceId="-1" />

The Nested Loops operation brings together the data retrieved from the Index Seek and the Key Lookup. The Estimate I/O Cost is zero, and the CPU cost shown for a single row is 0.0000042 (Examining higher row count operations show that this is actually 0.00000418 rounded to 7 places beyond the decimal point). The Estimated Subtree Cost represent the full cost of the current operation and all operations that feed into it.