Find Queries Using a Key Lookup Operator

While teaching about Extended Events and Execution Plans last week, Jason, one of the people in the class, asked: Is there a way in Extended Events to find queries using a Key Lookup operation? Sadly, the answer is no. However, you can query the Execution Plans in cache or in the Query Store to find this. Thanks for the question Jason. Here’s your answer.

Finding Key Lookups

Since we can’t have Extended Events just feed us the information, we have to query the plans. That means pulling out data from the XML. So, to find queries that are using the Key Lookup operator, we can do this:

SELECT DB_NAME(detqp.dbid),
       SUBSTRING(   dest.text,
                    (deqs.statement_start_offset / 2) + 1,
                    (CASE deqs.statement_end_offset
                         WHEN -1 THEN
                             DATALENGTH(dest.text)
                         ELSE
                             deqs.statement_end_offset
                     END - deqs.statement_start_offset
                    ) / 2 + 1
                ) AS StatementText,
       CAST(detqp.query_plan AS XML),
       deqs.execution_count,
       deqs.total_elapsed_time,
       deqs.total_logical_reads,
       deqs.total_logical_writes
FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle, deqs.statement_start_offset, deqs.statement_end_offset) AS detqp
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE detqp.query_plan LIKE '%Lookup="1"%';

I’m cheating a little in not writing an official XQuery and just using the LIKE command. However, once you know that you can’t look for a Key Lookup operator within the XML, this gets easy.

Wait. What did I just type?

Yeah, the Key Lookup operator, it’s a bit of a construct. It’s just a really fancy Clustered Index Seek operation.

Doubt me? Well, look for the string “Key Lookup” in any execution plan XML. You won’t find it. However, you will find that the operators with a property set to ‘Lookup=”1″‘ are indeed what we’re after within the execution plan. Just, in the XML they’re labeled as Clustered Index Seek:

If you doubt that, just identify the NodeID in the plan that shows Key Lookup and then find that same NodeID in the XML. You’ll see I’m right. The GUI then translates that for us into the operator we’re used to seeing.

Conclusion

If you want to get fancy with the XML, you certainly can. However, if you just want to get right to the information you’re interested in, this is one way you do it. It’d be easy to modify the above query to hit the Query Store instead of the cache. Regardless, I hope this answers your question Jason. Thank you for asking and for attending my presentation.