RID Lookup in SQL server

RID (Row Identifier) Lookup occurs when a query use a non-cluster index to find row and the index does not included all the required column from the query. The database engine has to go back to the table to fetch the missing column. This is happen in the heap table. A table does not have the primary key or clustered index. When we query a non-clustered index and need to retrieve column that are not part of index, SQL server uses the RID lookup for the corresponding rows in the heap. This lookup affect the performance.

See the below example

Here we are creating a head table.

create table RID_Lookup_Test ( RID_Lookup_Test int identity(1,1), col_num1 varchar(20), col_num2 varchar(20), col_num3 varchar(20) )

Table created successfully.

Now creating the non-cluster index on the col_num1 column.

create nonclustered index IX_01_RID_Lookup_Test_col_num1 on RID_Lookup_Test(col_num1)

Non-cluster index is created.

Now Inserting 10k records in this table.

declare @i int=1; while @i<=10000 begin insert into RID_Lookup_Test(col_num1,col_num2,col_num3) select 'col_num1'+cast(@i as varchar(10)),                                 'col_num2'+cast(@i as varchar(10)),                                 'col_num3'+cast(@i as varchar(10)) set @i=@i+1; end;

 10k records inserted successfully.

Let’s on the execution plan and run the below query.

select RID_Lookup_Test,col_num1,col_num2,col_num3 from RID_Lookup_Test where col_num1='col_num110';

 

See the result.

See the execution time for this query.

It took 46 MS

RID look up is one cause of performance degrade.

To overcome from RID Lookup we need to include the columns which are used in the select query.

Here I am dropping the existing index and creating the new index which include the all column which are in the select query.

drop index RID_Lookup_Test.IX_01_RID_Lookup_Test_col_num1 create nonclustered index IX_01_RID_Lookup_Test_col_num1 on RID_Lookup_Test(col_num1) include (RID_Lookup_Test,col_num2,col_num3)

 Now running the same query

See the execution plan 

RID Look gone.

See the execution time

 

It took 1 MS.

See the performance impact.