Post reply

Execution plan ignoring index, insisting on Key Lookup

  • September 15, 2015 at 12:02 pm

    #303171

    I have query with an expensive Key Lookup on a joined table. The predicate is the column that I'm joining on, and the output list contains two columns from the joined table.

    I've created a basic non-clustered index covering the predicate column and include-ing the two output columns. However, the execution plan ignores this, and insists on using the primary key of the joined table to do the expensive key lookup. I've tried adding the included columns to the index directly and there's no change. I've also tried running dbcc freeproccache and no change.

    Any ideas?

  • September 15, 2015 at 12:09 pm

    #1826861

    That's probably because the optimizer thinks that the key lookup is more efficient than the index.

    You can force the use of the index and compare performance.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 15, 2015 at 1:59 pm

    #1826897

    It's going to be down to estimations. Key lookups are OK on smaller row counts.

    Can you post the plan?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 15, 2015 at 2:19 pm

    #1826904

    Actually, it was a bonehead move on my part. I realized that I was joining to another table using a different column from the table involved in the key lookup. Once I added that column to the index, SQL Server used the index.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply