Learn about SQL Server key lookup performance and how to improve the performance of key lookup queries.
One of the easiest things to optimize in your SQL Server queries is a Key Lookup or RID Lookup. A key lookup occurs when the query optimizer chooses a table using an index seek to retrieve one set of columns for the result set, but not all of them. So it has to run back and fetch what it needs again from the clustered Index via the Primary Key, otherwise known as a key lookup. The same thing happens on heaps tables (without clustered indexes), which uses row ids instead of primary keys, called a RID lookup.
For example, let’s run the below query and look at its actual execution plan.
SELECT [SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],
[UnitPrice],[ModifiedDate]
FROM [AdventureWorks2019].[Sales].[SalesOrderDetail]
Where [ModifiedDate]> '2011/01/01' and [ProductID]=776The execution plan has 99% cost associated with the Key Lookup(clustered). We can also note that SQL Server estimated number of rows is 51984 compared to 228 actual rows.
To drill down further into the key lookup cost, hover the mouse, and in the tooltip, we get an estimated number of executions are 228.
The Index seeks operator cost is low (1%), but since the Index does not have all the required columns, the query optimizer uses clustered Index to find the additional column data. To eliminate the key lookup, we can create a new index with required columns as the included column on the Index.
The following query creates the non-clustered Index with the columns specified in the output list of the above image.
CREATE NONCLUSTERED INDEX [IX_SOD_ProductID]
ON [Sales].[SalesOrderDetail]([ProductID])
INCLUDE ([CarrierTrackingNumber],[UnitPrice], [ModifiedDate], [OrderQty])If we re-execute the select statement, it does not use key lookup (clustered). Instead, it used Index seek, which is very efficient.
The Index seek (non-clustered) shows the number of executions 1 and estimated, actual number of rows is equivalent.
