Execution Plan - Key Lookup

Question

Execution Plan - Key Lookup

satyanj

Old Hand

Points: 304
More actions
March 5, 2013 at 5:56 pm

#271164

I run the following query on the AdventureWorks database.
SELECT ContactID,
LastName,
Phone
FROM Person.Contact
WHERE EmailAddress LIKE 'sab%'
In the execution plan, why is the "Key Lookup" operator appearing below the "Index Seek" operator and not after it.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Dung Dinh SSCrazy Points: 2513 More actions · Answer 1

satyanj (3/5/2013)
I run the following query on the AdventureWorks database.
SELECT ContactID,
LastName,
Phone
FROM Person.Contact
WHERE EmailAddress LIKE 'sab%'
In the execution plan, why is the "Key Lookup" operator appearing below the "Index Seek" operator and not after it.

I guess that you created a non-clustered index on EmailAddressColumn and did not include ContactID, LastName and Phone?

Because a non-clustered index only contains index keys at leaf level so SQL Engine must use pointers to point real data which you want to select in SELECT statement. In other words, it uses Key Lookup operator as you see in execution plan. You can refer info of index structure at http://msdn.microsoft.com/en-us/library/ms177484%28v=SQL.105%29.aspx

satyanj Old Hand Points: 304 More actions · Answer 2

My question is why the "Key Lookup" is shown BELOW the "Index Seek" and not AFTER it when reading the plan from right to left.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Save your actual execution plan as a .sqlplan file and upload that to this thread. Sorry, but I don't know what you are talking about and I don't have the AdventureWorks database installed on my PC.

Dung Dinh SSCrazy Points: 2513 More actions · Answer 4

satyanj (3/5/2013)
My question is why the "Key Lookup" is shown BELOW the "Index Seek" and not AFTER it when reading the plan from right to left.

The rules to read the plan is read from right to left and from top to bottom. And that is order of operators in your execution plan.

satyanj Old Hand Points: 304 More actions · Answer 5

I am uploading the plan (ExecPlan.sqlplan). I am learning about execution plans, therefore forgive my ignorance.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

Forgive my wording here...But you got the index seek on IX_Contact_EmailAddress because I assume you created the NCI for "EmailAddress" and ContactID only. To get this data SQL has determine it can use this specific index, at a cost of 5%

Then in order to fulfill the rest of the query, it performs a lookup from that index back on top of the clustered index so it can find "LastName" and "PhoneNumber" at a more substantial cost of 95%

You'll notice in the index seek it shows 19 actual rows, with 1 actual execution: in the Key Lookup you'll notice the same amount of rows, but 19 separate executions which were needed to look up those 2 columns to satisfy the query.

If you change your index to CREATE NONCLUSTERED INDEX IC_Contact_EmailAddress ON [Contact] (EmailAddress, ContactID) INCLUDE (LastName, Phone) it should result in a 100% Index Seek and trash the keylookup on the clustered index altogether

The reason it is shown BELOW the Index Seek is to show it's a part of the same "operation/process" so-to_speak. The Nested Loop is the reason it's done this way. You can read more about that (as well as much more here[/url], Gail Shaw's awesome blog)

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

In simple words.

Key/RID lookup means Non-clustered index is not covering all the participated columns in one query for one particular table. so optimizer choose the heap(RID) or clustered index(Key) for left columns.

these lookup can be avoided by using INCLUDE in non-clustred index

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

satyanj (3/5/2013)
My question is why the "Key Lookup" is shown BELOW the "Index Seek" and not AFTER it when reading the plan from right to left.

Because it looks better 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 9

Bhuvnesh (3/5/2013)
In simple words.
Key/RID lookup means Non-clustered index is not covering all the participated columns in one query for one particular table. so optimizer choose the heap(RID) or clustered index(Key) for left columns.
these lookup can be avoided by using INCLUDE in non-clustred index

+1 Much better wording than mine! (+1)

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience