SQL Query Performance Optimization----Resolve Bookmark Lookups

When the optimizer chooses a nonclustered index that contains only part of the Query Request field, a lookup (lookup) is required to retrieve the other fields to satisfy the request. A key lookup for a table with a clustered index is a RID lookup (RID lookup) for a heap table. This lookup is the--bookmark lookup.

The bookmark lookup reads data from the table based on the row locator of the index. Therefore, in addition to the logical reading of the index page, a logical read of the data page is also required.

This article addresses this overhead by creating additional overhead from the index's row locator to reading data from the table.

Let's look at the structure of my test table:

　　　　　　　　　　

One can see that there is a clustered index Pk_userid and a non-clustered index ix_username.

Look at the effect of creating a bookmark lookup:

Select Username,gender from dbo. UserInfo where username= ' userN600 '

As you can see from the SQL generated execution plan above, a bookmark lookup (Key lookup) is generated, as

　　　　　　　　　　

If you rewrite the above SQL to

Select UserName from dbo. UserInfo where username= ' userN600 '

　　　　　　　　　　

You can see that the bookmark lookup is gone.

The main reason this SQL generates bookmark lookups is that the SQL Optimizer chooses the nonclustered index ix_username to hold SQL. Ix_username index does not contain Gender This field then produces a lookup that is a bookmark lookup from the index to the data table.

To resolve a bookmark lookup:

method One, use a Clustered Index

For clustered indexes, the leaf page of the index is the same as the table's data page, so when the value of the clustered index key column is read, the data engine can read the values of the other columns without requiring any row positioning, which resolves the bookmark lookup.

For this sentence SQL (select Username,gender from dbo. UserInfo where username= ' userN600 ') solves a bookmark lookup by building a clustered index on UserName because a table has only one clustered index, which means deleting an existing clustered index (Pk_userid) will cause other The foreign key constraints in the change, which requires some work related, may severely affect other queries that depend on the existing clustered index.

method Two, use a Overwrite Index  

The overwrite index is a nonclustered index that is established on all the columns required to satisfy the SQL query without reaching the base table. If the query encounters an index and does not need to reference the underlying data table at all, the index can be considered an overwrite index.  

For this sentence SQL (select Username,gender from dbo. UserInfo where username= ' userN600 ') the way to resolve a bookmark lookup is to include the Gender field in the non-clustered index ix_username.

That is, in the index when the include statement, the specific operation is as follows

　　　　　　　　

with include Better in use in the following cases:

1, do not want to increase the size of the index key, but can still build an overlay index;

2. To index a data type that cannot be indexed (except text, ntext, and images);

3. The maximum number of key columns that have exceeded an index

method Three, use Index Connections

An indexed connection is a query that is completely overwritten by using an index cross between multiple indexes. If the overlay index becomes very wide, then you can consider an index connection.

For this sentence SQL (select Username,gender from dbo. UserInfo where username= ' userN600 ' and gender=1 ' can build a nonclustered index on the Gender.

For this example, it is possible that the SQL optimizer does not use both the nonclustered index ix_username and our newly established index on gender, so we can tell the SQL optimizer to work with both indexes at the same time:

Select Gender,username from UserInfo with (index (ix_gender,ix_username)) where username= ' Jins ' and gender=0

SQL Query Performance Optimization----Resolve Bookmark Lookups