How to create index to optimize a "Key Lookup"

Question

I have an SP which on first run, can run for over a minute. On second run, it takes less than a second.

To fix, I check the Execution Plan which shows this:

So I add the following index, which as far as I know should create an index so it doesn't use the Key Lookup:

CREATE NONCLUSTERED INDEX [ix_account_id_include_utility_id] 
ON dbo.account (account_id)
 include ( utility_id)

I create the index which took 1 min 50 seconds.

I then check the execution plan again. But it has exactly the same plan with the Key Lookup.

Am I doing something wrong here with the index?

I am a newbie at indexing and optimization, so any advice would be appreciated.

Show us the query inside the sproc and [paste the plan](https://www.brentozar.com/pastetheplan/) — Caius Jard, May 17 '22 at 04:15

score 2 · Accepted Answer · answered May 17 '22 at 04:23

2

Keylookup means that a specific field is not available in the index and we have to go to the data page to pick up the field.

In your case, accountid is being used to pickup the utility_id from the data page of Account table.

What you have to do is, add this utility_id as part of the included column of either of the right most two indexes (as highlighted in Right box), to avoid the key lookup.

But, in your case, you are again adding the index to the account table. So, it is not leveraging the newly created index.

answered May 17 '22 at 04:23

Venkataraman R

12,181
2
31
58

1

Thank you, I added it to the CustId index and you are correct, it then used that index instead of the Key Lookup. – Greg Gum May 17 '22 at 04:56
Happy to note. Glad it helped. – Venkataraman R May 17 '22 at 08:42

How to create index to optimize a "Key Lookup"

1 Answers1