-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathExecutionPlan.sqlplan
More file actions
1 lines (1 loc) · 10.8 KB
/
ExecutionPlan.sqlplan
File metadata and controls
1 lines (1 loc) · 10.8 KB
1
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="12.0.2000.8"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT p.ProductId,SUM(h.ActualCost) 
FROM bigProduct AS p
INNER JOIN bigTransactionHistory AS h 
ON h.ProductId = p.ProductId
GROUP BY p.ProductId" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="125.071" StatementEstRows="21729.4" StatementOptmLevel="FULL" QueryHash="0x7494D384A7B45450" QueryPlanHash="0x9E20BB11A8C9A603" CardinalityEstimationModelVersion="70"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan CachedPlanSize="40" CompileTime="116" CompileCPU="19" CompileMemory="720"><ThreadStat Branches="3"/><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"/><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="12800" EstimatedPagesCached="3200" EstimatedAvailableDegreeOfParallelism="2"/><RelOp NodeId="1" PhysicalOp="Parallelism" LogicalOp="Gather Streams" EstimateRows="21664.8" EstimateIO="0" EstimateCPU="0.0600061" AvgRowSize="19" EstimatedTotalSubtreeCost="125.071" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigProduct]" Alias="[p]" Column="ProductID"/><ColumnReference Column="Expr1002"/></OutputList><Parallelism><RelOp NodeId="2" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="21664.8" EstimateIO="0" EstimateCPU="0.0568032" AvgRowSize="19" EstimatedTotalSubtreeCost="125.011" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigProduct]" Alias="[p]" Column="ProductID"/><ColumnReference Column="Expr1002"/></OutputList><Merge ManyToMany="0"><InnerSideJoinColumns><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ProductID"/></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigProduct]" Alias="[p]" Column="ProductID"/></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[AdventureWorks2012].[dbo].[bigProduct].[ProductID] as [p].[ProductID]=[AdventureWorks2012].[dbo].[bigTransactionHistory].[ProductID] as [h].[ProductID]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigProduct]" Alias="[p]" Column="ProductID"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ProductID"/></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="3" PhysicalOp="Parallelism" LogicalOp="Repartition Streams" EstimateRows="25200" EstimateIO="0" EstimateCPU="0.184693" AvgRowSize="11" EstimatedTotalSubtreeCost="0.64546" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigProduct]" Alias="[p]" Column="ProductID"/></OutputList><Parallelism PartitioningType="Hash"><PartitionColumns><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigProduct]" Alias="[p]" Column="ProductID"/></PartitionColumns><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigProduct]" Alias="[p]" Column="ProductID"/></OrderByColumn></OrderBy><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="25200" EstimateIO="0.446829" EstimateCPU="0.0139385" AvgRowSize="11" EstimatedTotalSubtreeCost="0.460767" TableCardinality="25200" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigProduct]" Alias="[p]" Column="ProductID"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigProduct]" Alias="[p]" Column="ProductID"/></DefinedValue></DefinedValues><Object Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigProduct]" Index="[pk_bigProduct]" Alias="[p]" IndexKind="Clustered" Storage="RowStore"/></IndexScan></RelOp></Parallelism></RelOp><RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="25200" EstimateIO="0" EstimateCPU="0.00126" AvgRowSize="19" EstimatedTotalSubtreeCost="124.309" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ProductID"/><ColumnReference Column="Expr1002"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"/><ScalarOperator ScalarString="CASE WHEN [globalagg1004]=(0) THEN NULL ELSE [globalagg1006] END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="globalagg1004"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Column="globalagg1006"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="25200" EstimateIO="0" EstimateCPU="0.02142" AvgRowSize="27" EstimatedTotalSubtreeCost="124.307" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ProductID"/><ColumnReference Column="globalagg1004"/><ColumnReference Column="globalagg1006"/></OutputList><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="globalagg1004"/><ScalarOperator ScalarString="SUM([partialagg1003])"><Aggregate Distinct="0" AggType="SUM"><ScalarOperator><Identifier><ColumnReference Column="partialagg1003"/></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="globalagg1006"/><ScalarOperator ScalarString="SUM([partialagg1005])"><Aggregate Distinct="0" AggType="SUM"><ScalarOperator><Identifier><ColumnReference Column="partialagg1005"/></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ProductID"/></GroupBy><RelOp NodeId="8" PhysicalOp="Parallelism" LogicalOp="Repartition Streams" EstimateRows="50400" EstimateIO="0" EstimateCPU="0.39305" AvgRowSize="27" EstimatedTotalSubtreeCost="124.286" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ProductID"/><ColumnReference Column="partialagg1003"/><ColumnReference Column="partialagg1005"/></OutputList><Parallelism PartitioningType="Hash"><PartitionColumns><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ProductID"/></PartitionColumns><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ProductID"/></OrderByColumn></OrderBy><RelOp NodeId="9" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="50400" EstimateIO="0" EstimateCPU="9.39168" AvgRowSize="27" EstimatedTotalSubtreeCost="123.893" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ProductID"/><ColumnReference Column="partialagg1003"/><ColumnReference Column="partialagg1005"/></OutputList><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="partialagg1003"/><ScalarOperator ScalarString="COUNT_BIG([AdventureWorks2012].[dbo].[bigTransactionHistory].[ActualCost] as [h].[ActualCost])"><Aggregate Distinct="0" AggType="COUNT_BIG"><ScalarOperator><Identifier><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ActualCost"/></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="partialagg1005"/><ScalarOperator ScalarString="SUM([AdventureWorks2012].[dbo].[bigTransactionHistory].[ActualCost] as [h].[ActualCost])"><Aggregate Distinct="0" AggType="SUM"><ScalarOperator><Identifier><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ActualCost"/></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ProductID"/></GroupBy><RelOp NodeId="10" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="3.12636e+007" EstimateIO="97.3061" EstimateCPU="17.1951" AvgRowSize="19" EstimatedTotalSubtreeCost="114.501" TableCardinality="3.12636e+007" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ProductID"/><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ActualCost"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ProductID"/></DefinedValue><DefinedValue><ColumnReference Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Alias="[h]" Column="ActualCost"/></DefinedValue></DefinedValues><Object Database="[AdventureWorks2012]" Schema="[dbo]" Table="[bigTransactionHistory]" Index="[IX_ProductId_TransactionDate]" Alias="[h]" IndexKind="NonClustered" Storage="RowStore"/></IndexScan></RelOp></StreamAggregate></RelOp></Parallelism></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp></Merge></RelOp></Parallelism></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>