15

I have a table User with a bunch of indexes. One of them is a unique index on the AccountIdentifier column.

Since this is a unique index, why is a key lookup required in addition to the index seek? The index seek tooltip reports that only one record is returned. I've also tried converting the index to a "unique key" type.

alt text http://s3.amazonaws.com/brandonc.baconfile.com/pitchurs/tmp/capture_2.png

BC.
  • 24,298
  • 12
  • 47
  • 62
  • 2
    As an aside: never ever do a `SELECT *` on a production system - **NEVER** - no exceptions. This is bad practice to begin with.... – marc_s Jul 03 '10 at 07:03

3 Answers3

17

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.

Matt
  • 74,352
  • 26
  • 153
  • 180
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    I did some experimenting. So it would seem selecting anything not in the index used results in a key lookup. That makes sense. – BC. Jul 03 '10 at 00:29
  • 1
    @BC - Basically yes but if you have a clustered index you can consider those columns to be included automatically. – Martin Smith Jul 03 '10 at 00:33
13

Key lookup doesn't mean "look up the key", but "look up the row based on the key".

erikkallen
  • 33,800
  • 13
  • 85
  • 120
7

See these articles and blog posts for some more background info on key lookups / bookmark lookups:

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459