Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Прошу помочь прочитать план выполнения запроса  [new]
grisha.butko
Member

Откуда:
Сообщений: 2
Добрый день, уважаемые форумчане!
Изучал примеры из топика опубликованного iap с примерами общих табличных выражений.
Решил вывести план выполенния запроса для примера №1 из данного топика.
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);


Получил следующий план выполнения запроса:


в XML (изображение приложил):

<?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&#xD;&#xA;(&#xD;&#xA; SELECT 1, @Seed WHERE @Cardinality &gt;= 1&#xD;&#xA; UNION ALL&#xD;&#xA; SELECT ID + 1, N + @Increment FROM T WHERE ID &lt; @Cardinality&#xD;&#xA;)&#xD;&#xA;SELECT ID, N FROM T ORDER BY ID&#xD;&#xA;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]&gt;=(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]&lt;[@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>


Не могли бы вы помочь мне разобрать данный план выполнения:
Пытался сам, но смущает два момента и от них идет непонимание.
перввый момент Table Spool - как данный оператор используется
и второй момент оператор Table Spool, зависит от оператора Index spool, а оператор Index Spool находится после оператора объединяющего данные.....

К сообщению приложен файл. Размер - 75Kb
25 сен 17, 16:59    [20821347]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь прочитать план выполнения запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
https://blogs.msdn.microsoft.com/craigfr/2007/10/25/recursive-ctes/
25 сен 17, 18:15    [20821569]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помочь прочитать план выполнения запроса  [new]
grisha.butko
Member

Откуда:
Сообщений: 2
Спасибо
26 сен 17, 09:41    [20822587]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить