Início / dba / Perguntas / 134172
Accepted
TheGameiswar
Asked: 2016-04-03 06:00:41 +0800 CST

Definir E/S de estatísticas para loops aninhados

  • 772

Considere a consulta abaixo:

CREATE PROC dbo.GetPage  @orderid  AS INT    = 0, -- anchor sort key
            @pagesize AS BIGINT = 25
 AS
SELECT
TOP (@pagesize) orderid, orderdate, custid, empid
 FROM dbo.Orders WHERE orderid > @orderid ORDER BY orderid;

exec GetPage 25,25

SET STATISTICS IO para a consulta acima retornou:

(25 row(s) affected)
Table 'Orders'. Scan count 1, logical reads 87, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

A explicação de Itzik Ben-Gan em seu livro é assim para as leituras acima:

Os custos de I/O envolvidos na execução do plano de consulta são os seguintes:

  • Procure a folha do índice: 3 leituras (o índice tem três níveis).
  • Varredura de intervalo de 25 linhas: 0–1 leituras (centenas de linhas cabem em uma página).
  • Pré-busca de loops aninhados usada para otimizar pesquisas: 9 leituras (medidas pela desativação da pré-busca com sinalizador de rastreamento 8744)
  • 25 pesquisas principais: 75 leituras

plano de consulta

Plano de execução

Agora, minha pergunta é: como os loops aninhados fazem uma pesquisa de chave uma vez para cada linha retornada da busca, as leituras de busca devem ser 25 * 3: 75, o mesmo que as pesquisas de chave?

Plano de consulta 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.4" Build="13.0.900.73" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="3" StatementEstRows="25" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130" StatementSubTreeCost="0.0887816" StatementText=" CREATE PROC dbo.GetPage  @orderid  AS INT    = 0, -- anchor sort key &#xD;&#xA;  @pagesize AS BIGINT = 25 &#xD;&#xA;  AS&#xD;&#xA;SELECT TOP (@pagesize) orderid, orderdate, custid, empid FROM dbo.Orders WHERE orderid &gt; @orderid ORDER BY orderid" StatementType="SELECT" QueryHash="0x48DC1D1D4649B914" QueryPlanHash="0x8FDC055F05E0E93C" RetrievedFromCache="true" 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="32" CompileTime="2" CompileCPU="2" CompileMemory="208">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="52428" EstimatedPagesCached="13107" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp AvgRowSize="29" EstimateCPU="2.5E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="25" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0887816">
              <OutputList>
                <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderid" />
                <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="custid" />
                <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="empid" />
                <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" />
              </OutputList>
              <Top RowCount="false" IsPercent="false" WithTies="false">
                <TopExpression>
                  <ScalarOperator ScalarString="[@pagesize]">
                    <Identifier>
                      <ColumnReference Column="@pagesize" />
                    </Identifier>
                  </ScalarOperator>
                </TopExpression>
                <RelOp AvgRowSize="29" EstimateCPU="4.1799" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="25" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0887791">
                  <OutputList>
                    <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderid" />
                    <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="custid" />
                    <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="empid" />
                    <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" />
                  </OutputList>
                  <NestedLoops Optimized="false" WithOrderedPrefetch="true">
                    <OuterReferences>
                      <ColumnReference Column="Uniq1001" />
                      <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" />
                      <ColumnReference Column="Expr1003" />
                    </OuterReferences>
                    <RelOp AvgRowSize="18" EstimateCPU="1.10013" EstimateIO="1.92683" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="25" LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00335567" TableCardinality="1000000">
                      <OutputList>
                        <ColumnReference Column="Uniq1001" />
                        <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderid" />
                        <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" />
                      </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="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderid" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Index="[PK_Orders]" IndexKind="NonClustered" Storage="RowStore" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <StartRange ScanType="GT">
                                <RangeColumns>
                                  <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderid" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[@orderid]">
                                    <Identifier>
                                      <ColumnReference Column="@orderid" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </StartRange>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                    <RelOp AvgRowSize="22" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="25" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0853189" TableCardinality="1000000">
                      <OutputList>
                        <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="custid" />
                        <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="empid" />
                      </OutputList>
                      <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="custid" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="empid" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Index="[idx_cl_od]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" />
                                  <ColumnReference Column="Uniq1001" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[PerformanceV3].[dbo].[Orders].[orderdate]">
                                    <Identifier>
                                      <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[Uniq1001]">
                                    <Identifier>
                                      <ColumnReference Column="Uniq1001" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </Top>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@pagesize" ParameterCompiledValue="(25)" />
              <ColumnReference Column="@orderid" ParameterCompiledValue="(25)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
sql-server execution-plan

1 respostas

  1. Best Answer

    Agora, minha pergunta é: como os loops aninhados fazem uma pesquisa de chave uma vez para cada linha retornada da busca, as leituras de busca devem ser 25 * 3: 75 iguais às pesquisas de chave

    Se a pergunta for "a busca também requer 75 leituras?" então a resposta é não, pelas razões que Itzik deu, e citou na pergunta:

    Procure a folha do índice: 3 leituras (o índice tem três níveis) Varredura de intervalo de 25 linhas: 0–1 leituras (centenas de linhas cabem em uma página)

    A busca inicial para encontrar a posição inicial da varredura de alcance (no operador Index Seek) leva 3 leituras. A partir desse ponto, o mecanismo de armazenamento lembra a posição atual da varredura, portanto, buscar a próxima linha Index Seek requer zero ou uma leitura. Zero lê se a próxima linha estiver na mesma página; uma lida se estiver na próxima página.

    A diferença de comportamento é uma fonte comum de confusão e uma das razões pelas quais não gosto de leituras lógicas como métrica de desempenho.

    • 9

relate perguntas