I'm in the process of trying to optimize a query that looks up historical data. I'm using the query analyzer to lookup the Execution Plan and have found that the majority of my query cost is on something called a "Bookmark Lookup". I've never seen this node in an execution plan before and don't know what it means.
我正在尝试优化查找历史数据的查询。我正在使用查询分析器来查找执行计划，并发现我的大部分查询成本都在称为“书签查找”。我以前从未在执行计划中看到过这个节点，也不知道它意味着什么。
Is this a good thing or a bad thing in a query?
在查询中这是好事还是坏事？
3 个解决方案
#1
28
A bookmark lookup is the process of finding the actual data in the SQL table, based on an entry found in a non-clustered index.
书签查找是基于在非聚集索引中找到的条目在SQL表中查找实际数据的过程。
When you search for a value in a non-clustered index, and your query needs more fields than are part of the index leaf node (all the index fields, plus any possible INCLUDE columns), then SQL Server needs to go retrieve the actual data page(s) - that's what's called a bookmark lookup.
当您在非聚集索引中搜索值时，您的查询需要的字段多于索引叶节点的一部分（所有索引字段以及任何可能的INCLUDE列），那么SQL Server需要检索实际数据page（s） - 这就是所谓的书签查找。
In some cases, that's really the only way to go - only if your query would require just one more field (not a whole bunch of 'em), it might be a good idea to INCLUDE that field in the non-clustered index. In that case, the leaf-level node of the non-clustered index would contain all fields needed to satisfy your query (a "covering" index), and thus a bookmark lookup wouldn't be necessary anymore.
在某些情况下，这真的是唯一的方法 - 只有当你的查询只需要一个字段（而不是一大堆'em）时，在非聚集索引中包含该字段可能是个好主意。在这种情况下，非聚集索引的叶级节点将包含满足查询所需的所有字段（“覆盖”索引），因此不再需要书签查找。
Marc
渣子
#2
3
It's a NESTED LOOP which joins a non-clustered index with the table itself on a row pointer.
它是一个NESTED LOOP，它将一个非聚集索引与表本身连接在一个行指针上。
Happens for the queries like this:
发生这样的查询：
SELECT col1
FROM table
WHERE col2 BETWEEN 1 AND 10
, if you have an index on col2.
，如果你有col2的索引。
The index on col2 contains pointers to the indexed rows.
col2上的索引包含指向索引行的指针。
So, in order to retrieve the value of col1, the engine needs to scan the index on col2 for the key values from 1 to 10, and for each index leaf, refer to the table itself using the pointer contained in the leaf, to find out the value of col1.
因此，为了检索col1的值，引擎需要扫描col2上的索引以获取从1到10的键值，并且对于每个索引叶，使用叶中包含的指针来查找表本身，以查找超出col1的值。
This article points out that a Bookmark Lookup is SQL Server 2000's term, which is replaced by NESTED LOOP's between the index and the table in SQL Server 2005 and above
本文指出Bookmark Lookup是SQL Server 2000的术语，它被SQL Server 2005及更高版本中的索引和表之间的NESTED LOOP取代
#3
2
From MSDN regarding Bookmark Lookups:
来自MSDN关于书签查询：
The Bookmark Lookup operator uses a bookmark (row ID or clustering key) to look up the corresponding row in the table or clustered index. The Argument column contains the bookmark label used to look up the row in the table or clustered index. The Argument column also contains the name of the table or clustered index in which the row is looked up. If the WITH PREFETCH clause appears in the Argument column, the query processor has determined that it is optimal to use asynchronous prefetching (read-ahead) when looking up bookmarks in the table or clustered index.
Bookmark Lookup操作符使用书签（行ID或聚类键）来查找表或聚簇索引中的相应行。 Argument列包含用于在表或聚簇索引中查找行的书签标签。 Argument列还包含查找行的表或聚簇索引的名称。如果WITH PREFETCH子句出现在Argument列中，则查询处理器确定在查找表或聚簇索引中的书签时使用异步预取（预读）是最佳的。
#1
28
A bookmark lookup is the process of finding the actual data in the SQL table, based on an entry found in a non-clustered index.
书签查找是基于在非聚集索引中找到的条目在SQL表中查找实际数据的过程。
When you search for a value in a non-clustered index, and your query needs more fields than are part of the index leaf node (all the index fields, plus any possible INCLUDE columns), then SQL Server needs to go retrieve the actual data page(s) - that's what's called a bookmark lookup.
当您在非聚集索引中搜索值时，您的查询需要的字段多于索引叶节点的一部分（所有索引字段以及任何可能的INCLUDE列），那么SQL Server需要检索实际数据page（s） - 这就是所谓的书签查找。
In some cases, that's really the only way to go - only if your query would require just one more field (not a whole bunch of 'em), it might be a good idea to INCLUDE that field in the non-clustered index. In that case, the leaf-level node of the non-clustered index would contain all fields needed to satisfy your query (a "covering" index), and thus a bookmark lookup wouldn't be necessary anymore.
在某些情况下，这真的是唯一的方法 - 只有当你的查询只需要一个字段（而不是一大堆'em）时，在非聚集索引中包含该字段可能是个好主意。在这种情况下，非聚集索引的叶级节点将包含满足查询所需的所有字段（“覆盖”索引），因此不再需要书签查找。
Marc
渣子
#2
3
It's a NESTED LOOP which joins a non-clustered index with the table itself on a row pointer.
它是一个NESTED LOOP，它将一个非聚集索引与表本身连接在一个行指针上。
Happens for the queries like this:
发生这样的查询：
SELECT col1
FROM table
WHERE col2 BETWEEN 1 AND 10
, if you have an index on col2.
，如果你有col2的索引。
The index on col2 contains pointers to the indexed rows.
col2上的索引包含指向索引行的指针。
So, in order to retrieve the value of col1, the engine needs to scan the index on col2 for the key values from 1 to 10, and for each index leaf, refer to the table itself using the pointer contained in the leaf, to find out the value of col1.
因此，为了检索col1的值，引擎需要扫描col2上的索引以获取从1到10的键值，并且对于每个索引叶，使用叶中包含的指针来查找表本身，以查找超出col1的值。
This article points out that a Bookmark Lookup is SQL Server 2000's term, which is replaced by NESTED LOOP's between the index and the table in SQL Server 2005 and above
本文指出Bookmark Lookup是SQL Server 2000的术语，它被SQL Server 2005及更高版本中的索引和表之间的NESTED LOOP取代
#3
2
From MSDN regarding Bookmark Lookups:
来自MSDN关于书签查询：
The Bookmark Lookup operator uses a bookmark (row ID or clustering key) to look up the corresponding row in the table or clustered index. The Argument column contains the bookmark label used to look up the row in the table or clustered index. The Argument column also contains the name of the table or clustered index in which the row is looked up. If the WITH PREFETCH clause appears in the Argument column, the query processor has determined that it is optimal to use asynchronous prefetching (read-ahead) when looking up bookmarks in the table or clustered index.
Bookmark Lookup操作符使用书签（行ID或聚类键）来查找表或聚簇索引中的相应行。 Argument列包含用于在表或聚簇索引中查找行的书签标签。 Argument列还包含查找行的表或聚簇索引的名称。如果WITH PREFETCH子句出现在Argument列中，则查询处理器确定在查找表或聚簇索引中的书签时使用异步预取（预读）是最佳的。