Skip to content

Instantly share code, notes, and snippets.

@mjswart

mjswart/Weird Estimates 2017.sqlplan Secret

Created August 17, 2018 18:32
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mjswart/7d73ecd577f72dfbf1a4ad47097751c2 to your computer and use it in GitHub Desktop.
Save mjswart/7d73ecd577f72dfbf1a4ad47097751c2 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Weird Estimates 2017.sqlplan
<?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.6" Build="14.0.1000.169" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementId="1" StatementText="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="140" StatementSubTreeCost="0.0244424" 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" StatementType="SELECT" QueryHash="0xC69D17BCC06EE719" QueryPlanHash="0x52EAA81B507A5009" RetrievedFromCache="false" SecurityPolicyApplied="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="40" CompileTime="2" CompileCPU="2" CompileMemory="688">
<MemoryGrantInfo SerialRequiredMemory="16" SerialDesiredMemory="24" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="307200" EstimatedPagesCached="153600" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="14595744" />
<OptimizerStatsUsage>
<StatisticsInfo Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Statistics="[PK_QO_SECTIONS]" ModificationCount="0" SamplingPercent="33.0627" LastUpdate="2018-07-20T01:56:03.38" />
<StatisticsInfo Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO_SECTIONS]" Statistics="[IX_QO_SECTIONS]" ModificationCount="0" SamplingPercent="33.0457" LastUpdate="2018-07-20T01:48:22.15" />
<StatisticsInfo Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Statistics="[_WA_Sys_00000008_4890A6B3]" ModificationCount="0" SamplingPercent="0.349944" LastUpdate="2017-08-22T22:41:25.18" />
<StatisticsInfo Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Statistics="[_WA_Sys_0000000B_4890A6B3]" ModificationCount="0" SamplingPercent="0.339409" LastUpdate="2017-10-06T17:52:32.92" />
<StatisticsInfo Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Statistics="[_WA_Sys_0000000A_4890A6B3]" ModificationCount="0" SamplingPercent="0.328442" LastUpdate="2017-12-13T17:36:28.91" />
<StatisticsInfo Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Statistics="[PK_QO]" ModificationCount="0" SamplingPercent="33.056" LastUpdate="2018-07-20T01:55:46.24" />
<StatisticsInfo Database="[WeirdEstimates]" Schema="[dbo]" Table="[QO]" Statistics="[IX_QO]" ModificationCount="0" SamplingPercent="33.0915" LastUpdate="2018-07-20T01:57:14.44" />
</OptimizerStatsUsage>
<RelOp AvgRowSize="20" EstimateCPU="0.00120027" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0244424">
<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="0.000439049" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="105.036" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0119809">
<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" EstimatedRowsRead="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="105.036" EstimatedRowsRead="105.036" LogicalOp="Clustered Index Seek" NodeId="6" 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>
</Batch>
</BatchSequence>
</ShowPlanXML>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment