When working with SQL Server, it is important to optimize queries for better performance. One common performance issue is the presence of bookmark lookup or RID lookup operations. In this article, we will discuss what bookmark lookup and RID lookup are, and explore two methods to remove them from the execution plan.

Understanding Bookmark Lookup and RID Lookup

Bookmark lookup, also known as key lookup, occurs when a query requests data from columns not present in a non-clustered index. SQL Server must then go back to the data pages to retrieve the data in those columns. If the table has a clustered index, it is called bookmark lookup; if the table does not have a clustered index but has a non-clustered index, it is called RID lookup.

Both bookmark lookup and RID lookup operations are expensive and can negatively impact query performance. To optimize queries containing these operations, it is important to remove them from the execution plan.

Method 1: Creating a Non-Clustered Cover Index

In this method, we create a non-clustered index that contains the columns used in the SELECT statement, along with the column used in the WHERE clause. This index covers all the columns in the query and eliminates the need for a bookmark lookup or RID lookup.

CREATE NONCLUSTERED INDEX [IX_OneIndex_Cover] ON [dbo].[OneIndex] (City, FirstName, ID) ON [PRIMARY]

By creating this non-clustered cover index, the SQL Server query execution engine can retrieve all the necessary data from the index itself, without having to go to the data pages.

Method 2: Creating an Included Column Non-Clustered Index

In this method, we create a non-clustered index that includes the columns used in the SELECT statement, along with the column used in the WHERE clause. This index also eliminates the need for a bookmark lookup or RID lookup.

CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex] (City) INCLUDE (FirstName, ID) ON [PRIMARY]

By including the necessary columns in the index itself, the SQL Server query execution engine can retrieve all the required data without the need for additional lookups.

Conclusion

Bookmark lookup and RID lookup operations can significantly reduce the performance of SQL Server queries. By using either a non-clustered cover index or an included column non-clustered index, we can remove these operations from the execution plan and improve query performance.

Optimizing queries is an essential aspect of SQL Server performance tuning. In future articles, we will explore additional concepts related to query optimization and discuss the optimal methods for improving performance.