Which Is Better: Bookmark/Key Lookup or Index Seek

Which is better: Bookmark/Key Lookup or Index Seek

Index seek, every time.

Lookups are expensive, so this is covering indexes and especially the INCLUDE clause was added to make them better.

Saying that if you expect exactly one row, for example, a seek followed a lookup can be better than trying to cover a query. We rely on this to avoid yet another index in certain situations.

Edit: Simple talk article: Using Covering Indexes to Improve Query Performance

Edit, Aug 2012

Lookups happen per row which is why they scale badly. Eventually, the optimiser will choose a clustered index scan instead of a seek+lookup because it's more efficient than many lookups.

Index Seek with Bookmark Lookup Only Option for SQL Query?

OH,

the covering index with include should work. Another option might be to create a clustered indexed view containing only the columns you need.

Regards,

Lieven

What is a Bookmark Lookup in Sql Server?

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.

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.

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.

Marc

SQL makes key lookup instead of seek over indexed column

A key lookup is a seek. It is looking up using the key.

The non clustered index always includes the clustered index key (or physical rid if the table isn't clustered in which case you get a "bookmark lookup" instead)

Because the index used in the previous index seek does not contain the CreateDate column it needs to use the clustered index key to seek into the clustered index to retrieve it. This type of seek to retrieve additional columns is called a key lookup.

If you wanted to get rid of the lookup you could consider adding CreateDate as an included column to the index on NewsCategoryUrlId.

Though as Hadi says in the comments your case sounds like parameter sniffing or outdated statistics. Often a plan with a non covering index seek and key lookups may be generated if the optimiser believes the parameter value to be selective and be problematic if it is not selective.

With parameter sniffing the problem can arise if a plan is compiled for a selective value and then cached and reused for a less selective value.

Outdated statistics may not reflect the true selectivity of the parameter value the plan is compiled for in the first place.

What is the difference between Lookup, Scan and Seek?

Basic difference between these operations:

Let's consider that you have two tables. TableA and TableB. Both tables contain more than 1000 000 rows, and both have clustered indexes on Id column. TableB has also nonclustered index on code column. (Remember that your nonclustered index is always pointing at pages of clustered one...)

seek:

Let's consider that you want only 1 record from TableA and your clustered index is on column Id.
Query should be like:

SELECT Name
FROM TableA
WHERE Id = 1

Your result contains fewer than 15% (it is something between 10-20, depends on situation) of your full data set... Sql Server performs index seek in this scenario. (optimizer has found a useful index to retrieve data)

scan:

For example your query needs more than 15% of data from TableA, then it is necessary to scan the whole index to satisfy the query.
Let's consider that TableB has TableA Id column as foreign key from TableA, and TableB contains all Ids from TableA. Query should be like:

SELECT a.Id
FROM TableA a
JOIN TableB b ON a.Id = b.TableAId

Or just

SELECT *
FROM TableA

For index on TableA SQL Server performs use index scan. Because all data (pages) need to satisfy the query...

lookup:

Let's consider that TableB has column dim and also column code and nonclustered index on code (as we mentioned).
SQL Server will use lookup when it needs to retrieve non key data from the data page and nonclustered index is used to resolve the query.
For example key lookup could be used in query like:

SELECT id, dim
FROM TableB
WHERE code = 'codeX'

• You can resolve it by covering index (include dim to nonclustered one)

Why does this sql query do a key lookup?

Because it is selecting *.

It uses the non clustered index to locate the row(s) but then needs to go and fetch the data to return.

To avoid the bookmark lookup you would need to make the non clustered index a covering index (ideally by reducing the number of columns in the select list but possible also by adding new columns into the index itself or as included columns)

If you have a clustered index on the table the row locator in the non clustered index will include the clustered index key so it won't need a bookmark lookup to satisfy queries on just the AccountIdentifier and clustered index columns.

SQL Server Plans : difference between Index Scan / Index Seek

An index scan is where SQL server reads the whole of the index looking for matches - the time this takes is proportional to the size of the index.

An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records (see http://mattfleming.com/node/192 for an idea on how this works) - time taken is only proportional to the number of matching records.

• In general an index seek is preferable to an index scan (when the number of matching records is proprtionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the toal number of records in your table.
• Note however that in certain situations an index scan can be faster than an index seek (sometimes significantly faster) - usually when the table is very small, or when a large percentage of the records match the predicate.

Is an Index seek operation more costly compared to index scan when the data volume is high?

You will never see SQL Server choose a scan for this query if you have an index on StockItemID as this covers the query and there is no "tipping point" issue.

It will always choose a seek even if it estimates that 100% of the rows match.

Example

CREATE TABLE OrderLines
(
OrderID     INT IDENTITY PRIMARY KEY,
StockItemID INT INDEX IX1
);

INSERT INTO OrderLines
            (StockItemID)
SELECT 1
FROM   sys.all_objects

SELECT StockItemID
FROM   OrderLines
WHERE  StockItemID = 1; 

In the case that the seek returns all the rows in the table the only difference between a seek and an index ordered scan is how the first row is located (by navigating the depth of the B tree or simply going to the first index page from metadata). This is likely to be negligible.

One edge case where a scan may perform better would be if an allocation ordered scan was preferable and you are running with a table lock or nolock so this becomes a viable option.

When is a full table scan better than index scan?

When selectivity of records is very high full table scan is always better than going through index scan.

http://www.techipost.com/single-index-versus-full-table-scan/

Edit by gbn:

Example, the optimiser may decide that it's easier to scan the table/clustered index if it would require many key lookups (eg non-clustered index to clustered indexs for non-key data).

Or you don't have many distinct values in the indexed columns

which execution plan has better performance?

IN statement is good for selecting littele bit data if you are selecting more data you should use INNER JOIN its better performance than IN for large data

IN is faster than JOIN on DISTINCT

EXISTS is more efficient that IN, because "EXISTS returns only one row"

---

Related Topics