Remove Key Lookup, the performance Degrader from your Execution Plan

Read Previous Post: Remove your RID Lookup, the performance Degrader

In first series of this article,  we were focusing on finding TABLE SCAN operator in execution plan, and finding it we try to remove it by creating clustered index,

Then we saw, how can we eliminate index scan to index seek by creating nonclustered index, but by doing this we introduce one more operator in the execution plan that us Key Look up operator.

Now what is Keylook up operator??

Key Lookup Operator: When your nonclustered index doesn’t cover all the data required by the select query it jumps to cluster index to get the data.

This reflect that Key Lookup is a very expensive operation because it perform a IO to clustered index the from clustered to heap to get the data which is not covered by nonclutered index.

But to be frank, I am always happy to see key lookup because I know I get a chance to improve the performance of query by removing the key lookup.

  

Lets stimulate a situation where we get lookup operator

CREATE TABLE RIDLookup_Demo_TB

(

    id   INT  IDENTITY (1, 1),

    col2 DATETIME        ,

    col3 NUMERIC (28, 10) NOT NULL

);

Command(s) completed successfully.

Our table is ready to get some data into it. Here we are trying to insert 5 Lkh data where date column i.e. Col2 has value 16June2014 and 5 record with col2 value 16June2014 values.

DECLARE @i AS INT = 0;

BEGIN TRANSACTION;

WHILE @i < 500000   

    BEGIN

        INSERT INTO RIDLookup_Demo_TB (col2, col3)

        SELECT '20140616',

               1000 * rand();

        SET @i = @i + 1;

    END

SET @i = 0;

WHILE @i < 5

    BEGIN

        INSERT INTO RIDLookup_Demo_TB (col2, col3)

        SELECT '20140617',

               1000 * rand();

        SET @i = @i + 1;

    END

COMMIT TRANSACTION;

It’s time to create clustered index in column on id field.

create clustered index ix_RIDLookup_Demo_TB_id

on RIDLookup_Demo_TB(id)

Now since cluster index is in place, lets create non cluster index on column col2

create nonclustered index [ix_col2]

on RIDLookup_Demo_TB ([col2] asc)

Command(s) completed successfully.

Let’s fire the Query, to see whether we are able to remove Key Lookup

SELECT SUM(col3) FROM RIDLookup_Demo_TB WHERE col2='20140617'

The next focus will be removing KEYLOOK from the execution plan.

Now since we have stimulated the Key lookup its time to remove the key lookup from the execution plan. The work around solution is covering index.

To create covering index, first we will delete the nonclustered index we created on column col2.

drop index [ix_col2] on RIDLookup_Demo_TB

Command(s) completed successfully.

Now lets create covering nonclustered index on the same column

create nonclustered index [ix_col2]

on RIDLookup_Demo_TB ([col2] asc) include (col3)

Command(s) completed successfully.

Now lets fire the query to see whether we are able to remove the Key Lookup operator.

SELECT SUM(col3) FROM RIDLookup_Demo_TB WHERE col2='20140617'

So here we saw with the help of Covering index we are able to remove the Key Lookup operator.

 I love my sleepless night when you are with me… me and my love SQL Server ;)

 

Post Reference: Vikram Aristocratic Elfin Share