clustered index and key lookup

Posted on 2011-05-12
Last Modified: 2012-05-11
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?

Question by:Mr_Shaw
    LVL 142

    Accepted Solution

    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.

    Author Comment

    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.
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    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 ...

    Author Comment

    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!!

    Author Comment

    At the moment I can't post the query. Sorry.

    Author Closing Comment


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now