Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

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

18 January,2016 by Tom Collins

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


DECLARE @Table_Name sysname = 'my_table_name';
,operators.value('(NetsedLoops/Object/@Schema)[1]','sysname') AS Schema_Name
,operators.value('(NetsedLoops/Object/@Table)[1]','sysname') AS Table_Name
,operators.value('(NetsedLoops/Object/@Index)[1]','sysname') AS Index_Name
,operators.value('@PhysicalOp','nvarchar(50)') AS Physical_Operator
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY query_plan.nodes('//RelOp') rel(operators)
WHERE operators.value('@PhysicalOp','nvarchar(60)') IN ('Nested Loops')
AND operators.value('(NetsedLoops/Object/@Table)[1]','sysname') = QUOTENAME(@Table_Name,'[');

Author: Tom Collins (


Verify your Comment

Previewing your Comment

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

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.


Post a comment on How to find Nested Loops in Execution Plans for a specific table | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer