1

Topic: I ask to help to read the request execution plan

Good afternoon, respected !
Studied examples from a topic published iap with examples of the general tabular expressions.
Decided to deduce the plan  request for an example No1 from the given topic.

DECLARE @Cardinality INT, @Seed INT, @Increment INT; [url =] [/url]
SELECT @Cardinality = 10, @Seed = 5, @Increment = 3;
WITH T (ID, N) AS
(
SELECT 1, @Seed WHERE @Cardinality> = 1
UNION ALL
SELECT ID + 1, N + @Increment FROM T WHERE ID <@Cardinality
)
SELECT ID, N FROM T ORDER BY ID
OPTION (MAXRECURSION 0);

Received the following execution plan of request:
In XML (the image put):

<? 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 = "2" StatementEstRows = "4" StatementId = "1" StatementOptmLevel = "FULL" StatementOptmEarlyAbortReason = "GoodEnoughPlanFound" CardinalityEstimationModelVersion = "120" StatementSubTreeCost = "0.0113838" StatementText = "WITH T (ID, N) AS
&#xA ;( 
 SELECT 1, @Seed WHERE @Cardinality >= 1
 UNION ALL
 SELECT ID + 1, N + @Increment FROM T WHERE ID < @Cardinality
&#xA ;) 
SELECT ID, N FROM T ORDER BY ID
OPTION (MAXRECURSION 0)" StatementType = "SELECT" QueryHash = "0x8E689938FAFE6EF3" QueryPlanHash = "0x5A68D2E5D4F49AA0" RetrievedFromCache = "true">
<StatementSetOptions ANSI_NULLS = "true" ANSI_PADDING = "true" ANSI_WARNINGS = "true" ARITHABORT = "true" CONCAT_NULL_YIELDS_NULL = "true" NUMERIC_ROUNDABORT = "false" QUOTED_IDENTIFIER = "true"/>
<QueryPlan DegreeOfParallelism = "1" MemoryGrant = "1024" CachedPlanSize = "24" CompileTime = "0" CompileCPU = "0" CompileMemory = "224">
<MemoryGrantInfo SerialRequiredMemory = "512" SerialDesiredMemory = "544" RequiredMemory = "512" DesiredMemory = "544" RequestedMemory = "1024" GrantWaitTime = "0" GrantedMemory = "1024" MaxUsedMemory = "16"/>
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant = "208281" EstimatedPagesCached = "104140" EstimatedAvailableDegreeOfParallelism = "4"/>
<RelOp AvgRowSize = "15" EstimateCPU = "0.000112495" EstimateIO = "0.0112613" EstimateRebinds = "0" EstimateRewinds = "0" EstimatedExecutionMode = "Row" EstimateRows = "4" LogicalOp = "Sort" NodeId = "0" Parallel = "false" PhysicalOp = "Sort" EstimatedTotalSubtreeCost = "0.0113838">
<OutputList>
<ColumnReference Column = "Recr1005"/>
<ColumnReference Column = "Recr1006"/>
</OutputList>
<MemoryFractions Input = "1" Output = "1"/>
<RunTimeInformation>
<RunTimeCountersPerThread Thread = "0" ActualRebinds = "1" ActualRewinds = "0" ActualRows = "10" ActualEndOfScans = "1" ActualExecutions = "1"/>
</RunTimeInformation>
<Sort Distinct = "false">
<OrderBy>
<OrderByColumn Ascending = "true">
<ColumnReference Column = "Recr1005"/>
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize = "15" EstimateCPU = "1E-08" EstimateIO = "0" EstimateRebinds = "0" EstimateRewinds = "0" EstimatedExecutionMode = "Row" EstimateRows = "4" LogicalOp = "Lazy Spool" NodeId = "1" Parallel = "false" PhysicalOp = "Index Spool" EstimatedTotalSubtreeCost = "9.994E-06">
<OutputList>
<ColumnReference Column = "Expr1010"/>
<ColumnReference Column = "Recr1005"/>
<ColumnReference Column = "Recr1006"/>
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread = "0" ActualRebinds = "1" ActualRewinds = "0" ActualRows = "10" ActualEndOfScans = "1" ActualExecutions = "1"/>
</RunTimeInformation>
<Spool Stack = "true">
<RelOp AvgRowSize = "15" EstimateCPU = "2E-09" EstimateIO = "0" EstimateRebinds = "0" EstimateRewinds = "0" EstimatedExecutionMode = "Row" EstimateRows = "4" LogicalOp = "Concatenation" NodeId = "2" Parallel = "false" PhysicalOp = "Concatenation" EstimatedTotalSubtreeCost = "8.404E-06">
<OutputList>
<ColumnReference Column = "Expr1010"/>
<ColumnReference Column = "Recr1005"/>
<ColumnReference Column = "Recr1006"/>
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread = "0" ActualRows = "10" ActualEndOfScans = "1" ActualExecutions = "1"/>
</RunTimeInformation>
<Concat>
<DefinedValues>
<DefinedValue>
<ColumnReference Column = "Expr1010"/>
<ColumnReference Column = "Expr1007"/>
<ColumnReference Column = "Expr1009"/>
</DefinedValue>
<DefinedValue>
<ColumnReference Column = "Recr1005"/>
<ColumnReference Column = "Expr1000"/>
<ColumnReference Column = "Expr1003"/>
</DefinedValue>
<DefinedValue>
<ColumnReference Column = "Recr1006"/>
<ColumnReference Column = "Seed"/>
<ColumnReference Column = "Expr1004"/>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize = "15" EstimateCPU = "2E-08" EstimateIO = "0" EstimateRebinds = "4" EstimateRewinds = "0" EstimatedExecutionMode = "Row" EstimateRows = "1" LogicalOp = "Compute Scalar" NodeId = "3" Parallel = "false" PhysicalOp = "Compute Scalar" EstimatedTotalSubtreeCost = "2E-08">
<OutputList>
<ColumnReference Column = "Expr1007"/>
<ColumnReference Column = "Expr1000"/>
<ColumnReference Column = "Seed"/>
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column = "Expr1007"/>
<ScalarOperator ScalarString = "(0)">
<Const ConstValue = "(0)"/>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize = "11" EstimateCPU = "1E-07" EstimateIO = "0" EstimateRebinds = "0" EstimateRewinds = "0" EstimatedExecutionMode = "Row" EstimateRows = "1" LogicalOp = "Compute Scalar" NodeId = "4" Parallel = "false" PhysicalOp = "Compute Scalar" EstimatedTotalSubtreeCost = "1.737E-06">
<OutputList>
<ColumnReference Column = "Expr1000"/>
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column = "Expr1000"/>
<ScalarOperator ScalarString = "(1)">
<Const ConstValue = "(1)"/>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize = "9" EstimateCPU = "4.8E-07" EstimateIO = "0" EstimateRebinds = "0" EstimateRewinds = "0" EstimatedExecutionMode = "Row" EstimateRows = "1" LogicalOp = "Filter" NodeId = "5" Parallel = "false" PhysicalOp = "Filter" EstimatedTotalSubtreeCost = "1.637E-06">
<OutputList/>
<RunTimeInformation>
<RunTimeCountersPerThread Thread = "0" ActualRebinds = "1" ActualRewinds = "0" ActualRows = "1" ActualEndOfScans = "1" ActualExecutions = "1"/>
</RunTimeInformation>
<Filter StartupExpression = "true">
<RelOp AvgRowSize = "9" EstimateCPU = "1.157E-06" EstimateIO = "0" EstimateRebinds = "0" EstimateRewinds = "0" EstimatedExecutionMode = "Row" EstimateRows = "1" LogicalOp = "Constant Scan" NodeId = "6" Parallel = "false" PhysicalOp = "Constant Scan" EstimatedTotalSubtreeCost = "1.157E-06">
<OutputList/>
<RunTimeInformation>
<RunTimeCountersPerThread Thread = "0" ActualRows = "1" ActualEndOfScans = "1" ActualExecutions = "1"/>
</RunTimeInformation>
<ConstantScan/>
</RelOp>
<Predicate>
<ScalarOperator ScalarString = "[Cardinality] >= (1)">
<Compare CompareOp = "GE">
<ScalarOperator>
<Identifier>
<ColumnReference Column = "Cardinality"/>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue = "(1)"/>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize = "15" EstimateCPU = "1.68E-07" EstimateIO = "0" EstimateRebinds = "4" EstimateRewinds = "0" EstimatedExecutionMode = "Row" EstimateRows = "4" LogicalOp = "Inner Join" NodeId = "14" Parallel = "false" PhysicalOp = "Nested Loops" EstimatedTotalSubtreeCost = "6.665E-06">
<OutputList>
<ColumnReference Column = "Expr1009"/>
<ColumnReference Column = "Expr1003"/>
<ColumnReference Column = "Expr1004"/>
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread = "0" ActualRows = "9" ActualEndOfScans = "1" ActualExecutions = "1"/>
</RunTimeInformation>
<NestedLoops Optimized = "false">
<OuterReferences>
<ColumnReference Column = "Expr1009"/>
<ColumnReference Column = "Recr1001"/>
<ColumnReference Column = "Recr1002"/>
</OuterReferences>
<RelOp AvgRowSize = "15" EstimateCPU = "2E-08" EstimateIO = "0" EstimateRebinds = "4" EstimateRewinds = "0" EstimatedExecutionMode = "Row" EstimateRows = "1" LogicalOp = "Compute Scalar" NodeId = "15" Parallel = "false" PhysicalOp = "Compute Scalar" EstimatedTotalSubtreeCost = "2E-08">
<OutputList>
<ColumnReference Column = "Expr1009"/>
<ColumnReference Column = "Recr1001"/>
<ColumnReference Column = "Recr1002"/>
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column = "Expr1009"/>
<ScalarOperator ScalarString = "[Expr1008] + (1)">
<Arithmetic Operation = "ADD">
<ScalarOperator>
<Identifier>
<ColumnReference Column = "Expr1008"/>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue = "(1)"/>
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize = "15" EstimateCPU = "2E-08" EstimateIO = "0" EstimateRebinds = "4" EstimateRewinds = "0" EstimatedExecutionMode = "Row" EstimateRows = "1" LogicalOp = "Lazy Spool" NodeId = "16" Parallel = "false" PhysicalOp = "Table Spool" EstimatedTotalSubtreeCost = "2E-08">
<OutputList>
<ColumnReference Column = "Expr1008"/>
<ColumnReference Column = "Recr1001"/>
<ColumnReference Column = "Recr1002"/>
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread = "0" ActualRebinds = "1" ActualRewinds = "0" ActualRows = "10" ActualEndOfScans = "1" ActualExecutions = "1"/>
</RunTimeInformation>
<Spool Stack = "true" PrimaryNodeId = "1"/>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize = "15" EstimateCPU = "1E-07" EstimateIO = "0" EstimateRebinds = "3" EstimateRewinds = "0" EstimatedExecutionMode = "Row" EstimateRows = "1" LogicalOp = "Compute Scalar" NodeId = "20" Parallel = "false" PhysicalOp = "Compute Scalar" EstimatedTotalSubtreeCost = "6.477E-06">
<OutputList>
<ColumnReference Column = "Expr1003"/>
<ColumnReference Column = "Expr1004"/>
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column = "Expr1003"/>
<ScalarOperator ScalarString = "[Recr1001] + (1)">
<Arithmetic Operation = "ADD">
<ScalarOperator>
<Identifier>
<ColumnReference Column = "Recr1001"/>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue = "(1)"/>
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column = "Expr1004"/>
<ScalarOperator ScalarString = "[Recr1002] + [@Increment]">
<Arithmetic Operation = "ADD">
<ScalarOperator>
<Identifier>
<ColumnReference Column = "Recr1002"/>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column = "Increment"/>
</Identifier>
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize = "9" EstimateCPU = "4.8E-07" EstimateIO = "0" EstimateRebinds = "3" EstimateRewinds = "0" EstimatedExecutionMode = "Row" EstimateRows = "1" LogicalOp = "Filter" NodeId = "21" Parallel = "false" PhysicalOp = "Filter" EstimatedTotalSubtreeCost = "6.077E-06">
<OutputList/>
<RunTimeInformation>
<RunTimeCountersPerThread Thread = "0" ActualRebinds = "10" ActualRewinds = "0" ActualRows = "9" ActualEndOfScans = "10" ActualExecutions = "10"/>
</RunTimeInformation>
<Filter StartupExpression = "true">
<RelOp AvgRowSize = "9" EstimateCPU = "1.157E-06" EstimateIO = "0" EstimateRebinds = "0" EstimateRewinds = "3" EstimatedExecutionMode = "Row" EstimateRows = "1" LogicalOp = "Constant Scan" NodeId = "22" Parallel = "false" PhysicalOp = "Constant Scan" EstimatedTotalSubtreeCost = "4.157E-06">
<OutputList/>
<RunTimeInformation>
<RunTimeCountersPerThread Thread = "0" ActualRows = "9" ActualEndOfScans = "9" ActualExecutions = "9"/>
</RunTimeInformation>
<ConstantScan/>
</RelOp>
<Predicate>
<ScalarOperator ScalarString = "[Recr1001] < [@Cardinality]">
<Compare CompareOp = "LT">
<ScalarOperator>
<Identifier>
<ColumnReference Column = "Recr1001"/>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column = "Cardinality"/>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</ComputeScalar>
</RelOp>
</NestedLoops>
</RelOp>
</Concat>
</RelOp>
</Spool>
</RelOp>
</Sort>
</RelOp>
<ParameterList>
<ColumnReference Column = "Increment" ParameterRuntimeValue = "(3)"/>
<ColumnReference Column = "Seed" ParameterRuntimeValue = "(5)"/>
<ColumnReference Column = "Cardinality" ParameterRuntimeValue = "(10)"/>
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

You to me could not help to disassemble the given execution plan:
Tried itself, but confuses two moments and from them there is a misunderstanding.
moment Table Spool - as the given operator is used
And the second moment operator Table Spool, depends on operator Index spool, and operator Index Spool is after the operator uniting the data.....

2

Re: I ask to help to read the request execution plan

https://blogs.msdn.microsoft.com/craigf … sive-ctes/

3

Re: I ask to help to read the request execution plan

Thanks