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:

Key Lookup identified by Lookup="1" on a 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.

2 thoughts on “Find Queries Using a Key Lookup Operator

  • Robert Eder

    A LIKE where clause with a leading wildcard just feels dirty. However, to use an Xml method, you have to have a data type conversion from nvarchar to Xml in the where clause. This feels just as dirty.

    Changing the where clause as below is the way to do using an Xml method.

    WHERE CAST(detqp.query_plan AS XML).exist(‘//*[@Lookup=”1″]’) = 1

    The big catch is you may receive the error below fi you have large plans in your cache.

    Msg 6335, Level 16, State 102, Line 26
    XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.

    No panacea in this one!

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.