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
The second method is to see if you can create a “covering index” that satisfies the entire query or at least eliminates the key lookups. A “covering index” is simply a non-clustered index that has all of the columns needed to either satisfy the entire query or in our case, eliminate the need for a key lookup operation.
A key lookup occurs when data is found in a non-clustered index, but additional data is needed from the clustered index to satisfy the query and therefore a lookup occurs. If the table does not have a clustered index then a RID Lookup occurs instead.
A Key lookup occurs when the table has a clustered index and a RID lookup occurs when the table does not have a clustered index, otherwise known as a heap. They can, of course, be a warning sign of underlying issues that may not really have an impact until your data grows.
A SQL index is used to retrieve data from a database very fast. Indexing a table or view is, without a doubt, one of the best ways to improve the performance of queries and applications. A SQL index is a quick lookup table for finding records users need to search frequently.
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.
Key lookup doesn't mean "look up the key", but "look up the row based on the key".
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With