Key Lookup Vs Nonclustered Index Seek (without Key Lookup)

Before we start lets understand, what we mean with these two words:

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.

Non Clustered Index Seek (without Key Lookup): When your non clustered index cover all the data required by select query then the operator which have greater chance to come in execution plan is Non Clustered Index(without Key lookup)

This reflect that Key Lookup is a very expensive operation compare to NC Index Seek(without key lookup) because it perform a IO to clustered index the from clustered to heap to get the data which is not covered by nonclutered index.

Let’s stimulate a situation where we get lookup operator

CREATE TABLE IndexSeek_KeyLookup_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 IndexSeek_KeyLookup_Demo_TB (col2, col3)

        SELECT '20140616',

               1000 * rand();

        SET @i = @i + 1;

    END

SET @i = 0;

WHILE @i < 5

    BEGIN

        INSERT INTO IndexSeek_KeyLookup_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_IndexSeek_KeyLookup_Demo_TB_id

on IndexSeek_KeyLookup_Demo_TB(id)

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

create nonclustered index [ix_col2]

on IndexSeek_KeyLookup_Demo_TB ([col2] asc)

Command(s) completed successfully.

Now lets create covering nonclustered index on the same column

create nonclustered index [ix_col2_2]

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

Command(s) completed successfully.

Now lets fire both the query in batch to see cost variance between two operator.

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

SELECT SUM(col3) FROM IndexSeek_KeyLookup_Demo_TB with (index=[ix_col2_2]) WHERE col2='20140617'

Go

So here we saw Index seek without Key look take only 17% of total cost of batch where as Key Lookup take 83% of total cost of batch, here we can say use of covering index drastically improve the performance of query.

I started because I find u interesting, I stand up with all versions of urs and all of sudden I came to knw… It was just me n you J

Post Reference: Vikram Aristocratic Elfin Share

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

Remove your RID Lookup, the performance Degrader

Either a rotten Apple or a bad fish spoil the whole pond, as a kid we all heard this quote, but did we understand the meaning behind it.

Spoiling our Execution plan by a bad fish, that what we would not want in our Plan cache. Here we are checking how RID lookup is ruining the whole plan and then we look out work around solution to the RID lookup problem.

RID Lookup: When you have Non Clustered index on a table without having Cluster index, then a RID corresponding to each key value of NONCLUSTERED  index is put on place in order to find the rest of the data from the table(HEAP)

A HEAP table is one without CLUSTERED index, and data are sorted in the table in the order in which insertion is made, but it is not guaranteed because to increase the efficiency optimizer can move the data around  in the heap. So it is always advisable to use order when you scan heap table.

Lets check with sample demo, how to remove RID Lookup,

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;

Since we are concern about RID LOOKUP, which is associated with Non Clustered index on table which has no Clustered index define.

it’s time to create Non Clustered Index on col2 of table.

create nonclustered index [ix_col2]

on RIDLookup_Demo_TB ([col2] asc)

Command(s) completed successfully.

Now since NonClustered Index is in place, lets fire the same query to see the RID Lookup in the Execution Plan.

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

Work around solution

When I see RID Lookup, I always think, why the table has no clustered index on it. So the solution is creating Clustered Index on the table.

create clustered index ix_RIDLookup_Demo_TB_id

on RIDLookup_Demo_TB(id)

Let’s fire the Query, to see whether we are able to remove RID lookup

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

So here we can see we are able to successfully remove RID lookup from Heap.

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

Thinking to leave with you scared, Ah you are wonderful! mY cODE J

 

Post Reference: Vikram Aristocratic Elfin Share