Keylookup -what is the harm

  • chaithu559

    SSCommitted

    Points: 1574

    Hi Experts,

    I know that key lookup happens , if any one of the select column that doesnt have index on it , but one column that is used a a predicated has clustered index on it. What does key lookup do , how does it locates the row, why is it not good to have it the execution plan?

    Please help me in understanding the above

    Thanks

    Chaithanya M

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Because it's slow. A key lookup is a single row at a time, so if you have key lookups happening on 10000 rows,that's 10000 seeks of the clustered index.

    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
  • Adam Haines

    SSC-Insane

    Points: 23197

    A key lookup occurs when the optimizer has to go to the clustered index for every column not present in the index. The number of seeks is equal to the number of rows returned by the table in questiony, as Gail said

    Here are some other interesting performance problems that can manifest from lookups:

    extra IO (the lookup), locking and blocking (due to a shared lock on the clustered index), dead locks, and the eventual use of table/index scans (where the optimizer deems the lookup more expensive than a scan).

  • Adam Haines

    SSC-Insane

    Points: 23197

    With that said, don't go and add every column to every index... This is something you have to watch and take action where appropriate.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721105

    Good points above, and Adam is right, but a couple more things.

    Don't add an index for every column either, some people to that, and it's a problem as well.

    Also, think about adding multiple columns to an index; you can often improve performance and cover multiple queries like this.

    Consider INCLUDEing columns in your index. Not a ton, since you start to slow down the index, but add 1, 2, 3 when it covers a lot of queries, or queries run very often.

Viewing 5 posts - 1 through 5 (of 5 total)

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