ShowPlanParser drops IF-condition query plans (StmtCond COND WITH QUERY) and MULTIPLE PLAN statement hashes

[erikdarlingdata]

Summary

ShowPlanParser.Parse drops two kinds of statement that carry their own plan or hashes:

1. StmtCond with StatementType="COND WITH QUERY", an IF EXISTS (SELECT …) condition. The condition's own QueryPlan (under <Condition>) is not parsed. It comes back as a statement with an empty StatementType, NULL QueryHash and QueryPlanHash, and a placeholder root node (PhysicalOp = "STATEMENT"). Its operators, its missing indexes, and every warning PlanAnalyzer would raise on them are lost.
2. StmtSimple with StatementType="MULTIPLE PLAN", a statement element that carries QueryHash and QueryPlanHash but no QueryPlan child. Both hashes come back NULL.

Repro

At 5336662ad; dev has no later change to PerformanceMonitor.PlanAnalysis/ShowPlanParser.cs.

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.4215.2"><BatchSequence><Batch><Statements>
<StmtCond StatementText="IF EXISTS (SELECT 1 FROM dbo.t WHERE id = @id)" StatementId="1" StatementCompId="1" StatementType="COND WITH QUERY" RetrievedFromCache="true" QueryHash="0x1111111111111111" QueryPlanHash="0x2222222222222222">
  <Condition><QueryPlan CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="104">
    <MissingIndexes><MissingIndexGroup Impact="90.5"><MissingIndex Database="[db]" Schema="[dbo]" Table="[t]"><ColumnGroup Usage="EQUALITY"><Column Name="[id]" ColumnId="1"/></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes>
    <RelOp NodeId="0" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimateIO="0.003" EstimateCPU="0.0001" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032" TableCardinality="100" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues/><Object Database="[db]" Schema="[dbo]" Table="[t]" IndexKind="Heap" Storage="RowStore"/></TableScan></RelOp>
  </QueryPlan></Condition>
  <Then><Statements><StmtSimple StatementText="RETURN" StatementId="2" StatementCompId="2" StatementType="RETURN NONE"/></Statements></Then>
</StmtCond>
<StmtSimple StatementText="SELECT c FROM dbo.u WHERE k = @k" StatementId="3" StatementCompId="3" StatementType="MULTIPLE PLAN" RetrievedFromCache="true" QueryHash="0x3333333333333333" QueryPlanHash="0x4444444444444444"/>
</Statements></Batch></BatchSequence></ShowPlanXML>

ShowPlanParser.Parse followed by PlanAnalyzer.Analyze, then listing plan.Batches.SelectMany(b => b.Statements):

1 type=''            QueryHash=NULL QueryPlanHash=NULL root=STATEMENT     missingIndexes=0
2 type='RETURN NONE' QueryHash=NULL QueryPlanHash=NULL root=RETURN NONE   missingIndexes=0
3 type='MULTIPLE PLAN' QueryHash=NULL QueryPlanHash=NULL root=MULTIPLE PLAN missingIndexes=0
AllMissingIndexes=0

Expected: statement 1 is COND WITH QUERY with hashes 0x1111… and 0x2222…, a Table Scan root and one missing index. Statement 3 has hashes 0x3333… and 0x4444….

Where

ParseStatementAndChildren (ShowPlanParser.cs:75-103) recurses into Condition, Then and Else for a StmtCond, but never reads the StmtCond element's own attributes. A <Condition> that holds a <QueryPlan> directly, rather than a nested statement element, is handed to the generic statement branch, which has no statement attributes to read. The MULTIPLE PLAN hashes are not read, apparently because the statement attributes are only taken when a QueryPlan child exists (not traced further).

Size

Measured on a 1-in-20 sample of the distinct cached and Query Store plans captured in one day on one production store class (15,928 plans):

• 2.9% of plans lose at least one statement's hash to one of these two cases;
• 2.8% of statement QueryPlanHash values are lost (18,531 in the XML, 18,005 parsed);
• 0.3% of missing-index suggestions are lost (971 <MissingIndex> elements, 968 parsed).

Most are stored-procedure plans, where IF EXISTS (…) checks are common.

[erikdarlingdata]

Claude posting for Erik Darling.

Fixed by #4470 (6f50c73). The plan parser now reads the query plan inside an IF condition. The condition statement (COND WITH QUERY) gets its own hashes, missing indexes and root operator, and UDF sub-plans on the condition are walked. A MULTIPLE PLAN statement without a captured plan keeps its QueryHash and QueryPlanHash. ShowPlanParserCondAndMultiplePlanTests covers both, using the repro plan from this issue. Closing.