how to find out the `Key Lookups` in the current query plan?

I am putting a query to list the key lookups that are present in the current requests being executed, I basically would like to tackle then and see if I could eliminate these key lookup from the execution plan.

To get to these Key lookups I use the following query:

SELECT 
    er.session_id,
    er.blocking_session_id,
    er.start_time,
    er.status,
    dbName = DB_NAME(er.database_id),
    er.wait_type,
    er.wait_time,
    er.last_wait_type,
    er.granted_query_memory,
    er.reads,
    er.logical_reads,
    er.writes,
    er.row_count,
    er.total_elapsed_time,
    er.cpu_time,
    er.open_transaction_count,
    er.open_transaction_count,
    s.text,
    qp.query_plan,
    logDate = CONVERT(DATETIME,GETDATE()),
    logTime = CONVERT(DATETIME,GETDATE())
FROM sys.dm_exec_requests er 
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) s
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERe er.session_id <> @@SPID
  and CONVERT(VARCHAR(MAX), qp.query_plan) LIKE '%IndexScan Lookup%'

The problem that I am facing with this query is that it returns any key lookup regardless of its cost.

I would like to filter those, I would like only to see the expensive key lookups.

How can I filter my query to show only the expensive look up operations?

This is not a good idea.

Take it from me. A long time back I coded a check into sp_BlitzCache that found plans with Key Lookups in them, and then compared that operator’s cost to the total cost of the plan. If it was >50% or something, I’d flag it as an expensive one.

I would not do this ever again.

The thing is, cost has absolutely zero Kelvin real world meaning. This has become more obvious with operator times being present in query plans.

Going around fixing every Key Lookup isn’t likely to solve your worst performance problems, and is very likely to end you up with a lot of very wide indexes.

Your better bet is to find queries to tune based on average CPU consumption, or ones that are known to cause specific business pains, and tune the actually-slow parts of those plans.

Key Lookups don’t indicate a performance problem, just like high costs for a plan or operator don’t. You need to get the actual execution plan to figure out what’s under-performing.

You can use XQuery to filter the XML plan. For example:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

..........
..........

WHERE er.session_id <> @@SPID
  AND qp.query_plan.exist('
  ShowPlanXML/BatchSequence/Batch/Statements/*
    [
     xs:decimal(@StatementSubTreeCost) div 2
     >
     (descendant::RelOp[IndexScan[@Lookup="1"]][1]/@EstimatedTotalSubtreeCost)
    ]
') = 1;

What this does is as follows:

• It begins at the top of the plan, and descends to ShowPlanXML/BatchSequence/Batch/Statements/* node where * means any name.
• It takes the value of the StatementSubTreeCost attribute, converts to decimal then divides by 2 (you can pick another calculation).
• And then takes all descendant nodes of the original one called RelOp,
  • checks that they have a child node called IndexScan that has an attribute Lookup which has a value 1
  • takes EstimatedTotalSubtreeCost attribute
• And then compares the first value to all of these.
• If there is a match it returns 1

db<>fiddle