Takeaway: If you see an unexpected RID lookup in an execution plan, consider adding a clustered index as a db schema improvement.
What is a RID lookup?
You may come across the RID lookup operator as part of an execution plan. With Management Studio, RID lookups are displayed with the following icon (sort of … I fancied it up a little).
A RID Lookup is a lookup into a heap using a Row ID. The Row is included with entries in a non-clustered index in order to find the rest of a table’s data in a heap. (Remember, with a heap, the table data is stored unordered so a Row ID is needed for the correlation).
The RID Lookup’s official docs can be read here at Books Online. But it’s light on the details and it references a more thorough explanation of lookups in general which can be found at Craig Freedman’s blog here.
They’re bad… or at least not good.
When you find an RID Lookup in a query plan, it’s a symptom. It indicates a database schema that breaks several rules of thumb. Those rules-of-thumb are:
- Each table should have a clustered index (of course there are exceptions but we’re dealing with rules-of-thumb here).
- A non-clustered index has been created indicating that someone somewhere identified an ordering on one or more columns that made sense for that data.
- There is at least one query (i.e. the one that generated the RID Lookup) that needs columns that are not covered by the non-clustered index.
These three points mean a wasted opportunity. A clustered index should have been created. A heap plus a non-clustered index take about the same space as a clustered index. At least logically: the non-clustered pages map to the clustered index’s index nodes and the heap’s pages map to the clustered index’s leaf nodes.
If possible, definitely consider modifying the schema to include a clustered index (After assessing risks of course).
Really? Always?
Well of course no respectable blogger writes in absolutes and Grant Fritchley has a video here that discusses RID lookups further. He says that when the RID lookup only deals with one row, that the impact is negligible. True, but had the table been designed with a clustered index, the performance would have been no worse.
