Skip to content

Instantly share code, notes, and snippets.

@mjswart

mjswart/Weird Estimates.sqlplan Secret

Created Aug 17, 2018
Embed
What would you like to do?
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementId="1" StatementText="&#xD;&#xA;DECLARE @NumPoints NUMERIC(19, 9)&#xD;&#xA;DECLARE @Page INT&#xD;&#xA;DECLARE @RandomSectionId BIGINT = (182623057) ;&#xD;&#xA;" StatementType="ASSIGN" RetrievedFromCache="false" />
</Statements>
<Statements>
<StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0229073" StatementText="declare @CollectionId bigint;&#xD;&#xA;&#xD;&#xA; SELECT TOP 1 QuestionPoints, Page&#xD;&#xA; FROM QO_SECTIONS QS&#xD;&#xA; INNER JOIN QO&#xD;&#xA; ON QO.CollectionId = QS.CollectionId&#xD;&#xA; AND ( QO.ObjectId = QS.SectionId &#xD;&#xA; OR QO.ParentObjectId = QS.SectionId )&#xD;&#xA; WHERE SectionId = @RandomSectionId&#xD;&#xA; ORDER BY Page DESC&#xD;&#xA; OPTION (QUERYTRACEON 9481)" StatementType="SELECT" QueryHash="0x3383CBAE5E5371BC" QueryPlanHash="0x7F0690C157A83B5A" RetrievedFromCache="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="32" CompileTime="8" CompileCPU="8" CompileMemory="536">
<MemoryGrantInfo SerialRequiredMemory="16" SerialDesiredMemory="24" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="768000" EstimatedPagesCached="1536000" EstimatedAvailableDegreeOfParallelism="8" />
<RelOp AvgRowSize="20" EstimateCPU="0.00010002" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0229073">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<TopSort Distinct="false" Rows="1">
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="20" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.011546">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
</OuterReferences>
<RelOp AvgRowSize="32" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="Uniq1001" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
</OuterReferences>
<RelOp AvgRowSize="27" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="48777400">
<OutputList>
<ColumnReference Column="Uniq1001" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Uniq1001" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
</DefinedValue>
</DefinedValues>
<Object Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Index="[IX_QO_SECTIONS_SectionId]" Alias="[QS]" IndexKind="NonClustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[@RandomSectionId]">
<Identifier>
<ColumnReference Column="@RandomSectionId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="48777400">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
</OutputList>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
</DefinedValue>
</DefinedValues>
<Object Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Index="[IX_QO_SECTIONS]" Alias="[QS]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
<ColumnReference Column="Uniq1001" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[WeirdEstimates].[dbo].[QO_SECTIONS].[CollectionId] as [QS].[CollectionId]">
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[WeirdEstimates].[dbo].[QO_SECTIONS].[SectionId] as [QS].[SectionId]">
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[Uniq1001]">
<Identifier>
<ColumnReference Column="Uniq1001" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="27" EstimateCPU="0.000272539" EstimateIO="0.00460648" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00487902" TableCardinality="162438000">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</DefinedValue>
</DefinedValues>
<Object Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Index="[IX_QO]" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="CollectionId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[WeirdEstimates].[dbo].[QO_SECTIONS].[CollectionId] as [QS].[CollectionId]">
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[WeirdEstimates].[dbo].[QO].[ObjectId]=[WeirdEstimates].[dbo].[QO_SECTIONS].[SectionId] as [QS].[SectionId] OR [WeirdEstimates].[dbo].[QO].[ParentObjectId]=[WeirdEstimates].[dbo].[QO_SECTIONS].[SectionId] as [QS].[SectionId]">
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="ObjectId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="ParentObjectId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</TopSort>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
<Statements>
<StmtSimple StatementCompId="3" StatementEstRows="1" StatementId="3" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="5365.95" StatementText=" -- old CE is better&#xD;&#xA;&#xD;&#xA; SELECT TOP 1 QuestionPoints, Page&#xD;&#xA; FROM QO_SECTIONS QS&#xD;&#xA; INNER JOIN QO&#xD;&#xA; ON QO.CollectionId = QS.CollectionId&#xD;&#xA; AND ( QO.ObjectId = QS.SectionId &#xD;&#xA; OR QO.ParentObjectId = QS.SectionId )&#xD;&#xA; WHERE SectionId = @RandomSectionId&#xD;&#xA; ORDER BY Page DESC" StatementType="SELECT" QueryHash="0xC69D17BCC06EE719" QueryPlanHash="0x39DD4F7813E2D682" RetrievedFromCache="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="56" CompileTime="11" CompileCPU="11" CompileMemory="568">
<ThreadStat Branches="2" />
<MemoryGrantInfo SerialRequiredMemory="1040" SerialDesiredMemory="2200" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="768000" EstimatedPagesCached="1536000" EstimatedAvailableDegreeOfParallelism="8" />
<RelOp AvgRowSize="20" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="5365.95">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</OutputList>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="20" EstimateCPU="0.028533" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="5365.95">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</OutputList>
<Parallelism>
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="20" EstimateCPU="999.513" EstimateIO="1306.61" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="TopN Sort" NodeId="2" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="5365.92">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</OutputList>
<MemoryFractions Input="0.00689655" Output="1" />
<TopSort Distinct="false" Rows="1">
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="20" EstimateCPU="310.476" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="162438000" LogicalOp="Inner Join" NodeId="3" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3059.8">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</OutputList>
<MemoryFractions Input="1" Output="0.993103" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="CollectionId" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[WeirdEstimates].[dbo].[QO].[CollectionId]=[WeirdEstimates].[dbo].[QO_SECTIONS].[CollectionId] as [QS].[CollectionId] AND ([WeirdEstimates].[dbo].[QO].[ObjectId]=[WeirdEstimates].[dbo].[QO_SECTIONS].[SectionId] as [QS].[SectionId] OR [WeirdEstimates].[dbo].[QO].[ParentObjectId]=[WeirdEstimates].[dbo].[QO_SECTIONS].[SectionId] as [QS].[SectionId])">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="CollectionId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="ObjectId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="ParentObjectId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="32" EstimateCPU="0.0285043" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Distribute Streams" NodeId="4" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0350747">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
</OutputList>
<Parallelism PartitioningType="Broadcast">
<RelOp AvgRowSize="32" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="Uniq1001" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
</OuterReferences>
<RelOp AvgRowSize="27" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="48777400">
<OutputList>
<ColumnReference Column="Uniq1001" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Uniq1001" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
</DefinedValue>
</DefinedValues>
<Object Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Index="[IX_QO_SECTIONS_SectionId]" Alias="[QS]" IndexKind="NonClustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[@RandomSectionId]">
<Identifier>
<ColumnReference Column="@RandomSectionId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="48777400">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
</OutputList>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="QuestionPoints" />
</DefinedValue>
</DefinedValues>
<Object Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Index="[IX_QO_SECTIONS]" Alias="[QS]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
<ColumnReference Column="Uniq1001" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[WeirdEstimates].[dbo].[QO_SECTIONS].[CollectionId] as [QS].[CollectionId]">
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="CollectionId" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[WeirdEstimates].[dbo].[QO_SECTIONS].[SectionId] as [QS].[SectionId]">
<Identifier>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Alias="[QS]" Column="SectionId" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[Uniq1001]">
<Identifier>
<ColumnReference Column="Uniq1001" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Parallelism>
</RelOp>
<RelOp AvgRowSize="35" EstimateCPU="22.3352" EstimateIO="2726.95" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="162438000" LogicalOp="Clustered Index Scan" NodeId="12" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="2749.29" TableCardinality="162438000">
<OutputList>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="ObjectId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="CollectionId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="ParentObjectId" />
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="ObjectId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="CollectionId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="ParentObjectId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Column="Page" />
</DefinedValue>
</DefinedValues>
<Object Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Index="[IX_QO]" IndexKind="Clustered" Storage="RowStore" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</TopSort>
</RelOp>
</Parallelism>
</RelOp>
</Top>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment