SQL Script : How to find Nested Loops in Execution Plans for a specific table

18 January,2016 by Jack Vamvas

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

  1. Increased IO – More calls may be made to the disk
  2. Higher CPU – Extra cycles may be required to process data
  3. 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

Download Find nested loops in execution plans

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Script : How to find Nested Loops in Execution Plans for a specific table


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer