When is a Lookup not a Lookup?

Execution plans are great things. They give us an insight into how SQL Server is putting together queries and why they run slowly. One immediate thing I look for in an execution plan is a Key Lookup. In a Key Lookup operation, SQL Server has to reference the clustered index on the table because a value it’s looking for is not present in the index that was used to find the row. Normally, when you look at the Key Lookup, you will see a list of output columns. This is a great way to help you modify your indexes and make sure that you can avoid these extra disk hits by sacrificing a tiny bit of storage space. Today, while troubleshooting a query that is never finishing, I ran across this gem. There’s no output list in the Key Lookup. I, wrongly, thought that Key Lookups would always include an output list. What’s going on here? Well, here’s the scoop. I have the following, sample, table:``` CREATE TABLE dbo.ServiceList ( ServiceListId BIGINT NOT NULL PRIMARY KEY, BillId BIGINT NOT NULL, ServiceMonth DATETIME );

In this query, ServiceList is referenced through a CROSS APPLY’d inline function. The inline function references the BillId and ServiceListId. There’s an index on ServiceList that contains both of these columns: CREATE INDEX IX_ServiceList_BillId ON dbo.ServiceList ( BillId ) INCLUDE (ServiceListId, ServiceMonth);

> **Protip:** included data is only included in the index; it is not indexed.

To avoid this kind of behavior, I need to change this index to actually index on the combination of BillId and ServiceListId like so:```
CREATE INDEX IX\_ServiceList\_BillId ON dbo.ServiceList
(
  BillId,
  ServiceListId
)
INCLUDE (ServiceMonth);