What is a Key Lookup?
Published: 4th July 2015
I was asked this the other day and it’s actually something that I’m surprised hasn’t come up before as people all seem to know it’s a bad thing, but many people don’t know why and don’t even know what one is in the first place.

Therefore I thought I would give a quick explanation for anyone still unsure what a Key Lookup is actually doing and why it’s considered bad.

We all know that Non-Clustered indexes all include the “Clustered Key” of the table. If not, then you do now. And if you have no Clustered Key (ie. Your table is a Heap) then the non-clustered will contain a Row Identifier which is unique and refers back to a specific record within your Heap. This is how a non-clustered index is linked to your table… whether it be via the Clustered Key or the RID.

We can easily prove this as follows:

First we create a simple table with a Clustered Key (in this case also the Primary Key of the table) and throw in some fabricated data from AdventureWorks:

use tempDB

if object_id('tempDB..#lookup') is not null drop table #lookup

create table #lookup
id int identity primary key clustered,
firstName varchar(50),
lastName varchar(50),
address1 varchar(150),
city varchar(50),
postalCode varchar(50)

insert into #lookup
select top 1000 firstName, lastName, AddressLine1, city, PostalCode
from AdventureWorks2012.person.address a
join AdventureWorks2012.person.person p
on a.AddressID = p.BusinessEntityID

Now we’re going to create a non-clustered index on LastName and FirstName:

create nonclustered index ix_tempLookup on #lookup(lastName, firstName)

Now, we’re going to select lastName, firstName, AND the ID of the record… this will only use our non-clustered index, which shows that the Clustered Key is a part of the index despite us not specifying it:

select lastName, firstName, id
from #lookup
where lastName like 'B%'

As you can see… SQL can return the Clustered Key (ID) from just the LastName, firstName non-clustered index.

So, this leads us nicely on to what a Key Lookup is…

Basically a Key Lookup is when SQL Server thinks it’s beneficial to use the non-clustered index (for example to see a LastName rather than perform a whole table scan), but it doesn’t have all the columns you require.

Therefore what SQL Server does is to Seek on your non-clustered to find your record, and then uses the Clustered Key of the table to then do another Seek on the Clustered Index in order to obtain the additional rows.

Hence it performs a Key Lookup into the Clustered Index:

select lastName, firstName, id, address1
from #lookup
where lastName = 'Matthew'

As the execution plan clearly shows, we do the Index Seek on the non-clustered index to find our record with lastName “Matthew” and then we perform a Key Lookup on the Clustered index in order to obtain the additional columns.

You can also see this if you hover over each iterator:

Above we can see the Index Seek which shows that it outputs the firstName, LastName, and ID from the non-clustered index.

And from the Key Lookup we can see that the Seek Predicate is the ID (clustered key) and we output the Address1 column.

This is also why a Key Lookup is known to be expensive and less than ideal… because for each Seek on the non-clustered you are also performing a Seek on the Clustered. Therefore you are, at best, doubling your IO. If you have a large clustered index which has multiple b-tree levels then your IO increases even more.

So that’s a Key Lookup. Hopefully that explains it to anyone who wasn’t 100% sure.
NB: Comments will only appear once they have been moderated.