March 21, 2014 at 10:15 am
Hi,
I am working on Query tuning. I observed the execution plan. I found that in one place Key look up occurred. Is it bad or good. If I avoid that key look up it will improve the performance or not? Please let me know.
March 21, 2014 at 1:54 pm
March 21, 2014 at 2:32 pm
You cannot say in advance if Key Lookup is good or bad. If your query needs to return several columns you cannot avoid it (or you can if you scan the whole table). By query tuning you should not always try to eliminate Key Lookup. Please post the query and the execution plan and you will get soon advices what and how to tune.
BTW. The book mentioned by suneel (http://www.goodreads.com/book/show/14975241-sql-server-2012-query-performance-tuning) is excellent for query tuning.
___________________________
Do Not Optimize for Exceptions!
March 21, 2014 at 4:12 pm
In general a key lookup means that the index used didn't have all the columns available so it had to go to the clustered index to get those missing columns. For small data sets, this isn't that big a deal. For any one row retrieved by the index seek operation, you'll get 3-4 reads from the key lookup operation. But, as the number of rows retrieved increases, key lookups become extremely painful. Then you need to look into making the nonclustered index covering as was stated above. A covering index has all the columns needed by the query. That can be a clustered index (they're always covering since the data is stored with the clustered index), a set of keys in a non-clustered index, or INCLUDE columns in addition to the keys from a non-clustered index. Picking the right thing to deal with the lookup is a matter of testing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 21, 2014 at 4:14 pm
milos.radivojevic (3/21/2014)
You cannot say in advance if Key Lookup is good or bad. If your query needs to return several columns you cannot avoid it (or you can if you scan the whole table). By query tuning you should not always try to eliminate Key Lookup. Please post the query and the execution plan and you will get soon advices what and how to tune.BTW. The book mentioned by suneel (http://www.goodreads.com/book/show/14975241-sql-server-2012-query-performance-tuning) is excellent for query tuning.
Thanks!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 21, 2014 at 4:17 pm
suneel kamavaram (3/21/2014)
It needs a covering index.See here[/url] and BOL
Thanks for bringing up the book. For execution plans though, I'd recommend this one. You can even download it for free.[/url]
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply