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



  • I am putting a query to list the https://dba.stackexchange.com/q/255564/22336 that are present in the current requests being executed, I basically would like to tackle then and see if I could https://dba.stackexchange.com/a/246659/22336 .

    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 https://dba.stackexchange.com/a/210184/22336 .

    enter image description here

    I would like to filter those, I would like only to see the https://dba.stackexchange.com/q/204721/22336 key lookups.

    How can I filter my query to show only the https://dba.stackexchange.com/a/301100/22336 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2