How to create index to optimize a "Key Lookup"-CodePudding

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.

CodePudding user response：

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.