June 11, 2012 at 6:49 am
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
June 11, 2012 at 6:56 am
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
June 13, 2012 at 2:58 pm
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).
June 13, 2012 at 2:59 pm
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.
June 14, 2012 at 9:28 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply