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

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';
;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
cp.plan_handle
,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
,cp.usecounts
,qp.query_plan
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,'[');