When a select query requested rows the optimizer will try to use a non-clustered index on the column or columns in WHERE clause to retrieve the requested data, If the select query consists columns which are not present in the non-clustered index then SQL server need to retrieve actual data pages. If the table has clustered index it is called bookmark lookup (or key lookup). If a table doesn’t have clustered index but it has non-clustered index then it is called RID lookup. These kinds of lookup operations are very expensive and impacts query performance. To optimize any query we should try to remove bookmark lookups or RID lookups by verifying the execution plan.
Bookmark lookup -- It is a process of finding actual data in the SQL table, based on an entry found in the non-clustered index. When you search for a value in a non-clustered index and your query needs more fields than the key columns, then SQL Server need to retrieve the actual data pages, that’s what called a bookmark lookup.
RID Lookup -- You may see RID lookup operator in the execution plan while executing the select statements for those tables doesn’t have clustered index. 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 table’s data in a heap.
To overcome the RID lookup we need to define clustered index on the table.
To overcome the bookmark lookup we need to create a non-clustered all the columns which are being used in the select statement (OR)
We need to create non-clustered index with INCLUDED columns to avoid index limitation.
Bookmark lookup and RID lookup are performs random IO operations into the clustered index, and Random IO operations are very expensive. To avoid this we need to create proper indexes on the tables