Sql-server – Reducing Key Lookups

bookmark-lookupperformancesql serversql-server-2008

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 can create an index with the 'include' columns to cover the non index columns you have in the select statement.

For instance,

SELECT ID, FirstName FROM OneIndex WHERE City = 'Las Vegas'
GO

This index will include a key lookup,

CREATE NONCLUSTERED INDEX [IX_OneIndex_City] ON [dbo].[OneIndex]
(
[City] ASC
) ON [PRIMARY]
GO

But this one will remove the key lookup,

CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex]
(
City
) INCLUDE (FirstName,ID) ON [PRIMARY]
GO

I mean how much of an impact will this have on performance? The key lookup has an operator cost of 0.295969 (99%), but what does that really mean?

How do you know that you need the second index there, and at what point does it become the case that you are trying to add too many indexes and it is not worth it?

It seems to me that some queries can include index scans, key lookups, and still seem to perform very fast.

Best Answer

Imagine the phone company has a list of phone numbers, including who the customer is, where they live, what their billing number is, and so on. The Primary Key could be the phone number.

They give you the White Pages. That's like a non-clustered index, which ordered by Name, including columns like the address.

If you want to find all the Farleys in the book, and are interested in their addresses, then the white pages is all you need. You can quickly Seek to the Farleys (finding the Fs, and so on), and then you have all the information you need.

But if you want their billing numbers, then you need to do a lookup. You can quickly find all the phone numbers of the Farleys, but then you need to take each of them (hundreds) and do another Seek (lookup) in the main (clustered) index, the one that's ordered by the phone number. Each of those is roughly the same cost as the seek to find the Farleys, making your query run orders of magnitude worse.

And there's a threshold. At some point, the database will realise that it's quicker just to go through every page of the clustered index, checking every record to see if it's of interest.

Seriously - get rid of Lookups. Your queries might be fast now, but probably won't scale.

Related Question