One of the easiest things to fix when performance tuning queries are Key Lookups or RID Lookups. The key lookup operator occurs when the query optimizer performs an index seek against a specific table and that index does not have all of the columns needed to fulfill the result set. SQL Server is forced to go back to the clustered index using the Primary Key and retrieve the remaining columns it needs to satisfy the request. A RID lookup is the same operation but is performed on a table with no clustered index, otherwise known as a heap. It uses a row id instead of a primary key to do the lookup.
As you can see these can very expensive and can result in substantial performance hits in both I/O and CPU. Imagine a query that runs thousands of times per minute that includes one or more key lookups. This can result in tremendous overhead which is generated by these extra reads it effects the overall engine performance.
Let’s look at an example.
SELECT [SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID], [UnitPrice],[ModifiedDate] FROM [AdventureWorks2014].[Sales].[SalesOrderDetail] Where [ModifiedDate]> 2014/01/01 and [ProductID]=772
The cost of the key lookup operator is 99% of the query. You can see it did an Index Seek to the IX_SalesOrderDetail_ProductID which is very effective, but that index did not have all the columns needed to satisfy the query. The optimizer then went out to the clustered index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID to retrieve the additional columns it needed. You can see what it got by hovering over the key lookup in the query plan window.
The good thing about Key and RID look ups is that they are super easy to fix. With a little modification to the non-clustered Index IX_SalesOrderDetail_ProductID we can change to query plan from an Index Seek and a Key Lookup to a very small index seek. All we have to do is recreate that index and add the Output List fields as Included columns on that index.
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]([ProductID] ASC) INCLUDE ([CarrierTrackingNumber],[UnitPrice], [ModifiedDate], [OrderQty]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
And as you can see, we now have an Index Seek only and a more efficient plan.
Key Lookups can cause performance headaches, especially for queries that run many times a day. Do yourself and your environment a favor and start hunting these down and get them fixed. Jonathan Kehayias (B|T) has a great blog on how to locate theses in plan cache be sure to check it out.
