Find All Queries Using a Plan Guide in Cache

The inspiration for this post came a few days ago when a developer wanted to figure out if his application’s queries were still using plan guides that had been setup a couple of years ago. I asked him if he knew the specific queries in question, and of course, he did not.

My first thought was to create an Extended Events session to capture the “sqlserver.plan_guide_successful” and “sqlserver.plan_guide_unsuccessful” events. This would tell me, upon execution, if a query was successful or unsuccessful at using a plan guide. Unfortunately, the developer wasn’t able to execute the queries at the time, so this wouldn’t work in this case.

My next thought was to drill down into the database in Object Explorer -> Programmability -> Plan Guides and look to see what plan guides actually existed. Then I could drill into each individual plan guide and find the query that plan guide referenced. I could then lookup that query in cache and find the execution plan to see if the query was indeed using the plan guide. (Note – you can also query sys.plan_guides to get a list of all plan guides created on the database)

This can usually be done by looking at the properties window of the left-most operator (the Select operator in this case) of the query plan. If the query is using a plan guide, there will be two Properties: PlanGuideDB and PlanGuideName.

This could be tedious and take a little bit of time if you needed to lookup queries for many different plan guides. There had to be another way.

If you look at the raw XML of the execution plan, you can see the same PlanGuideDB and PlanGuideName properties (see line #5 below):

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.2100.60">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="select ? from Schema1.Object1" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" PlanGuideDB="AdventureWorks2012" PlanGuideName="PlanGuide_1" StatementSubTreeCost="0.00363" StatementEstRows="290" StatementOptmLevel="TRIVIAL" QueryHash="0x4578128A64C31C86" QueryPlanHash="0x4C7F74CCEDD0EAE0">
          <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
          <QueryPlan NonParallelPlanReason="MaxDOPSetToOne" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="120">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="207295" EstimatedPagesCached="51823" EstimatedAvailableDegreeOfParallelism="1" />
            <RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="290" EstimateIO="0" EstimateCPU="2.9e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00363" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
              <OutputList>
                <ColumnReference Column="Expr1003" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1003" />
                    <ScalarOperator ScalarString="ScalarString1">
                      <Const ConstValue="Value1" />
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp NodeId="1" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="290" EstimateIO="0.003125" EstimateCPU="0.000476" AvgRowSize="9" EstimatedTotalSubtreeCost="0.003601" TableCardinality="290" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                  <OutputList />
                  <IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
                    <DefinedValues />
                    <Object Database="Database1" Schema="Schema1" Table="Object1" Index="Index1" IndexKind="NonClustered" />
                  </IndexScan>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Ah ha! We should be able to search through the execution plan xml to find all plans that contain %PlanGuideName% currently in cache. With that in mind I came up with the following query. (Note: This will only find queries using a plan guide that are currently cached. It is very possible that a query that is not in the procedure cache could use a plan guide, also. But if it isn’t cached, we won’t find it.)

--Could take a while to execute on a server with a large plan cache, be patient
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT qt.text
	,DB_NAME(qt.dbid) AS db_name
	,LEFT(SUBSTRING(CAST(qp.query_plan AS nvarchar(max)), CHARINDEX('PlanGuideName', 
	CAST(qp.query_plan AS nvarchar(max))) + 15, 100), 
	CHARINDEX('"', SUBSTRING(CAST(qp.query_plan AS nvarchar(max)), 
	CHARINDEX ('PlanGuideName', CAST(qp.query_plan AS nvarchar(max))) + 16, 100))) AS PlanGuideName
	,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads]
	,qs.total_elapsed_time / qs.execution_count / 1000 AS [Avg Elapsed ms]
	,qs.execution_count
	,qs.last_execution_time
	,qs.creation_time as Compile_Time
	,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE CAST(qp.query_plan AS nvarchar(max)) LIKE ('%PlanGuideName%')
	AND qt.text NOT LIKE '%sys.dm%' --filter out any DBA user queries that may be hitting system tables
ORDER BY qs.last_execution_time desc
OPTION (RECOMPILE, MAXDOP 1);--prevent this query from filling the cache & try to minimize the cpu impact

Sample output:

Voilà! Now we have a list of all the queries, currently in cache, that are using plan guides. Much easier than digging through each Plan Guide individually!