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.
17 questions
1
vote
2
answers
154
views
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 ...
- 13
14
votes
1
answer
807
views
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 ...
- 263
0
votes
2
answers
227
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 ...
- 119
2
votes
1
answer
216
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,
...
- 33
6
votes
1
answer
664
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 ...
- 39.5k
0
votes
2
answers
480
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. ...
- 39.5k
1
vote
1
answer
119
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 ...
- 371
4
votes
2
answers
7k
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 ...
- 39.5k
2
votes
1
answer
669
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 ...
- 299
1
vote
0
answers
438
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, ...
- 11
32
votes
3
answers
57k
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 ...
- 707
18
votes
2
answers
6k
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
...
- 2,639
1
vote
2
answers
2k
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 ...
- 113
25
votes
3
answers
4k
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 ...
- 683
1
vote
1
answer
294
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
...
- 399
6
votes
2
answers
11k
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-...
- 2,187
4
votes
1
answer
262
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 ...
- 546