[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

clustered index and key lookup

My query uses a clustered index on table tb1.

When I run the query the optimiser performs a seek on the clustered index and also a key lookup on tb1.

I would only expect to see a Lookup if I was using a non-covering nonclustered index.

I am not sure why the optimizer needs to perform a key lookup if all table columns are included in the clustered index?

 1
0
Mr_Shaw
Asked:
Mr_Shaw
  • 4
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the clustered key has the data still only in the leaf nodes, so not "in the clustered index" itself.
so, the key lookup is still needed to get data from the leaf data nodes.
0
 
Mr_ShawAuthor Commented:
I've now added a nonclustered index which covers the primary key and the column listed in the ouput list of the Key Lookup. However, I am still getting a Key Lookup.

This is strange.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this seems to be a more complex query.
the key lookup might not be the actual problem?
without being able to see the "full context", this will be difficult to tackle ...
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Mr_ShawAuthor Commented:
R u right this is a complex query which I have been ask to optimize.

I don't really want to play around with the SQL as the logic is MAD.

It has another Key Lookup with a cost of 85% which i've also tried adding a covering index. The problem happens, even with a covering index the key lookup remains. Bizar!!
0
 
Mr_ShawAuthor Commented:
At the moment I can't post the query. Sorry.
0
 
Mr_ShawAuthor Commented:
thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now