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