我读过的所有地方都说估计的操作员成本是估计的 CPU 成本和估计的 I/O 成本的总和。但是,在我看到的许多运营商中,情况并非如此。这是一个例子:
SELECT Column2
INTO Object1
FROM Object2
WHERE Column3 >= Variable2
AND Column3 <= Variable1
AND ( Column4 = Variable5
OR Variable5 = ? )
EstimateIO="0.01" EstimateCPU="0.000246492"
总和:0.010246492
然而,SSMS 将这个 0.073823 显示为估计的运营商成本。我完全不知道这是如何计算的。下面是执行计划 xml(匿名)。节点 ID 0 是有问题的节点。
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.6537.0">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="	SELECT Column2 INTO Object1 
	FROM Object2
	WHERE Column3>=Variable2 AND Column3<=Variable1 
	AND (Column4=Variable5 OR Variable5=?)

" StatementId="1" StatementCompId="7" StatementType="SELECT INTO" RetrievedFromCache="true" StatementSubTreeCost="0.405134" StatementEstRows="246.492" StatementOptmLevel="FULL" QueryHash="0x180DF38DFFFEAFA2" QueryPlanHash="0x45A4295471B90968" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="48" CompileTime="23" CompileCPU="6" CompileMemory="360">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="157286" EstimatedPagesCached="314572" EstimatedAvailableDegreeOfParallelism="16" />
<RelOp NodeId="0" PhysicalOp="Table Insert" LogicalOp="Insert" EstimateRows="246.492" EstimateIO="0.01" EstimateCPU="0.000246492" AvgRowSize="9" EstimatedTotalSubtreeCost="0.405134" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList />
<Update DMLRequestSort="0">
<Object Table="Object1" />
<SetPredicate>
<ScalarOperator ScalarString="ScalarString1">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Table="Object1" Column="Column2" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column2" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="246.492" EstimateIO="0.22831" EstimateCPU="0.103001" AvgRowSize="15" EstimatedTotalSubtreeCost="0.331311" TableCardinality="1.03883e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column2" />
</OutputList>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column2" />
</DefinedValue>
</DefinedValues>
<Object Database="Database1" Schema="Schema1" Table="Object2" Index="Index1" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<StartRange ScanType="GE">
<RangeColumns>
<ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column3" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="ScalarString2">
<Identifier>
<ColumnReference Column="Column7" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange ScanType="LE">
<RangeColumns>
<ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column3" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="ScalarString3">
<Identifier>
<ColumnReference Column="Column8" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="ScalarString4">
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column4" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Column9" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Column10">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Column9" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="Value4" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Update>
</RelOp>
<ParameterList>
<ColumnReference Column="Column9" ParameterCompiledValue="Value1" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
编辑:意识到我没有发布一个精心设计的问题。这是问题:
给定示例计划,SSMS 和计划资源管理器使用什么公式或计算得出节点 0 的估计运营商成本 0.073823?
正确回答该问题的唯一方法是启动调试器并查看优化器在此过程中做出了哪些选择。成本不仅是IO和CPU。与给定运算符相关的额外成本反映在总成本中,但未反映在 IO 和 CPU 成本估算中。您可以在Paul White 撰写的这篇优秀文章中了解更多有关一些额外费用的信息。
我对你的问题没有准确的答案(我不怀疑,保罗会)。不过,我愿意猜测一下。您所看到的是由优化器确定的操作的额外开销,超出了它显示为 IO 和 CPU 的开销(由估计的行等确定)。我相信这是基于什么的计算就 IO 而言,创建表并存储 246.492 行 * 9b 的数据是必要的,这些数据计算为在 INSERT 语句中。246.492 * 9 / 1024 = 2.1664 小于 8k 页。但是,我们必须至少创建一个页面,因此当您计算 8 * 0.01 的成本时,它使我们略高于估计的 0.073832。这是我的猜测,也是一个猜测。但是,我确实知道成本中存在间接费用
格兰特,我相信一位同事(丹尼斯·罗杰斯)和我已经回答了这个问题。这里出现了 SSMS 用来计算运营商成本和成本百分比的确定公式。
估计的运营商成本 == @EstimatedTotalSubtreeCost - Sum(Immediate Children@EstimatedTotalSubtreeCost)
估计的操作员成本百分比 = 估计的操作员成本 / StmtSimple@StatementSubTreeCost * 100
我已经用几个计划对此进行了测试,这似乎是正确的。
要将上述计算表示为 xpath,它变成:
sum 找到第一个 RelOp 类型的后代,然后备份一个级别,并获取该级别的所有 RelOps 并提取它们的 EstimatedTotalSubtreeCost。