This is actually a blog post I thought I’d written more than two years ago, but this morning when I went looking for it after receiving a question by email I realized that I’ve never blogged about this before. At PASS Summit 2010 I presented a session on performance tuning SQL Server by digging into the plan cache, and since then I have blogged a number of other plan cache parsing scripts. One of the demo scripts I showed at PASS was a query to find plans and statements in the plan cache that perform a Key Lookup operation. An example statement that performs a Key Lookup is shown below.
USE [AdventureWorks]; GO SELECT NationalIDNumber, HireDate, MaritalStatus FROM HumanResources.Employee WHERE NationalIDNumber = N'14417807'; GO
Taking a look at the execution plan XML we can see that there isn’t an actual Key Lookup operator, instead the Index Scan operator associated with the Clustered Index Seek will have a Lookup attribute that is set to a value of 1, as shown in the XML snip-it below:
<RelOp NodeId="3" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="17" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="290" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"> <OutputList> <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="MaritalStatus" /> <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="HireDate" /> </OutputList> <IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="MaritalStatus" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="HireDate" /> </DefinedValue> </DefinedValues> <Object Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Index="[PK_Employee_EmployeeID]" TableReferenceId="-1" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="EmployeeID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[AdventureWorks].[HumanResources].[Employee].[EmployeeID]"> <Identifier> <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="EmployeeID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp>
As a side note, the ShowplanXML uses the IndexScan XML element for both Seeks and Scans. An Index Seek is identified by the SeekPredicates child element, whereas an Index Scan would have a Predicates child element instead. Another important note when dealing with ShowplanXML is that the attribute values of a cached execution plan differ from those of an Actual execution plan collected from SSMS. In the above XML from the plan cache, the Lookup attribute has a value of 1. However, in an Actual plan collected by SSMS, the value would be true as shown in the Actual XML below:
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
When building an XML parser for querying the plan cache, the parser must check for a value of 1, not true, when looking for a Lookup attribute in the cached plan XML. Given this information we can build a parser to search the plan cache and find the statements and columns associated with Key Lookups occurring.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text, n.query('.'), i.value('(@PhysicalOp)[1]', 'VARCHAR(128)') AS PhysicalOp, i.value('(./IndexScan/Object/@Database)[1]', 'VARCHAR(128)') AS DatabaseName, i.value('(./IndexScan/Object/@Schema)[1]', 'VARCHAR(128)') AS SchemaName, i.value('(./IndexScan/Object/@Table)[1]', 'VARCHAR(128)') AS TableName, i.value('(./IndexScan/Object/@Index)[1]', 'VARCHAR(128)') as IndexName, i.query('.'), STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)') FROM i.nodes('./OutputList/ColumnReference') AS t(cg) FOR XML PATH('')),1,2,'') AS output_columns, STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)') FROM i.nodes('./IndexScan/SeekPredicates/SeekPredicateNew//ColumnReference') AS t(cg) FOR XML PATH('')),1,2,'') AS seek_columns, i.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)') as Predicate, cp.usecounts, query_plan FROM ( SELECT plan_handle, query_plan FROM ( SELECT DISTINCT plan_handle FROM sys.dm_exec_query_stats WITH(NOLOCK)) AS qs OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp ) as tab (plan_handle, query_plan) INNER JOIN sys.dm_exec_cached_plans AS cp ON tab.plan_handle = cp.plan_handle CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS q(n) CROSS APPLY n.nodes('.//RelOp[IndexScan[@Lookup="1"] and IndexScan/Object[@Schema!="[sys]"]]') as s(i) OPTION(RECOMPILE, MAXDOP 1);
The first CROSS APPLY above breaks out the individual statements so that we can tie the Key Lookup operation to the specific statement that is causing it to occur. The second CROSS APPLY breaks out the actual IndexScan element in the XML and filters out any Key Lookups that are happening in the ‘sys’ schema, since many of the system tables have frequent Key Lookup operations that occur. Using the two XML fragments created by the CROSS APPLY operations we can extract the statement, the table and index the lookup operation occurs against, the output columns of the lookup operation and the seek predicate being used to perform the lookup. With this information we can look at the indexing on our tables and determine if a change to a non-clustered index to make it covering is warranted to improve performance or not.
Hope this is helpful!
15 thoughts on “Finding Key Lookups inside the Plan Cache”
Jonathan,
How hard is it to add the number of times the cached plan has been used?
Thanks,
SQLOzzie
PS This made the process a little easier – dialing it in by database
WHERE i.value(‘(./IndexScan/Object/@Database)[1]’, ‘VARCHAR(128)’) = ‘[DatabaseName-you need the brackets]’
Hey Douglas,
If you carry the plan_handle up one level you can use it to join to the sys.dm_exec_cached_plans DMV to get the usecounts. Posting the code in the comment didn’t work out so good so I updated it in the post to now include the usecounts.
Cheers!
Great post again Jonathan.
Would it be possible to join to sys.dm_exec_sql_text to get the Proc name ?
CROSS APPLY sys.dm_exec_sql_text(tab.plan_handle) AS st
Thanks
Raj
Hey Rajesh,
Did you try it and have a problem? I don’t see any reason why it would have a problem if you tried it.
It works fine , thanks Jonathan
Thanks
Raj
How can I eliminate the duplication of rows. Because of the XML, we can’t just slap a DISTINCT on the SELECT.
Hey Mark,
You don’t have to keep the XML in the outputs, it’s just a matter of changing what is being output to fit your own personal needs. The above output is what works for most of my situations and is not the only way you can get the information. It is more of an example to get people started with knowing how to mine this information. If you’d like a specific customization or change made, email me offline and we can talk about what that might cost to write.
A cool enhancement would be to output the percentage of the plan cost taken up by the key lookup (to help determine if it is worth bothering with). It is in the graphical plan, but I don’t see it in the XML. How can we extract that?
Hey Mark,
The cost percentage information is calculated (sometimes incorrectly) by SSMS in the graphical display, it’s not a part of the XML and therefore not able to be extracted without trying to reproduce the calculations (and also correct the bugs in the way SSMS calculates certain plans/operations), and the calculations aren’t documented.
Hi Jonathan
Excellent article..
Will this script work on SQL 2014?
thanks
Yes it should.
Hi Jonathan,
Great post.
Is there any way to find out the number of key lookup executions within the plan? Or is that not possible because the plans will mostly be estimated plans?
We’re trying to uncover which plan is causing the SQLServer:Access Methods: Key Lookups/sec value to peak at certain times.
Thanks,
Dan