Question: How can I find Key Lookups in Execution Plans for a specific table?
I executed the script on Find last time of update on a table and found there were a high amount of lookups on some high usage tables. I’d like to be able to find the statement and execution plans with high key lookups
Answer: Key Lookup is a bookmark lookup on a clustered index . According to the MS documentation a "key lookup is always associated with a Nested Loop".Depending on the circumstances and if an excessive amount of key lookups occurs this may be a strong indicator that some query tuning is required.
There are always a number of questions you should ask when deciding on how much time to spending on tuning query. It will depend on asking the right question and understanding the context. Experience plays a major part on these decisions , a SQL expert can be worth their weight in gold.
So what’s the big deal about excessive key lookups ? Here are some general points about the impact of high key lookups
- Increased IO – More calls may be made to the disk
- Higher CPU – Extra cycles may be required to process data
- Response time to the end user or process gets slower
In a future post – I’ll go into techniques you can implement to decrease Key Lookups.Read more about How to Remove a Key Lookup with an INCLUDE - SQL Server