Execution Plans: Key Lookup = Add Index
-
Just a Double Check; As I understand it, when I'm reading an Execution plan, If I see a Key Lookup, the rule of thumb is/always/should be / that it can be eliminated, and performance (probably) enhanced by adding a new index that collects the lookup column(s) with the index it was scanning against right?
I'm not looking for actual help on any performance issue, but making sure I have plan of attack when analyzing execution plans.
I'm looking at a probably typical All-In-Wonderful views that join a dozen tables together for a search form; No one node has more than a 6% cost, but i bet there are 100 nodes in the execution plan.
It actually performs pretty well, It just got me thinking i should create a simple list of things to look for in execution plans.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I don't know that I'd say that's always right, but yeah, that can be a great solution. Add the columns the the INCLUDE list of the index that was part of the original seek (or scan) and the lookup will go away. However, you have to take into account the added storage & overhead needed to maintain the larger index. And, adding two or three or even up to 5 columns, depending on the data sizes & data types, is fine, but adding 15 or 20 is getting crazy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Lowell (8/22/2011)
Just a Double Check; As I understand it, when I'm reading an Execution plan, If I see a Key Lookup, the rule of thumb is/always/should be / that it can be eliminated,No, not always.
If you have a query that returns one row and to eliminate the key lookup you need to add 20 columns to the index, it's not worth it. That's one extreme. Depends on how much you have to widen the index by to eliminate it and now costly that lookup is.
and performance (probably) enhanced by adding a new index that collects the lookup column(s) with the index it was scanning against right?
No, by widening the index that was used for the seek to cover the columns being looked up
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Is there a better cost/ratio by adding a column(s) to the index versus including them?
I've sometimes noticed the index will grow considerably larger by adding columns, but less when the extra columns are included...and I'm by no means an expert on the subject...
Is there a general rule of thumb for this?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
GilaMonster (8/22/2011)[hrNo, not always.
...up
Gail I was looking more for a rule of thumb, I'm sure that just like you identified, there are exceptions where the key lookup is better...testing would determine that.
but is it ok to say as a general rule(80% or90% of the time?), try to eliminate key lookups with either a covering index or an index with included columns?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
It depends on the cost of the key lookup, the gain from covering the query and the increase in DB size (maintenance time, backup size) and impact on data modifications.
Can't give you a %, depends on what kind of database activity you have. I'd be far more likely to cover in a datawarehouse scenario (heavy, large reads, few writes) than in an OLTP environment (lots of small reads, lots of writes)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
MyDoggieJessie (8/22/2011)
Is there a general rule of thumb for this?If the column is in the where or join (or order, group) then it needs to be in the key. Otherwise (with a few exceptions) as an include.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply