A Celebrity among all Execution Plan Operators – KEYLOOKUP

I will start with this contour, this operator is crazy on changing its name, and it has changed its name three times in the last three release of SQL Server. From BookMark Lookup to Cluster Index Seek and now it is Key Lookup. I am personally comfortable with Key Lookup, which make more sense to me to the way it works.

Few facts I would like to point out here about KeyLook Up.

• It typically comes with Non Clustered Index.
• It fetches values via cluster index when the required values are not present in (Cover in) Non Clustered Index.
•  It perform one of the expensive IO operation, I will take on this further in sometime  :)

I want to take it further to explain more on this Celebrity operator, when rows are requested by the query with predicate, optimizer look into non-clustered index applied on column participate in where predicate to retrieve the requested data. If the all requested data is not found in Non Clustered Index then it go to data page where the data is stored and retrieve it.

Now in above situation, if clustered Index present on table, it goes to Clustered index to get the physical location of data and this operation is called as KEYLOOKUP and if there is no clustered index present on table then there is something called RID lookup comes into picture.

Now it’s time to get yourself relaxed and have popcorn with cup of tea, ah I m addicted to Tea, you can have CokeJ

Ya, now we can take up our point for more understanding “It perform one of the expensive IO operation”

It is one of the most expensive operations because for each value in non clustered index it goes to clustered index to retrieve the asked data.

Lets stimulate a condition where we have Keylookup operator comes into picture in execution plan.

I am using AdventureWorks2008R2 database and Person.Address Table to stimulate the condition, let’s see how many different indexes are present on Person.Address table

select * from sys.indexes where object_id = OBJECT_ID('Person.Address')

Table   name                         type_desc

------- ---------------------------  ---------------

Address PK_Address_AddressID         CLUSTERED

Address AK_Address_rowguid           NONCLUSTERED

Address IX_Address_AddressLine1_    

        AddressLine2_City_           

        StateProvinceID_PostalCode   NONCLUSTERED

Address IX_Address_StateProvinceID   NONCLUSTERED

So here we can see, we have 4 indexes, one cluster and 3 non clustered index. Lets disable all non clustered indexes first.

alter index AK_Address_rowguid

on Person.Address DISABLE

go

alter index IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode

on Person.Address DISABLE

go

alter index IX_Address_StateProvinceID

on Person.Address DISABLE

go

Command(s) completed successfully.

Now lets fire the query

select AddressID, City, StateProvinceID from Person.Address where City = 'Las Vegas'

Here we are creating a non clustered index on City column which is used in where predicate. Once it s done, again we will check the query Plan.

CREATE NONCLUSTERED INDEX ix_PersonAddress_City

ON [Person].[Address] ([City])

Now lets query the same select statement to see how this newly created index perform, is it  better then the above clustered index scan method

select AddressID, City, StateProvinceID from Person.Address where City = 'Las Vegas'

To be honest, seeing Keylookup in Execution plan excites SQL guy, because he has a good chance to improve further the performance of Query by simply creating Covering Index. I will take covering index in separate article.

Here Key Lookup operator is used to read the value of AddressID, StateProvinceID from by referencing to clustered Index, since this two column are not included in Non Clustered Index.

But at the same time Key Lookup Operator will worsen your cost, if the number of rows to return is more because for each rows it has to switch to Clustered Index to get the value. So that moments scan would be more efficient strategy then Key Lookup. But yes we have the option of Covering those column in Non Clustered Index which prompt Key Lookup by creating Covering Index.

         

If I can love someone, I need to love her as I love my Programming with SQL Server! :)

 

Post Reference: Vikram Aristocratic Elfin Share