Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Execution Plan - Key Lookup
Add to briefcase

Execution Plan - Key Lookup Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
satyanj
Posted Tuesday, March 05, 2013 5:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:59 AM
Points: 4, Visits: 42
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.
Post #1427099
Dung Dinh
Posted Tuesday, March 05, 2013 8:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 03, 2013 8:54 AM
Points: 113, Visits: 890
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
Post #1427136
satyanj
Posted Tuesday, March 05, 2013 8:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:59 AM
Points: 4, Visits: 42
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.
Post #1427145
Lynn Pettis
Posted Tuesday, March 05, 2013 8:44 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 21,624, Visits: 27,463
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.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1427146
Dung Dinh
Posted Tuesday, March 05, 2013 8:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 03, 2013 8:54 AM
Points: 113, Visits: 890
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.
Post #1427150
satyanj
Posted Tuesday, March 05, 2013 10:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:59 AM
Points: 4, Visits: 42
I am uploading the plan (ExecPlan.sqlplan). I am learning about execution plans, therefore forgive my ignorance.

  Post Attachments 
ExecPlan.sqlplan (6 views, 17.64 KB)
Post #1427172
MyDoggieJessie
Posted Tuesday, March 05, 2013 11:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:19 PM
Points: 2,037, Visits: 3,761
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, Gail Shaw's awesome blog)

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1427177
Bhuvnesh
Posted Tuesday, March 05, 2013 11:39 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1427183
ChrisM@Work
Posted Wednesday, March 06, 2013 1:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 5,618, Visits: 10,990
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1427235
MyDoggieJessie
Posted Wednesday, March 06, 2013 6:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:19 PM
Points: 2,037, Visits: 3,761
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; They'll drag you down to their level and beat you with experience"
Post #1427335
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.