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.
Questions tagged [bookmark-lookup]
17 questions
32
votes
3 answers
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 more.
I put the clustered index column QuoteID in…
Serdia
- 707
- 1
- 8
- 16
25
votes
3 answers
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 VARCHAR(20) NOT NULL,
B VARCHAR(20) NOT NULL,
…
GarethD
- 693
- 7
- 14
18
votes
2 answers
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
(
RowKey INT NOT NULL PRIMARY KEY,
…
8kb
- 2,639
- 2
- 32
- 36
14
votes
1 answer
Why am I seeing key lookups for all rows read, instead of all rows matching the where clause?
I have a table such as the following:
create table [Thing]
(
[Id] int constraint [PK_Thing_Id] primary key,
[Status] nvarchar(20),
[Timestamp] datetime2,
[Foo] nvarchar(100)
)
with a non-clustered, non-covering index on the Status…
Twicetimes
- 263
- 1
- 5
7
votes
1 answer
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 an included column when creating a nonclustered…
J.D.
- 40,776
- 12
- 62
- 141
6
votes
2 answers
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-remove-key-lookup/
So if you have a key lookup you…
peter
- 2,187
- 5
- 19
- 39
4
votes
1 answer
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 actual execution plan it says
Expected Rows :…
Gavin
- 546
- 1
- 5
- 15
4
votes
2 answers
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 locates the row via the RID?
Does different…
J.D.
- 40,776
- 12
- 62
- 141
2
votes
1 answer
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,
StatusReasonComment, UserEnteredStatusDateTime,
…
Evan Barke
- 33
- 3
2
votes
1 answer
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 to do with the Cursor .
Is there any way I could…
Lucy
- 299
- 4
- 7
- 14
1
vote
1 answer
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
WHERE COL20 < 10000 ---- COL20 IS NOT THE…
Amam
- 399
- 1
- 5
- 11
1
vote
2 answers
Why can SQL Server not perform a TOP N SORT between an Index Seek and the Key Lookup?
I am looking into a minor performance issue, where an optimizer tool is basically saying, "hey just go ahead and include all the columns on this table in this index" which is a horrible solution in my opinion.
My thought process brought me to think…
Morgeth888
- 113
- 3
1
vote
1 answer
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 don't know how to approach it with a complicated…
Sicilian-Najdorf
- 381
- 4
- 13
1
vote
0 answers
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, C returns potato
query C returns lettuce,…
bountrisv
- 11
- 3
1
vote
2 answers
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 Of Columns...]
FROM Results_201505
…
MSM
- 113
- 1
- 6