CODEWITHSUNDEEP
sql-serverperformanceindexing
Teoman shipahi
Teoman shipahi

Reputation: 23052

SQL makes key lookup instead of seek over indexed column

I have 1 large and 2 small tables inner joined. I added appropriate indexes over large table. Even if query is fast (most of the time) some times it is getting over 3 seconds. When I checked execution plan, seems like SQL goes with key lookup instead of index seek.

Here is my query;

enter image description here

and my execution plan;

enter image description here

and here execution details;

enter image description here

Am I missing something here?

Upvotes: 2

Views: 1420

Answers (2)

Teoman shipahi
Teoman shipahi

Reputation: 23052

After suggestion from Martin Smith, I re-create my index as below;

enter image description here

and now, execution plan is mush satisfied for me;

enter image description here

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453406

A key lookup is a seek. It is looking up using the key.

The non clustered index always includes the clustered index key (or physical rid if the table isn't clustered in which case you get a "bookmark lookup" instead)

Because the index used in the previous index seek does not contain the CreateDate column it needs to use the clustered index key to seek into the clustered index to retrieve it. This type of seek to retrieve additional columns is called a key lookup.

If you wanted to get rid of the lookup you could consider adding CreateDate as an included column to the index on NewsCategoryUrlId.

Though as Hadi says in the comments your case sounds like parameter sniffing or outdated statistics. Often a plan with a non covering index seek and key lookups may be generated if the optimiser believes the parameter value to be selective and be problematic if it is not selective.

With parameter sniffing the problem can arise if a plan is compiled for a selective value and then cached and reused for a less selective value.

Outdated statistics may not reflect the true selectivity of the parameter value the plan is compiled for in the first place.

Upvotes: 3

Related Questions