Questions tagged [bookmark-lookup]

Key or RID Lookup in an execution plan. Reading additional data from the base object because a nonclustered index read cannot deliver all the columns needed by the query.

Filter by
Sorted by
Tagged with
0
votes
2answers
38 views

Key Lookup isn't applied by default?

I'm trying to learn about covering indexes. In the Northwind database, I select from the table Categories: As you can see the table has a non-clustered index on the column CategoryName. This SQL ...
1
vote
1answer
84 views

Key lookup still happening after creating covering index

I've implemented a covering index to avoid a key lookup: CREATE INDEX IX_StatusHistory_Covering ON StatusHistory(ID) INCLUDE (Status_ID, StatusComment, StatusReason_ID, ...
5
votes
1answer
285 views

Is there any difference with specifying the primary key as an include column in a nonclustered index?

Don't nonclustered indexes inherently store a reference to the primary key on a table so that it can do a key lookup as needed?...if so, is it any less or more performant to specify the primary key as ...
0
votes
2answers
82 views

Are key lookups from non-clustered indexes always slower than a second query that does the lookup?

I've noticed in my system, whenever a non-clustered index is used in a query that has to also do a key lookup to get the additional fields being selected, it's faster for me to instead do two queries. ...
0
votes
1answer
65 views

Nasty Lookups in Query plan

I've been trying to get rid of expensive lookups in my query plan but cannot seem to get my head around it. I understand the idea of creating a covering index to eliminate the need for lookups but I ...
2
votes
2answers
4k views

Performance differences between RID Lookup vs Key Lookup?

Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index ...
2
votes
1answer
462 views

Can't reduce cost of query plan and get rid of Key Lookup because of cursor

I have tried to create a non-clustered index on the fields that are in the output list which are created_by and Chk1002 .I don't have a column called Chk1002 anywhere . I have read here that it has ...
1
vote
0answers
64 views

Reverse dictionary (key to sequence of values) search with NoSQL database

Suppose we have a dictionary that maps keys to sequences of values - for example: potato: A, B, C tomato: B lettuce: C carrot: A, C cucumber: C I would like to perform queries such as: query A, B, ...
23
votes
3answers
31k views

Eliminate Key Lookup (Clustered) operator that slows down performance

How can I eliminate a Key Lookup (Clustered) operator in my execution plan? Table tblQuotes already has a clustered index (on QuoteID) and 27 nonclustered indexes, so I am trying not to create any ...
18
votes
2answers
5k views

Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?

I am trying to understand why using a table variable is preventing the optimizer from using an index seek and then bookmark lookup versus an index scan. Populating the table: CREATE TABLE dbo.Test ...
1
vote
2answers
1k views

Optimizing key lookup in subquery with UNION ALL

I am trying to optimize following query (little bit more complex actually, but this is important part): SELECT Id, StatusDate, [...Lot Of Columns...] FROM ( ( SELECT Id, StatusDate, [...Lot ...
24
votes
3answers
3k views

Index on Persisted Computed column needs key lookup to get columns in the computed expression

I have a persisted computed column on a table which is simply made up concatenated columns, e.g. CREATE TABLE dbo.T ( ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY, A ...
1
vote
1answer
213 views

Key lookup partition pruning

I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each: SELECT A.COL1, B.COL2, C.COL3 FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN C ON A.ID = C.ID ...
6
votes
2answers
10k views

Reducing Key Lookups

I am using SQL server, and I have been looking closely at the concept of key lookups, http://blog.sqlauthority.com/2009/10/07/sql-server-query-optimization-remove-bookmark-lookup-remove-rid-lookup-...
4
votes
1answer
224 views

How can a key lookup with zero executions and zero actual rows take time?

I have a query that is taking about 10 seconds to run and I am trying to optimize it. All the time is spent in the key lookup which is fine however in this case if I mouse over the key lookup in the ...