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.
A Normal Output List from a Query Plan
Today, while troubleshooting a query that is never finishing, I ran across this gem.
Empty. Bereft of Meaning. Nothing.
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);
This index is correctly being used by the query optimizer but in the past it’s only been used to perform lookups and push data to screen (or somewhere). So, what we’re seeing here makes sense. The CROSS APPLY needs to reference the BillId so the compiler checks the situation out and decides to use IX_ServiceList_BillId in order get the BillId. Since the index only includes ServiceListId, it has to perform a Key Lookup.
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);
