SQL Server Execution plan those things (3)--Bookmark Lookup

The next article is to record their own blind spots, but also reveal their own development process (may not be developed, can only be said to be blind). Of course, some blind spots are also in the process of work and exploration slowly some, now also willing to carry forward the dedication of the blog park, to come out and share with you.

At the beginning of the work, always with their own "high-tech" work, and feel special, often in front of others to say something to make others feel tall on the wording, to later learn to show off in front of the sister to talk about SQL Server execution plan, this time don't say the plan, Is that the optimization of SQL is still a new thing for me, always with self can correctly find the results and complacent. However, the girl who has no worldly affairs will ask me what the plan is, and my answer is, "It is ...", and I do not know what to say.

Straight into the question

In the process of looking at the SQL Sever execution plan, have you ever been like me, seeing the index look ecstatic, all right, never ignoring the RID lookup, the key looking for what, not even what they are. But, as a program ape, I had to figure out what a bookmark (RID, key value) would look for in order to become a human.

What happens when a bookmark is found: A bookmark lookup always appears with the lookup of a nonclustered index, but not all nonclustered indexes will cause bookmark lookups. When a table is stored as a heap (without a clustered index), the bookmark lookup is a RID lookup, and when the table is stored as a tree (with a clustered index), the bookmark is found as the key lookup.

Bookmark Lookup Why: when the SQL Server query optimizer uses a nonclustered index lookup, it causes bookmark lookups when the index does not completely overwrite the query columns and return columns.

What is the RID: when the table is stored as a heap, the physical location of the data rows is not (strictly speaking, rarely) changed, because there is very little change, so a single row can be determined by an address, and because each row of the database must be stored on a page, and a page must be in a database file, so you can pass the file number: Page number: line number (for example: 1:22:14, refers to the 1th database file 22nd page 14th line).

Key-value bookmarks : When a table is stored as a tree, that is, when the table contains a clustered index, the rows of data in the table are moved, so the RID is not always positioned on the same line. This is the time to locate a row by using the clustered index key, which is the key-value bookmark.

Here are some examples of how bookmarks are found, and how they affect performance:

We also use SQL Server to execute plans for those things (1)--the selection of hashes, merges, nested joins (all indexes are deleted).

1. We first set up a nonclustered index

Create nonclustered index non_index_headers_buydate on headers (buydate)

And then execute

Select Id,buydate from Headers where buydate> ' 2008-12-28 '

Note: This example is for illustrative purposes only and does not contain practical significance.

Analysis: In a table that does not contain a clustered index, because the ID column is not overwritten in this nonclustered index, it is searched through the RID.

2. Build a clustered index

Create clustered index index_headers_id on headers (ID)

Then execute the above query statement

Select Id,buydate from Headers where buydate> ' 2008-12-28 '

Analysis: Does it feel strange? Not surprisingly, because the overlay column of a nonclustered index contains the key columns of the nonclustered index, the columns that the nonclustered indexes contain, and the key columns of the clustered index. Therefore, IDs and buydate overwrite columns for nonclustered indexes, so the results can be found only through the index page, and no more bookmarks are needed to find them.

3. Execute the following SQL statement

Select Id,buydate,discount from Headers where buydate> ' 2008-12-28 '

Parse: Because a clustered index is present in the table, and because the nonclustered index does not overwrite the discount column, it causes the key lookup.

4. How big is the impact of bookmark lookups on query performance?

Create a nonclustered index to overwrite the discount column

Create nonclustered index non_index_headers_buydate_include on headers (buydate) include (Discount)

Then execute the query statement in Example 3

Select Id,buydate,discount from Headers where buydate> ' 2008-12-28 '

Analysis: Because the nonclustered index is now covered, query columns and result columns, there is no bookmark lookup. In addition, when the number of logical reads is significantly smaller than before, the visible bookmark lookup is very resource-intensive, and even invalidates the index, resulting in a full table scan.

Summarize

1. Bookmark lookups are only caused when a nonclustered index lookup is used, and the lookup column and the returned column are not completely overwritten by the index.

2. Bookmark lookups are very resource-intensive and can even invalidate an index.

3. You can eliminate bookmark lookups by overwriting the query columns and returning columns for the nonclustered index.

SQL Server Execution plan those things (3)--Bookmark Lookup