Skip to content

Instantly share code, notes, and snippets.

@chadbaldwin

chadbaldwin/Actual execution plan - Bad.sqlplan

Last active September 29, 2025 16:32
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save chadbaldwin/2316bec6f0d435015bc7af66a15dc835 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save chadbaldwin/2316bec6f0d435015bc7af66a15dc835 to your computer and use it in GitHub Desktop.
Download ZIP
SQL Server 2022 Bug - Bad plan received with new CE for indexed views
Raw
README
[placeholder]
Raw
Actual execution plan - Bad.sqlplan
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Version="1.564" Build="16.0.4205.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="160" StatementSubTreeCost="126.466" StatementText="UPDATE dbo.vw_FooFiltered SET Code = 'ABC' WHERE FooID = @FooID" StatementType="UPDATE" QueryHash="0xD280ECD9D3E6F9E2" QueryPlanHash="0x45FC3E4D8415E259" RetrievedFromCache="true" StatementSqlHandle="0x09009E9BB574372BDDFB48BD416DC587C0EE0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="64" CompileTime="4" CompileCPU="4" CompileMemory="672">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="24263" EstimatedPagesCached="30329" EstimatedAvailableDegreeOfParallelism="10" MaxCompileMemory="2534424" />
<OptimizerStatsUsage>
<StatisticsInfo Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Statistics="[PK_FooDetail]" ModificationCount="0" SamplingPercent="11.5831" LastUpdate="2025-09-18T19:37:18.18" />
<StatisticsInfo Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Statistics="[IX_FooDetail_FooID]" ModificationCount="0" SamplingPercent="10.7957" LastUpdate="2025-09-18T19:37:18.3" />
<StatisticsInfo Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Statistics="[PK_Foo]" ModificationCount="0" SamplingPercent="13.5" LastUpdate="2025-09-18T19:37:17.94" />
<StatisticsInfo Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Statistics="[IXF_Foo_FooID_Code_Status]" ModificationCount="0" SamplingPercent="13.5421" LastUpdate="2025-09-18T19:37:18" />
</OptimizerStatsUsage>
<QueryTimeStats CpuTime="0" ElapsedTime="0" />
<RelOp AvgRowSize="9" EstimateCPU="5" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="5000000" LogicalOp="Sequence" NodeId="1" Parallel="false" PhysicalOp="Sequence" EstimatedTotalSubtreeCost="126.466">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<Sequence>
<RelOp AvgRowSize="14" EstimateCPU="5.02642E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Eager Spool" NodeId="2" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0117432">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Spool>
<RelOp AvgRowSize="14" EstimateCPU="2E-06" EstimateIO="0.02" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Update" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0232863">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Index="[PK_Foo]" IndexKind="Clustered" Storage="RowStore" />
<Object Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Index="[IXF_Foo_FooID_Code_Status]" Filtered="true" IndexKind="NonClustered" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[SandBox].[dbo].[Foo].[Code] = [Expr1002]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1002" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="18" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328428">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Column="Expr1002" />
<ColumnReference Column="Expr1010" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1010" />
<ScalarOperator ScalarString="[Expr1010]">
<Identifier>
<ColumnReference Column="Expr1010" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="18" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="5" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328428">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Column="Expr1002" />
<ColumnReference Column="Expr1010" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1010" />
<ScalarOperator ScalarString="CASE WHEN [Expr1009] THEN (0) ELSE (1) END">
<IF>
<Condition>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1009" />
</Identifier>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328418">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Column="Expr1002" />
<ColumnReference Column="Expr1009" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="'ABC'">
<Const ConstValue="'ABC'" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="12" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="7" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328408">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Column="Expr1009" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1009" />
<ScalarOperator ScalarString="CASE WHEN [SandBox].[dbo].[Foo].[Code] = 'ABC' THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="BINARY IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'ABC'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1500000">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</DefinedValue>
</DefinedValues>
<Object Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Index="[PK_Foo]" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[@FooID]">
<Identifier>
<ColumnReference Column="@FooID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[SandBox].[dbo].[Foo].[Status]&lt;'DELETED' OR [SandBox].[dbo].[Foo].[Status]&gt;'DELETED'">
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'DELETED'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'DELETED'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
</Spool>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="5" EstimateIO="83.6316" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="5000000" LogicalOp="Update" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="121.454">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Update WithOrderedPrefetch="true" DMLRequestSort="true">
<Object Database="[SandBox]" Schema="[dbo]" Table="[vw_IndexedView]" Index="[CIX]" IndexKind="ViewClustered" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[SandBox].[dbo].[vw_IndexedView].[Code] = [SandBox].[dbo].[Foo].[Code]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[vw_IndexedView]" Column="Code" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="14" EstimateCPU="20.9" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="5000000" LogicalOp="Inner Join" NodeId="12" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="32.8224">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Alias="[x]" Column="FooDetailID" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
</OuterReferences>
<RelOp AvgRowSize="14" EstimateCPU="5.02642E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Eager Spool" NodeId="14" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0117432">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Spool PrimaryNodeId="2" />
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="5.50016" EstimateIO="6.41053" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="5000000" EstimatedRowsRead="5000000" LogicalOp="Index Seek" NodeId="15" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="11.9107" TableCardinality="5000000">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Alias="[x]" Column="FooDetailID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Alias="[x]" Column="FooDetailID" />
</DefinedValue>
</DefinedValues>
<Object Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Index="[IX_FooDetail_FooID]" Alias="[x]" IndexKind="NonClustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Alias="[x]" Column="FooID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[SandBox].[dbo].[Foo].[FooID]">
<Identifier>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Update>
</RelOp>
</Sequence>
</RelOp>
<ParameterList>
<ColumnReference Column="@FooID" ParameterDataType="int" ParameterCompiledValue="(-1)" ParameterRuntimeValue="(-1)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Raw
Actual execution plan - Good.sqlplan
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Version="1.564" Build="16.0.4205.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160" StatementSubTreeCost="0.0367941" StatementText="UPDATE dbo.vw_FooFiltered SET Code = 'ABC' WHERE FooID = @FooID" StatementType="UPDATE" QueryHash="0xD280ECD9D3E6F9E2" QueryPlanHash="0xF608400EC73C8075" RetrievedFromCache="true" StatementSqlHandle="0x09009E9BB574372BDDFB48BD416DC587C0EE0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="64" CompileTime="2" CompileCPU="2" CompileMemory="608">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="24263" EstimatedPagesCached="30329" EstimatedAvailableDegreeOfParallelism="10" MaxCompileMemory="2534568" />
<OptimizerStatsUsage>
<StatisticsInfo Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Statistics="[PK_FooDetail]" ModificationCount="0" SamplingPercent="11.5831" LastUpdate="2025-09-18T19:37:18.18" />
<StatisticsInfo Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Statistics="[IX_FooDetail_FooID]" ModificationCount="0" SamplingPercent="10.7957" LastUpdate="2025-09-18T19:37:18.3" />
<StatisticsInfo Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Statistics="[PK_Foo]" ModificationCount="0" SamplingPercent="13.5" LastUpdate="2025-09-18T19:37:17.94" />
<StatisticsInfo Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Statistics="[IXF_Foo_FooID_Code_Status]" ModificationCount="0" SamplingPercent="13.5421" LastUpdate="2025-09-18T19:37:18" />
</OptimizerStatsUsage>
<QueryTimeStats CpuTime="0" ElapsedTime="0" />
<RelOp AvgRowSize="9" EstimateCPU="4.37908E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3.37908" LogicalOp="Sequence" NodeId="1" Parallel="false" PhysicalOp="Sequence" EstimatedTotalSubtreeCost="0.0367941">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<Sequence>
<RelOp AvgRowSize="14" EstimateCPU="5.02642E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Eager Spool" NodeId="2" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0117432">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Spool>
<RelOp AvgRowSize="14" EstimateCPU="2E-06" EstimateIO="0.02" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Update" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0232863">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Index="[PK_Foo]" IndexKind="Clustered" Storage="RowStore" />
<Object Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Index="[IXF_Foo_FooID_Code_Status]" Filtered="true" IndexKind="NonClustered" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[SandBox].[dbo].[Foo].[Code] = [Expr1002]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1002" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="18" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328428">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Column="Expr1002" />
<ColumnReference Column="Expr1010" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1010" />
<ScalarOperator ScalarString="[Expr1010]">
<Identifier>
<ColumnReference Column="Expr1010" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="18" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="5" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328428">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Column="Expr1002" />
<ColumnReference Column="Expr1010" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1010" />
<ScalarOperator ScalarString="CASE WHEN [Expr1009] THEN (0) ELSE (1) END">
<IF>
<Condition>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1009" />
</Identifier>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328418">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Column="Expr1002" />
<ColumnReference Column="Expr1009" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="'ABC'">
<Const ConstValue="'ABC'" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="12" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="7" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328408">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Column="Expr1009" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1009" />
<ScalarOperator ScalarString="CASE WHEN [SandBox].[dbo].[Foo].[Code] = 'ABC' THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="BINARY IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'ABC'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1500000">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</DefinedValue>
</DefinedValues>
<Object Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Index="[PK_Foo]" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[@FooID]">
<Identifier>
<ColumnReference Column="@FooID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[SandBox].[dbo].[Foo].[Status]&lt;'DELETED' OR [SandBox].[dbo].[Foo].[Status]&gt;'DELETED'">
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'DELETED'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'DELETED'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
</Spool>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="3.37908E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3.37908" LogicalOp="Update" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0250465">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualRowsRead="2" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[SandBox]" Schema="[dbo]" Table="[vw_IndexedView]" Index="[CIX]" IndexKind="ViewClustered" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[SandBox].[dbo].[vw_IndexedView].[Code] = [SandBox].[dbo].[Foo].[Code]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[vw_IndexedView]" Column="Code" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="14" EstimateCPU="1.41246E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3.37908" LogicalOp="Inner Join" NodeId="11" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0150431">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Alias="[x]" Column="FooDetailID" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
</OuterReferences>
<RelOp AvgRowSize="14" EstimateCPU="5.02642E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Eager Spool" NodeId="13" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0117432">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="Code" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Spool PrimaryNodeId="2" />
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.000160717" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3.37908" EstimatedRowsRead="3.37908" LogicalOp="Index Seek" NodeId="14" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00328572" TableCardinality="5000000">
<OutputList>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Alias="[x]" Column="FooDetailID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualRowsRead="2" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Alias="[x]" Column="FooDetailID" />
</DefinedValue>
</DefinedValues>
<Object Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Index="[IX_FooDetail_FooID]" Alias="[x]" IndexKind="NonClustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[FooDetail]" Alias="[x]" Column="FooID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[SandBox].[dbo].[Foo].[FooID]">
<Identifier>
<ColumnReference Database="[SandBox]" Schema="[dbo]" Table="[Foo]" Column="FooID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Update>
</RelOp>
</Sequence>
</RelOp>
<ParameterList>
<ColumnReference Column="@FooID" ParameterDataType="int" ParameterCompiledValue="(123456)" ParameterRuntimeValue="(123456)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Raw
Base examples.sql
USE SandBox;
GO
------------------------------------------------------------
-- Good plan - using max range_high_key value
------------------------------------------------------------
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
DECLARE @sql nvarchar(MAX) = N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID;';
DECLARE @FooID int;
SELECT @FooID = MAX(CONVERT(int, h.range_high_key))
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) h
WHERE s.[object_id] = OBJECT_ID('dbo.Foo')
AND s.[name] = 'IXF_Foo_FooID_Code_Status';
EXEC sp_executesql @stmt = @sql, @params = N'@FooID int', @FooID = @FooID;
GO
------------------------------------------------------------
------------------------------------------------------------
-- Bad plan - using max range_high_key value + N
------------------------------------------------------------
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
DECLARE @sql nvarchar(MAX) = N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID;';
DECLARE @FooID int;
SELECT @FooID = MAX(CONVERT(int, h.range_high_key)) + 300
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) h
WHERE s.[object_id] = OBJECT_ID('dbo.Foo')
AND s.[name] = 'IXF_Foo_FooID_Code_Status';
EXEC sp_executesql @stmt = @sql, @params = N'@FooID int', @FooID = @FooID;
GO
------------------------------------------------------------
------------------------------------------------------------
Raw
Debug optimizer.sql
-- Good
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC sp_executesql N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID OPTION(QUERYTRACEON 2363, QUERYTRACEON 3604);', N'@FooID int', @FooID = 123456;
GO
-- Bad
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC sp_executesql N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID OPTION(QUERYTRACEON 2363, QUERYTRACEON 3604);', N'@FooID int', @FooID = -1;
/*
If you run each and compare with a diff tool. You'll find multiple messages for the bad plan like this:
```
Calculator failed. Replanning.
Plan for computation:
CSelCalcGuessComparisonJoin x_cmpEq
Selectivity: 1
```
Where `Selectivity: 1` is indicating that due to the failure, it is resulting to a cardinality of 1, which is the entire table.
Googling `CSelCalcGuessComparisonJoin` is what eventually led me to this StackOverflow post:
https://stackoverflow.com/q/59492898/3474677
Which links back to this SQL Server bug for 2016/2017:
https://support.microsoft.com/en-us/topic/kb3192154-fe5cda6a-59ce-60fb-e0fd-56729a8c8fa6
All of the symptoms explained in the SO post match the symptoms of this issue in 2022.
*/
Raw
Optimizer debug - Bad.txt
Begin selectivity computation
Input tree:
LogOp_Select
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
ScaOp_Logical x_lopAnd
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].FooID
ScaOp_Identifier COL: @FooID
ScaOp_Comp x_cmpNe
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].Status
ScaOp_Const TI(varchar collate 872468488,Var,Trim,ML=7) XVAR(varchar,Owned,Value=Len,Data = (7,DELETED))
Plan for computation:
CSelCalcUniqueKeyFilter
Selectivity: 0.000000666667
Stats collection generated:
CStCollFilter(ID=6, CARD=1)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
End selectivity computation
Begin selectivity computation
Input tree:
LogOp_Select
CStCollProject(ID=12, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
ScaOp_Logical x_lopAnd
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].FooID
ScaOp_Identifier COL: @FooID
ScaOp_Comp x_cmpNe
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].Status
ScaOp_Const TI(varchar collate 872468488,Var,Trim,ML=7) XVAR(varchar,Owned,Value=Len,Data = (7,DELETED))
Plan for computation:
CSelCalcUniqueKeyFilter
Selectivity: 0.000000666667
Stats collection generated:
CStCollFilter(ID=13, CARD=1)
CStCollProject(ID=12, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
End selectivity computation
Begin selectivity computation
Input tree:
LogOp_Join
CStCollBaseTable(ID=4, CARD=5000000.000 TBL: dbo.FooDetail AS TBL: x)
CStCollRemap(ID=29, CARD=1)
CStCollUpdate(ID=28, CARD=1)
Stats for delta stream:
CStCollRemap(ID=25, CARD=1)
CStCollProject(ID=24, CARD=1)
CStCollProject(ID=14, CARD=1)
CStCollFilter(ID=13, CARD=1)
CStCollProject(ID=12, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
Stats for write cursor (old values):
CStCollRemap(ID=27, CARD=1)
CStCollFudge(ID=26, CARD=1)
CStCollBaseTable(ID=2, CARD=1500000.000 TBL: [SandBox].[dbo].[Foo])
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [y].FooID
ScaOp_Identifier QCOL: [x].FooID
Plan for computation:
CSelCalcExpressionComparedToExpression( QCOL: [x].FooID x_cmpEq QCOL: [y].FooID )
Loaded histogram for column QCOL: [x].FooID from stats with id 2
Loaded histogram for column QCOL: [SandBox].[dbo].[Foo].FooID from stats with id 2
Calculator failed. Replanning.
Plan for computation:
CSelCalcGuessComparisonJoin x_cmpEq
Selectivity: 1
Stats collection generated:
CStCollJoin(ID=30, CARD=5000000.000 x_jtInner)
CStCollBaseTable(ID=4, CARD=5000000.000 TBL: dbo.FooDetail AS TBL: x)
CStCollRemap(ID=29, CARD=1)
CStCollUpdate(ID=28, CARD=1)
Stats for delta stream:
CStCollRemap(ID=25, CARD=1)
CStCollProject(ID=24, CARD=1)
CStCollProject(ID=14, CARD=1)
CStCollFilter(ID=13, CARD=1)
CStCollProject(ID=12, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
Stats for write cursor (old values):
CStCollRemap(ID=27, CARD=1)
CStCollFudge(ID=26, CARD=1)
CStCollBaseTable(ID=2, CARD=1500000.000 TBL: [SandBox].[dbo].[Foo])
End selectivity computation
Loaded histogram for column QCOL: [SandBox].[dbo].[Foo].FooID from stats with id 1
Begin selectivity computation
Input tree:
LogOp_Select
CStCollProject(ID=37, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
ScaOp_Logical x_lopAnd
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].FooID
ScaOp_Identifier COL: @FooID
ScaOp_Comp x_cmpNe
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].Status
ScaOp_Const TI(varchar collate 872468488,Var,Trim,ML=7) XVAR(varchar,Owned,Value=Len,Data = (7,DELETED))
Plan for computation:
CSelCalcUniqueKeyFilter
Selectivity: 0.000000666667
Stats collection generated:
CStCollFilter(ID=38, CARD=1)
CStCollProject(ID=37, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
End selectivity computation
Begin selectivity computation
Input tree:
LogOp_Join
CStCollBaseTable(ID=4, CARD=5000000.000 TBL: dbo.FooDetail AS TBL: x)
CStCollRemap(ID=45, CARD=1)
CStCollUpdate(ID=44, CARD=1)
Stats for delta stream:
CStCollRemap(ID=41, CARD=1)
CStCollProject(ID=40, CARD=1)
CStCollProject(ID=39, CARD=1)
CStCollFilter(ID=38, CARD=1)
CStCollProject(ID=37, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
Stats for write cursor (old values):
CStCollRemap(ID=43, CARD=1)
CStCollFudge(ID=42, CARD=1)
CStCollBaseTable(ID=2, CARD=1500000.000 TBL: [SandBox].[dbo].[Foo])
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [y].FooID
ScaOp_Identifier QCOL: [x].FooID
Plan for computation:
CSelCalcExpressionComparedToExpression( QCOL: [x].FooID x_cmpEq QCOL: [y].FooID )
Calculator failed. Replanning.
Plan for computation:
CSelCalcGuessComparisonJoin x_cmpEq
Selectivity: 1
Stats collection generated:
CStCollJoin(ID=46, CARD=5000000.000 x_jtInner)
CStCollBaseTable(ID=4, CARD=5000000.000 TBL: dbo.FooDetail AS TBL: x)
CStCollRemap(ID=45, CARD=1)
CStCollUpdate(ID=44, CARD=1)
Stats for delta stream:
CStCollRemap(ID=41, CARD=1)
CStCollProject(ID=40, CARD=1)
CStCollProject(ID=39, CARD=1)
CStCollFilter(ID=38, CARD=1)
CStCollProject(ID=37, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
Stats for write cursor (old values):
CStCollRemap(ID=43, CARD=1)
CStCollFudge(ID=42, CARD=1)
CStCollBaseTable(ID=2, CARD=1500000.000 TBL: [SandBox].[dbo].[Foo])
End selectivity computation
Begin selectivity computation
Input tree:
LogOp_Select
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].FooID
ScaOp_Identifier COL: @FooID
Plan for computation:
CSelCalcUniqueKeyFilter
Calculator failed. Replanning.
Plan for computation:
CSelCalcPointPredUniqueKeyFilter
Selectivity: 0.000000666667
Stats collection generated:
CStCollFilter(ID=53, CARD=1)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
End selectivity computation
Estimating distinct count in utility function
Input stats collection:
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
Columns to distinct on:QCOL: [SandBox].[dbo].[Foo].FooID
Plan for computation:
CDVCPlanUniqueKey
Result of computation: 1500000.000
Estimating distinct count in utility function
Input stats collection:
CStCollBaseTable(ID=4, CARD=5000000.000 TBL: dbo.FooDetail AS TBL: x)
Columns to distinct on:QCOL: [x].FooID
Loaded histogram for column QCOL: [x].FooDetailID from stats with id 1
Ignoring multi-column stats with id 2 because it appears to be unscaled:
distinct count(QCOL: [x].FooID QCOL: [x].FooDetailID) = 4993220.000
distinct count(QCOL: [x].FooDetailID) = 4998620.000
Plan for computation:
CDVCPlanLeaf
0 Multi-Column Stats, 1 Single-Column Stats, 0 Guesses
Using ambient cardinality 5000000.000 to combine distinct counts:
1480070.000
Combined distinct count: 1480070.000
Result of computation: 1480070.000
Estimating distinct count in utility function
Input stats collection:
CStCollRemap(ID=45, CARD=1)
CStCollUpdate(ID=44, CARD=1)
Stats for delta stream:
CStCollRemap(ID=41, CARD=1)
CStCollProject(ID=40, CARD=1)
CStCollProject(ID=39, CARD=1)
CStCollFilter(ID=38, CARD=1)
CStCollProject(ID=37, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
Stats for write cursor (old values):
CStCollRemap(ID=43, CARD=1)
CStCollFudge(ID=42, CARD=1)
CStCollBaseTable(ID=2, CARD=1500000.000 TBL: [SandBox].[dbo].[Foo])
Columns to distinct on:QCOL: [y].FooID
Plan for computation:
CDVCPlanTrivial
Result of computation: 1
Raw
Optimizer debug - Good.txt
Begin selectivity computation
Input tree:
LogOp_Select
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
ScaOp_Logical x_lopAnd
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].FooID
ScaOp_Identifier COL: @FooID
ScaOp_Comp x_cmpNe
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].Status
ScaOp_Const TI(varchar collate 872468488,Var,Trim,ML=7) XVAR(varchar,Owned,Value=Len,Data = (7,DELETED))
Plan for computation:
CSelCalcUniqueKeyFilter
Selectivity: 0.000000666667
Stats collection generated:
CStCollFilter(ID=6, CARD=1)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
End selectivity computation
Begin selectivity computation
Input tree:
LogOp_Select
CStCollProject(ID=12, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
ScaOp_Logical x_lopAnd
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].FooID
ScaOp_Identifier COL: @FooID
ScaOp_Comp x_cmpNe
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].Status
ScaOp_Const TI(varchar collate 872468488,Var,Trim,ML=7) XVAR(varchar,Owned,Value=Len,Data = (7,DELETED))
Plan for computation:
CSelCalcUniqueKeyFilter
Selectivity: 0.000000666667
Stats collection generated:
CStCollFilter(ID=13, CARD=1)
CStCollProject(ID=12, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
End selectivity computation
Begin selectivity computation
Input tree:
LogOp_Join
CStCollBaseTable(ID=4, CARD=5000000.000 TBL: dbo.FooDetail AS TBL: x)
CStCollRemap(ID=29, CARD=1)
CStCollUpdate(ID=28, CARD=1)
Stats for delta stream:
CStCollRemap(ID=25, CARD=1)
CStCollProject(ID=24, CARD=1)
CStCollProject(ID=14, CARD=1)
CStCollFilter(ID=13, CARD=1)
CStCollProject(ID=12, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
Stats for write cursor (old values):
CStCollRemap(ID=27, CARD=1)
CStCollFudge(ID=26, CARD=1)
CStCollBaseTable(ID=2, CARD=1500000.000 TBL: [SandBox].[dbo].[Foo])
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [y].FooID
ScaOp_Identifier QCOL: [x].FooID
Plan for computation:
CSelCalcExpressionComparedToExpression( QCOL: [x].FooID x_cmpEq QCOL: [y].FooID )
Loaded histogram for column QCOL: [x].FooID from stats with id 2
Loaded histogram for column QCOL: [SandBox].[dbo].[Foo].FooID from stats with id 2
Selectivity: 0.000000675817
Stats collection generated:
CStCollJoin(ID=30, CARD=3.37908 x_jtInner)
CStCollBaseTable(ID=4, CARD=5000000.000 TBL: dbo.FooDetail AS TBL: x)
CStCollRemap(ID=29, CARD=1)
CStCollUpdate(ID=28, CARD=1)
Stats for delta stream:
CStCollRemap(ID=25, CARD=1)
CStCollProject(ID=24, CARD=1)
CStCollProject(ID=14, CARD=1)
CStCollFilter(ID=13, CARD=1)
CStCollProject(ID=12, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
Stats for write cursor (old values):
CStCollRemap(ID=27, CARD=1)
CStCollFudge(ID=26, CARD=1)
CStCollBaseTable(ID=2, CARD=1500000.000 TBL: [SandBox].[dbo].[Foo])
End selectivity computation
Loaded histogram for column QCOL: [SandBox].[dbo].[Foo].FooID from stats with id 1
Begin selectivity computation
Input tree:
LogOp_Select
CStCollProject(ID=37, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
ScaOp_Logical x_lopAnd
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].FooID
ScaOp_Identifier COL: @FooID
ScaOp_Comp x_cmpNe
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].Status
ScaOp_Const TI(varchar collate 872468488,Var,Trim,ML=7) XVAR(varchar,Owned,Value=Len,Data = (7,DELETED))
Plan for computation:
CSelCalcUniqueKeyFilter
Selectivity: 0.000000666667
Stats collection generated:
CStCollFilter(ID=38, CARD=1)
CStCollProject(ID=37, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
End selectivity computation
Begin selectivity computation
Input tree:
LogOp_Join
CStCollBaseTable(ID=4, CARD=5000000.000 TBL: dbo.FooDetail AS TBL: x)
CStCollRemap(ID=45, CARD=1)
CStCollUpdate(ID=44, CARD=1)
Stats for delta stream:
CStCollRemap(ID=41, CARD=1)
CStCollProject(ID=40, CARD=1)
CStCollProject(ID=39, CARD=1)
CStCollFilter(ID=38, CARD=1)
CStCollProject(ID=37, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
Stats for write cursor (old values):
CStCollRemap(ID=43, CARD=1)
CStCollFudge(ID=42, CARD=1)
CStCollBaseTable(ID=2, CARD=1500000.000 TBL: [SandBox].[dbo].[Foo])
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [y].FooID
ScaOp_Identifier QCOL: [x].FooID
Plan for computation:
CSelCalcExpressionComparedToExpression( QCOL: [x].FooID x_cmpEq QCOL: [y].FooID )
Selectivity: 0.000000675817
Stats collection generated:
CStCollJoin(ID=46, CARD=3.37908 x_jtInner)
CStCollBaseTable(ID=4, CARD=5000000.000 TBL: dbo.FooDetail AS TBL: x)
CStCollRemap(ID=45, CARD=1)
CStCollUpdate(ID=44, CARD=1)
Stats for delta stream:
CStCollRemap(ID=41, CARD=1)
CStCollProject(ID=40, CARD=1)
CStCollProject(ID=39, CARD=1)
CStCollFilter(ID=38, CARD=1)
CStCollProject(ID=37, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
Stats for write cursor (old values):
CStCollRemap(ID=43, CARD=1)
CStCollFudge(ID=42, CARD=1)
CStCollBaseTable(ID=2, CARD=1500000.000 TBL: [SandBox].[dbo].[Foo])
End selectivity computation
Begin selectivity computation
Input tree:
LogOp_Select
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [SandBox].[dbo].[Foo].FooID
ScaOp_Identifier COL: @FooID
Plan for computation:
CSelCalcUniqueKeyFilter
Selectivity: 0.000000666667
Stats collection generated:
CStCollFilter(ID=53, CARD=1)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
End selectivity computation
Estimating distinct count in utility function
Input stats collection:
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
Columns to distinct on:QCOL: [SandBox].[dbo].[Foo].FooID
Plan for computation:
CDVCPlanUniqueKey
Result of computation: 1500000.000
Estimating distinct count in utility function
Input stats collection:
CStCollBaseTable(ID=4, CARD=5000000.000 TBL: dbo.FooDetail AS TBL: x)
Columns to distinct on:QCOL: [x].FooID
Loaded histogram for column QCOL: [x].FooDetailID from stats with id 1
Ignoring multi-column stats with id 2 because it appears to be unscaled:
distinct count(QCOL: [x].FooID QCOL: [x].FooDetailID) = 4993220.000
distinct count(QCOL: [x].FooDetailID) = 4998620.000
Plan for computation:
CDVCPlanLeaf
0 Multi-Column Stats, 1 Single-Column Stats, 0 Guesses
Using ambient cardinality 5000000.000 to combine distinct counts:
1480070.000
Combined distinct count: 1480070.000
Result of computation: 1480070.000
Estimating distinct count in utility function
Input stats collection:
CStCollRemap(ID=45, CARD=1)
CStCollUpdate(ID=44, CARD=1)
Stats for delta stream:
CStCollRemap(ID=41, CARD=1)
CStCollProject(ID=40, CARD=1)
CStCollProject(ID=39, CARD=1)
CStCollFilter(ID=38, CARD=1)
CStCollProject(ID=37, CARD=1500000.000)
CStCollBaseTable(ID=1, CARD=1500000.000 TBL: dbo.Foo)
Stats for write cursor (old values):
CStCollRemap(ID=43, CARD=1)
CStCollFudge(ID=42, CARD=1)
CStCollBaseTable(ID=2, CARD=1500000.000 TBL: [SandBox].[dbo].[Foo])
Columns to distinct on:QCOL: [y].FooID
Plan for computation:
CDVCPlanTrivial
Result of computation: 1
Raw
Setup Environment.sql
--USE tempdb; -- Change to database of choice where it's safe to create and drop objects and clear plan cache
USE Sandbox;
------------------------------------------------------------
------------------------------------------------------------
-- This should be off. The issue only occurs on the new CE
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF; -- This is off by default, but setting anyway
/* Because we are working on SQL Server 2022 (compat level 160), that means
any query optimizer hotfixes that have been released prior to compat 160
should be "on-by-default", even if this config setting is disabled.
See: https://support.microsoft.com/en-us/topic/kb974006-cd3ebf5c-465c-6dd8-7178-d41fdddccc28
So, because of this servicing model for QUERY_OPTIMIZER_HOTFIXES (trace flag 4199)
I am leaving this disabled at the database level, but will test at the query level.
*/
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF; -- This is off by default, but setting anyway
------------------------------------------------------------
------------------------------------------------------------
DROP VIEW IF EXISTS dbo.vw_IndexedView;
DROP TABLE IF EXISTS dbo.FooDetail;
DROP TABLE IF EXISTS dbo.Foo;
------------------------------------------------------------
------------------------------------------------------------
CREATE TABLE dbo.Foo (
FooID int NOT NULL IDENTITY CONSTRAINT PK_Foo PRIMARY KEY,
[Status] varchar(15) NULL,
Code char(3) NULL,
SomethingElse varchar(20) NULL,
);
CREATE NONCLUSTERED INDEX IXF_Foo_FooID_Code_Status ON dbo.Foo (FooID, Code, [Status]) WHERE [Status] <> 'DELETED';
CREATE NONCLUSTERED INDEX IXF_Foo_FooID_SomethingElse ON dbo.Foo (FooID, SomethingElse) WHERE [Status] <> 'DELETED';
------------------------------------------------------------
------------------------------------------------------------
CREATE TABLE dbo.FooDetail (
FooDetailID int NOT NULL IDENTITY CONSTRAINT PK_FooDetail PRIMARY KEY,
FooID int NOT NULL,
);
CREATE NONCLUSTERED INDEX IX_FooDetail_FooID ON dbo.FooDetail (FooID);
------------------------------------------------------------
------------------------------------------------------------
INSERT dbo.Foo ([Status], Code, SomethingElse)
SELECT CASE
WHEN r.rnd > 0.00 AND r.rnd <= 0.10 THEN 'DELETED'
WHEN r.rnd > 0.10 AND r.rnd <= 0.20 THEN 'Status A'
WHEN r.rnd > 0.20 AND r.rnd <= 0.60 THEN 'Status B'
WHEN r.rnd > 0.60 AND r.rnd <= 0.80 THEN 'Status C'
WHEN r.rnd > 0.80 AND r.rnd <= 0.98 THEN 'Status D'
WHEN r.rnd > 0.98 AND r.rnd <= 0.99 THEN 'Status E'
WHEN r.rnd > 0.99 AND r.rnd <= 1.00 THEN 'Status F'
ELSE NULL
END
, CONCAT(CHAR(64 + (ABS(CHECKSUM(NEWID())) % 6) + 1), CHAR(64 + (ABS(CHECKSUM(NEWID())) % 6) + 1), CHAR(64 + (ABS(CHECKSUM(NEWID())) % 6) + 1))
, LEFT(NEWID(),20)
FROM GENERATE_SERIES(1,1500000) x
CROSS APPLY (SELECT rnd = RAND(CHECKSUM(NEWID()))) r;
------------------------------------------------------------
------------------------------------------------------------
DECLARE @rc bigint = CONVERT(bigint, OBJECTPROPERTYEX(OBJECT_ID('dbo.Foo'), 'Cardinality'));
INSERT dbo.FooDetail (FooID)
SELECT CEILING(RAND(CHECKSUM(NEWID())) * @rc)
FROM GENERATE_SERIES(1,5000000);
------------------------------------------------------------
------------------------------------------------------------
GO
CREATE OR ALTER VIEW dbo.vw_IndexedView
WITH SCHEMABINDING
AS
SELECT x.FooDetailID, y.FooID, y.Code
FROM dbo.FooDetail x
JOIN dbo.Foo y ON y.FooID = x.FooID;
GO
CREATE UNIQUE CLUSTERED INDEX CIX ON dbo.vw_IndexedView (FooDetailID);
GO
------------------------------------------------------------
------------------------------------------------------------
-- Create a view for soft-delete logic
GO
CREATE OR ALTER VIEW dbo.vw_FooFiltered
AS
SELECT * FROM dbo.Foo WHERE [Status] <> 'DELETED';
GO
------------------------------------------------------------
------------------------------------------------------------
/* New indexes are created with fullscan stats. We need to refresh
them so that they are instead using sample stats like they would
in a production environment. This issue is directly impacted by
stats and their sample vs fullscan status. */
UPDATE STATISTICS dbo.vw_IndexedView;
UPDATE STATISTICS dbo.Foo;
UPDATE STATISTICS dbo.FooDetail;
------------------------------------------------------------
------------------------------------------------------------
Raw
Solution attempts - Bad.sql
------------------------------------------------------------
-- Recompiling query/dynamic SQL
------------------------------------------------------------
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC sp_executesql N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID OPTION(RECOMPILE);', N'@FooID int', @FooID = -1;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC sp_executesql N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID;', N'@FooID int', @FooID = -1 WITH RECOMPILE;
------------------------------------------------------------
GO
------------------------------------------------------------
-- Rebuild stats with lower than FULLSCAN
------------------------------------------------------------
/* This has worked in some test cases, but the cut off % varies from database to database.
Some required up to 90% whereas others were as low as 5% */
------------------------------------------------------------
GO
------------------------------------------------------------
-- Tested various trace flgs, DB scoped configs and query hints
------------------------------------------------------------
/*
---- CE / Selectivity based settings ----
---- See: https://support.microsoft.com/en-us/topic/kb2952101-c14d67e6-cf24-c5e3-2a73-ccf4fbfe3f67
---- See: https://techcommunity.microsoft.com/blog/azuresqlblog/cardinality-estimation-feedback-explained-by-kate-smith-akatesmith/4197930
╭─────────┬─────────────────────────────────────────────┬─────────────────────────────────────────────────┬─────────────────────────────────────────────────────────────────────────────╮
│ flag_id │ db_config_name │ query_hint_name │ description │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ │ │ ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES │ Generates a plan using most to least selectivity when estimating AND │
│ │ │ │ predicates for filters to account for partial correlation. This hint name │
│ │ │ │ is the default behavior of the cardinality estimation model of SQL Server │
│ │ │ │ 2014 (12.x) and later versions. │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 9472 │ │ ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES │ Generates a plan using maximum selectivity when estimating AND predicates │
│ │ │ │ for filters to account for full independence. This hint name is the default │
│ │ │ │ behavior of the cardinality estimation model of SQL Server 2012 (11.x) and │
│ │ │ │ earlier versions, and equivalent to Trace Flag 9472 when used with │
│ │ │ │ cardinality estimation model of SQL Server 2014 (12.x) and later versions. │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 9471 │ │ ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES │ Causes SQL Server to generate a plan using minimum selectivity for │
│ │ │ │ single-table filters, under the query optimizer cardinality estimation │
│ │ │ │ model of SQL Server 2014 (12.x) and later versions. │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 9440 │ │ │ Disables the fix for bug 2112485 and bug 2636294. The bug fix doesn't apply │
│ │ │ │ when using the legacy Cardinality Estimation (CE) model. When a database │
│ │ │ │ uses the default CE model, outer join cardinality estimates might increase │
│ │ │ │ higher than the cardinality of the tables involved in the join when the │
│ │ │ │ join predicates consist of primary keys from the tables (for example, │
│ │ │ │ primary key to foreign key joins). A cap is applied that will limit the │
│ │ │ │ amount of cardinality overestimation similar to the overestimation limit │
│ │ │ │ that exists in the legacy CE for this scenario. │
╰─────────┴─────────────────────────────────────────────┴─────────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────────╯
---- Optimizer based settings ----
╭─────────┬─────────────────────────────────────────────┬─────────────────────────────────────────────────┬─────────────────────────────────────────────────────────────────────────────╮
│ flag_id │ db_config_name │ query_hint_name │ description │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 2340 │ │ DISABLE_OPTIMIZED_NESTED_LOOP │ Causes SQL Server not to use a sort operation (batch sort) for optimized │
│ │ │ │ Nested Loops joins when generating a plan. By default, SQL Server can use │
│ │ │ │ an optimized Nested Loops join instead of a full scan or a Nested Loops │
│ │ │ │ join with an explicit Sort, when the Query Optimizer concludes that a sort │
│ │ │ │ is most likely not required, but still a possibility if the cardinality or │
│ │ │ │ cost estimates are incorrect. For more information, see High CPU or memory │
│ │ │ │ grants may occur with queries that use optimized nested loop or batch sort. │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 8790 │ │ │ Causes SQL Server to use a wide query plan when executing an UPDATE │
│ │ │ │ statement to update indexes in a table. When you do an UPDATE against a │
│ │ │ │ clustered index column, SQL Server updates not only the clustered index │
│ │ │ │ itself, but also all the nonclustered indexes because the nonclustered │
│ │ │ │ indexes contain the cluster index key. To optimize performance and reduce │
│ │ │ │ random I/O SQL Server might choose to sort all nonclustered index data in │
│ │ │ │ memory, and then update all indexes by the order. This is known as a wide │
│ │ │ │ plan, also called Per-Index Update, and can be forced using this trace │
│ │ │ │ flag. │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 4138 │ │ DISABLE_OPTIMIZER_ROWGOAL │ Causes SQL Server to generate a plan that doesn't use row goal adjustments │
│ │ │ │ with queries that contain TOP, OPTION (FAST N), IN, or EXISTS keywords. For │
│ │ │ │ more information, see KB2667211. │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 2335 │ │ │ Causes SQL Server to assume a fixed amount of memory is available during │
│ │ │ │ query optimization, for a scenario where the max server memory server │
│ │ │ │ configuration is set too high, and causes SQL Server to generate an │
│ │ │ │ inefficient plan for a specific query. It doesn't limit the memory SQL │
│ │ │ │ Server grants to execute the query. The memory configured for SQL Server is │
│ │ │ │ still used by data cache, query execution, and other consumers. │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 4199 │ QUERY_OPTIMIZER_HOTFIXES = ON │ ENABLE_QUERY_OPTIMIZER_HOTFIXES │ Enables Query Optimizer (QO) fixes released in SQL Server Cumulative │
│ │ │ │ Updates and Service Packs. │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 10054 │ │ │ Disables the SQL Server Query Optimizer rule that decorrelates subqueries │
│ │ │ │ in OR predicates into outer joins. │
╰─────────┴─────────────────────────────────────────────┴─────────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────────╯
---- Histogram based settings ----
---- Specifically calling the following flags out as not working since they are very specific to this issue, which is an ascending key problem
---- Note: It is surprising that 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS' did not work since it is closely related to this issue, which is an ascending key problem
╭─────────┬─────────────────────────────────────────────┬─────────────────────────────────────────────────┬─────────────────────────────────────────────────────────────────────────────╮
│ flag_id │ db_config_name │ query_hint_name │ description │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 4139 │ │ ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS │ Enable automatically generated quick statistics (histogram amendment) │
│ │ │ │ regardless of key column status. If Trace Flag 4139 is set, regardless of │
│ │ │ │ the leading statistics column status (ascending, descending, or │
│ │ │ │ stationary), the histogram used to estimate cardinality is adjusted at │
│ │ │ │ query compile time. For more information, see KB2952101. │
╰─────────┴─────────────────────────────────────────────┴─────────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────────╯
---- Misc ----
╭─────────┬─────────────────────────────────────────────┬─────────────────────────────────────────────────┬─────────────────────────────────────────────────────────────────────────────╮
│ flag_id │ db_config_name │ query_hint_name │ description │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ │ │ DISALLOW_BATCH_MODE │ Disables batch mode execution. │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 2301 │ │ │ Enable advanced decision support optimizations that are specific to │
│ │ │ │ decision support queries. This option applies to decision support │
│ │ │ │ processing of large data sets. │
╰─────────┴─────────────────────────────────────────────┴─────────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────────╯
*/
------------------------------------------------------------
GO
------------------------------------------------------------
-- Creating other covering indexes
------------------------------------------------------------
/* Each of these were tested individually, recomputing stats with sample after creating each index
I also tested removing all non-clustered indexes - still no luck, even though that wouldn't be
a production solution, it at least confirms the non-clustered indexes themselves are not
causing the issue.
CREATE NONCLUSTERED INDEX IX_Test ON dbo.Foo (FooID);
CREATE NONCLUSTERED INDEX IX_Test ON dbo.Foo (FooID) WHERE [Status] <> 'DELETED';
CREATE NONCLUSTERED INDEX IX_Test ON dbo.Foo (FooID) INCLUDE (Code);
CREATE NONCLUSTERED INDEX IX_Test ON dbo.Foo (FooID) INCLUDE (Code) WHERE [Status] <> 'DELETED';
CREATE NONCLUSTERED INDEX IX_Test ON dbo.Foo (FooID, [Status]);
CREATE NONCLUSTERED INDEX IX_Test ON dbo.Foo (FooID, [Status]) WHERE [Status] <> 'DELETED';
CREATE NONCLUSTERED INDEX IX_Test ON dbo.Foo (FooID, [Status]) INCLUDE (Code);
CREATE NONCLUSTERED INDEX IX_Test ON dbo.Foo (FooID, [Status]) INCLUDE (Code) WHERE [Status] <> 'DELETED';
CREATE NONCLUSTERED INDEX IX_Test ON dbo.Foo ([Status], FooID);
CREATE NONCLUSTERED INDEX IX_Test ON dbo.Foo ([Status], FooID) WHERE [Status] <> 'DELETED';
CREATE NONCLUSTERED INDEX IX_Test ON dbo.Foo ([Status], FooID) INCLUDE (Code);
CREATE NONCLUSTERED INDEX IX_Test ON dbo.Foo ([Status], FooID) INCLUDE (Code) WHERE [Status] <> 'DELETED';
CREATE NONCLUSTERED INDEX IX_Test ON dbo.FooDetail (FooID);
CREATE NONCLUSTERED INDEX IX_TESTING ON dbo.vw_IndexedView (FooID)
/* These actually made it worse because they contain the changing column
so a maintenance update is needed and the bad stats make it even worse */
CREATE NONCLUSTERED INDEX IX_TESTING ON dbo.vw_IndexedView (FooID, Code)
CREATE NONCLUSTERED INDEX IX_TESTING ON dbo.vw_IndexedView (Code, FooID)
*/
------------------------------------------------------------
GO
------------------------------------------------------------
-- Creating light weight full scan stat
------------------------------------------------------------
/* Each of thse were tested individually
CREATE STATISTICS ST_TESTING1 ON dbo.Foo (FooID) WITH FULLSCAN
CREATE STATISTICS ST_TESTING2 ON dbo.Foo ([Status]) WITH FULLSCAN
CREATE STATISTICS ST_TESTING3 ON dbo.Foo (Code) WITH FULLSCAN
CREATE STATISTICS ST_TESTING4 ON dbo.Foo (SomethingElse) WITH FULLSCAN
CREATE STATISTICS ST_TESTING5 ON dbo.FooDetail (FooDetailID) WITH FULLSCAN
CREATE STATISTICS ST_TESTING6 ON dbo.FooDetail (FooID) WITH FULLSCAN
CREATE STATISTICS ST_TESTING7 ON dbo.vw_IndexedView (FooDetailID) WITH FULLSCAN
CREATE STATISTICS ST_TESTING8 ON dbo.vw_IndexedView (FooID) WITH FULLSCAN
CREATE STATISTICS ST_TESTING9 ON dbo.vw_IndexedView (Code) WITH FULLSCAN
*/
------------------------------------------------------------
------------------------------------------------------------
Raw
Solution attempts - Good.sql
------------------------------------------------------------
-- Rebuild all filtered stats with fullscan
------------------------------------------------------------
UPDATE STATISTICS dbo.Foo (IXF_Foo_FooID_Code_Status, IXF_Foo_FooID_SomethingElse) WITH FULLSCAN;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC sp_executesql N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID;', N'@FooID int', @FooID = -1;
GO
UPDATE STATISTICS dbo.Foo;
------------------------------------------------------------
GO
------------------------------------------------------------
-- Optimize for unknown
------------------------------------------------------------
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC sp_executesql N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID OPTION(OPTIMIZE FOR UNKNOWN);', N'@FooID int', @FooID = -1;
------------------------------------------------------------
GO
------------------------------------------------------------
-- Optimize for parameter unknown
------------------------------------------------------------
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC sp_executesql N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID OPTION(OPTIMIZE FOR (@FooID UNKNOWN));', N'@FooID int', @FooID = -1;
------------------------------------------------------------
GO
------------------------------------------------------------
-- Old school parameter sniffing issue "fix"
------------------------------------------------------------
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC sp_executesql N'DECLARE @FooID2 int = @FooID; UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID2;', N'@FooID int', @FooID = -1;
------------------------------------------------------------
GO
------------------------------------------------------------
-- Rebuild filtered indexes as non-filtered, rebuild PK stat with FULLSCAN
------------------------------------------------------------
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
BEGIN TRAN; -- Using transaction for easier rollback
GO
DROP INDEX IF EXISTS IXF_Foo_FooID_Code_Status ON dbo.Foo;
DROP INDEX IF EXISTS IXF_Foo_FooID_SomethingElse ON dbo.Foo;
GO
CREATE NONCLUSTERED INDEX IX_Foo_FooID_Code_Status ON dbo.Foo (FooID, Code, [Status]);
CREATE NONCLUSTERED INDEX IX_Foo_FooID_SomethingElse ON dbo.Foo (FooID, SomethingElse);
GO
UPDATE STATISTICS dbo.Foo; -- rebuild all indexes with sample
UPDATE STATISTICS dbo.Foo (PK_Foo) WITH FULLSCAN;
GO
EXEC sp_executesql N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID;', N'@FooID int', @FooID = -1;
GO
ROLLBACK;
------------------------------------------------------------
GO
------------------------------------------------------------
-- Query hint based solutions
------------------------------------------------------------
/*
╭─────────┬─────────────────────────────────────────────┬─────────────────────────────────────────────────┬─────────────────────────────────────────────────────────────────────────────╮
│ flag_id │ db_config_name │ query_hint_name │ description │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 9476 │ │ ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS │ Causes SQL Server to generate a plan using the Simple Containment │
│ │ │ │ assumption instead of the default Base Containment assumption, under the │
│ │ │ │ query optimizer cardinality estimation model of SQL Server 2014 (12.x) and │
│ │ │ │ later versions. For more information, see Join containment assumption in │
│ │ │ │ the New Cardinality Estimator degrades query performance. │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 4136 │ PARAMETER_SNIFFING = OFF │ DISABLE_PARAMETER_SNIFFING │ Disables parameter sniffing unless OPTION(RECOMPILE), WITH RECOMPILE, or │
│ │ │ │ OPTIMIZE FOR <value> is used. For more information, see KB980653. │
├─────────┼─────────────────────────────────────────────┼─────────────────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 9481 │ LEGACY_CARDINALITY_ESTIMATION = ON │ FORCE_LEGACY_CARDINALITY_ESTIMATION │ Sets the Query Optimizer cardinality estimation (CE) model to SQL Server │
│ │ │ │ 2012 (11.x) and earlier (version 70), irrespective of the compatibility │
│ │ │ │ level of the database. For more information, see Query hints. │
╰─────────┴─────────────────────────────────────────────┴─────────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────────╯
*/
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC sp_executesql N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID OPTION(USE HINT (''ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS''));' , N'@FooID int', @FooID = -1;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC sp_executesql N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID OPTION(USE HINT (''DISABLE_PARAMETER_SNIFFING''));' , N'@FooID int', @FooID = -1;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC sp_executesql N'UPDATE dbo.vw_FooFiltered SET Code = ''ABC'' WHERE FooID = @FooID OPTION(USE HINT (''FORCE_LEGACY_CARDINALITY_ESTIMATION''));' , N'@FooID int', @FooID = -1;
------------------------------------------------------------
------------------------------------------------------------
@chadbaldwin
Copy link
Author

chadbaldwin commented Sep 11, 2025 •
edited
Loading

Good plan on left, bad plan on right:

image

You can see where the calculator failure results in a selectivity of 1, which then impacts the cardinality to go from ~3.5 rows to 5,000,000 rows, which is the entire table.

@chadbaldwin
Copy link
Author

chadbaldwin commented Sep 18, 2025 •
edited
Loading

Useful PowerShell snippet to cleanup line breaks and scientific notation values in query plans and optimizer debugging exports:

(gcb -Raw) `
    -replace '\d(\.\d+)?e[-+]\d\d',
             { ([decimal]$_.Value).ToString('0.000###########') } `
    -replace "[`n`r]+",
             "`r`n" | scb

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment