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