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
1 vote
2 answers
113 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 ...
Morgeth888's user avatar
14 votes
1 answer
763 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 ...
Twicetimes's user avatar
0 votes
2 answers
199 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 ...
A.HADDAD's user avatar
  • 119
2 votes
1 answer
184 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, ...
Evan Barke's user avatar
6 votes
1 answer
555 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 ...
J.D.'s user avatar
  • 37.1k
0 votes
2 answers
370 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. ...
J.D.'s user avatar
  • 37.1k
1 vote
1 answer
112 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 ...
Sicilian-Najdorf's user avatar
4 votes
2 answers
6k 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 ...
J.D.'s user avatar
  • 37.1k
2 votes
1 answer
649 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 ...
Lucy's user avatar
  • 299
1 vote
0 answers
435 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, ...
bountrisv's user avatar
31 votes
3 answers
53k 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 ...
Serdia's user avatar
  • 687
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 ...
8kb's user avatar
  • 2,619
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 ...
MSM's user avatar
  • 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 ...
GarethD's user avatar
  • 683
1 vote
1 answer
279 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 ...
Amam's user avatar
  • 389
6 votes
2 answers
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-...
peter's user avatar
  • 2,187
4 votes
1 answer
249 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 ...
Gavin's user avatar
  • 546