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) |
See the execution plan
RID Look gone.
See the execution time
It took 1 MS.
See the performance impact.
