Início / dba / Perguntas / 254116
Accepted
Adam K.
Asked: 2019-11-26 13:01:40 +0800 CST

Esquisitices de execução de consulta entre o SQL Server 2008r2, 2017 e 2019

  • 772

Eu tenho uma consulta muito básica que estava funcionando completamente bem com buscas de índice em toda a linha no SQL Server 2008r2. Quando migramos para o SQL Server 2017, ele começou a ter um desempenho pior e agora faz uma verificação de índice clusterizado em Remote_Records na consulta com a qual nunca teve problemas antes, fazendo com que o restante de nossa consulta seja executado por um minuto ou mais, dependendo de em quantos registros a função é aplicada. Isso funcionou em meros segundos antes de atualizarmos. Alterá-lo de volta para usar o Estimador de Cardinalidade herdado mudou o plano de volta, mas essa é uma opção que gostaríamos de evitar.

Também o testei no SQL Server 2019 e ele retornou ao mesmo tipo de plano que o SQL 2008r2, exceto que a busca de índice clusterizado em Local_Record_Additional_Data agora está usando o modo de lote no processamento RowStore para obter a busca.

Posso fazer com que a consulta não use uma verificação de índice clusterizado em Remote_Records usando um TOP 1 na consulta no SQL 2017, mas como isso funcionou no SQL 2008r2 sem modificações adicionais, estou meio perdido por que o Estimador de cardinalidade está desligado neste caso.

Essa consulta faz parte de uma função maior com valor de tabela em linha que é aplicada a um determinado valor de id para coletar determinados dados que queremos exibir. Esta é a única parte da função que está tendo problemas.

Qualquer conselho seria apreciado.

Aqui estão os links do plano de execução: Plano original do SQL 2008r2: https://www.brentozar.com/pastetheplan/?id=S1G4pnK2H

Plano original do SQL 2017: https://www.brentozar.com/pastetheplan/?id=B1ALKXQ2S

SQL 2017 com TF 9481: https://www.brentozar.com/pastetheplan/?id=ByguM2Y2S

A Microsoft não publicou o DTD para SQL 2019, incluí o XML abaixo para o plano SQL 2019.

Código para gerar as tabelas e o conjunto de dados para espelhar o que estamos vendo na produção:

CREATE TABLE Remote_Records  
(
    Remote_record_id int identity (1,1) PRIMARY KEY CLUSTERED,
    Bit_1 bit,
    Bit_2 bit
)
GO
CREATE TABLE Local_Record_Additional_Data 
(
    Local_record_id int identity(1,1) PRIMARY KEY CLUSTERED,
    Status_data varchar(100),
    Device_data varchar(25)
)
GO
CREATE TABLE Remote_Additional_Data 
(
    Remote_add_data_id int identity(1,1) PRIMARY KEY CLUSTERED,
    Remote_record_id int NOT NULL CONSTRAINT FK_Remote_Additional_Data_Remote_Records_Remote_record_id  FOREIGN KEY (Remote_record_id) REFERENCES Remote_Records(Remote_record_id),
    Local_record_add_id int NULL CONSTRAINT FK_Remote_Additional_Data_Local_Record_Additional_Data FOREIGN KEY (Local_record_add_id) REFERENCES Local_Record_Additional_Data(Local_record_id)
)
GO



DECLARE @count int = 1
WHILE @count <=319756
BEGIN
    INSERT INTO Remote_Records
    SELECT 0,0
    SET @count = @count + 1
END
GO

DECLARE @count int = 1
WHILE @count <=457
BEGIN
    INSERT INTO Local_Record_Additional_Data(Status_data,Device_data)
    SELECT NULL,NULL
    SET @count = @count + 1
END
GO


DECLARE @count int = 2
DECLARE @count_2 int = 1
DECLARE @rr_id int
DECLARE @max_rr_id int 
DECLARE @rad_id int = 1
DECLARE @lrad_id int
SELECT @max_rr_id = max(Remote_record_id) FROM Remote_Records
WHILE @count <= 486004
BEGIN
    IF @count > @max_rr_id
    BEGIN
        SET @count_2 = @count_2 + 1
    END

    IF @count >= 486004-456
    BEGIN
        SET @lrad_id = ISNULL(@lrad_id,0) + 1
    END
    SELECT @rr_id = CASE WHEN @count > @max_rr_id THEN @count_2 ELSE @count END

    INSERT INTO Remote_Additional_Data(Remote_record_id, Local_record_add_id)
    SELECT @rr_id, @lrad_id

    SET @count = @count + 1

END
GO
sp_createstats 'NO','NO','NORECOMPUTE'
GO

UPDATE STATISTICS Remote_Records WITH FULLSCAN
GO
UPDATE STATISTICS Local_Record_Additional_Data WITH FULLSCAN
GO
UPDATE STATISTICS Remote_Additional_Data WITH FULLSCAN
GO
CREATE INDEX IX_Remote_Additional_Data_Remote_record_id ON [Remote_Additional_Data](Remote_record_id) INCLUDE (Local_record_add_id) 
GO
CREATE INDEX IX_Remote_Additional_Data_Local_record_add_id ON [Remote_Additional_Data](Local_record_add_id) INCLUDE (Remote_record_id) 
GO

Aqui está a consulta do problema:

declare @lra_id int = 267
SELECT  Local_record_add_id,
        Bit_1,
        Bit_2
        FROM Remote_records se 
        INNER JOIN Remote_Additional_Data foe ON se.Remote_record_id = foe.Remote_record_id
        INNER JOIN Local_Record_Additional_Data foh on foe.Local_record_add_id = foh.Local_record_id
        WHERE foe.Local_record_add_id = @lra_id
go

O XML do plano de execução do SQL 2019:

<?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.539" Build="15.0.2000.5" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.00985766" StatementText="SELECT  Local_record_add_id,&#xD;&#xA;      Bit_1,&#xD;&#xA;        Bit_2&#xD;&#xA;     FROM Remote_records se &#xD;&#xA;       INNER JOIN Remote_Additional_Data foe ON se.Remote_record_id = foe.Remote_record_id&#xD;&#xA;       INNER JOIN Local_Record_Additional_Data foh on foe.Local_record_add_id = foh.Local_record_id&#xD;&#xA;      WHERE foe.Local_record_add_id = @lra_id" StatementType="SELECT" QueryHash="0x6825DB46BFFA067E" QueryPlanHash="0xE4DD43257F5DD402" RetrievedFromCache="false" SecurityPolicyApplied="false" BatchModeOnRowStoreUsed="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" CachedPlanSize="32" CompileTime="8" CompileCPU="8" CompileMemory="376">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="102400" EstimatedPagesCached="153600" EstimatedAvailableDegreeOfParallelism="12" MaxCompileMemory="18406536" />
            <OptimizerStatsUsage>
              <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Statistics="[Local_record_add_id]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:16.28" />
              <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Statistics="[IX_Remote_Additional_Data_Remote_record_id]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:16.5" />
              <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Statistics="[Remote_record_id]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:15.86" />
              <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Statistics="[IX_Remote_Additional_Data_Local_record_add_id]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:17.78" />
              <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Statistics="[PK__Remote_R__FEF044CF617642DF]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:14.75" />
            </OptimizerStatsUsage>
            <QueryTimeStats CpuTime="0" ElapsedTime="0" />
            <RelOp AvgRowSize="12" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985766">
              <OutputList>
                <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_1" />
                <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_2" />
                <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
              </RunTimeInformation>
              <NestedLoops Optimized="false">
                <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="457">
                  <OutputList />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="1" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                  </RunTimeInformation>
                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                    <DefinedValues />
                    <Object Database="[Stats_Testing]" Schema="[dbo]" Table="[Local_Record_Additional_Data]" Index="[PK__Local_Re__E377E55CA10BE177]" Alias="[foh]" IndexKind="Clustered" Storage="RowStore" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Local_Record_Additional_Data]" Alias="[foh]" Column="Local_record_id" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="[@lra_id]">
                                <Identifier>
                                  <ColumnReference Column="@lra_id" />
                                </Identifier>
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
                <RelOp AvgRowSize="12" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
                  <OutputList>
                    <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_1" />
                    <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_2" />
                    <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                  </RunTimeInformation>
                  <NestedLoops Optimized="false">
                    <OuterReferences>
                      <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Remote_record_id" />
                    </OuterReferences>
                    <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="4" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="486003">
                      <OutputList>
                        <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Remote_record_id" />
                        <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                      </RunTimeInformation>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Remote_record_id" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Index="[IX_Remote_Additional_Data_Local_record_add_id]" Alias="[foe]" IndexKind="NonClustered" Storage="RowStore" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[@lra_id]">
                                    <Identifier>
                                      <ColumnReference Column="@lra_id" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                    <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="319756">
                      <OutputList>
                        <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_1" />
                        <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_2" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                      </RunTimeInformation>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_1" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_2" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Index="[PK__Remote_R__FEF044CF617642DF]" Alias="[se]" IndexKind="Clustered" Storage="RowStore" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Remote_record_id" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[Stats_Testing].[dbo].[Remote_Additional_Data].[Remote_record_id] as [foe].[Remote_record_id]">
                                    <Identifier>
                                      <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Remote_record_id" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </NestedLoops>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@lra_id" ParameterDataType="int" ParameterRuntimeValue="(267)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
sql-server performance

2 respostas

  1. Best Answer

    Parece que isso foi corrigido pela Microsoft com um dos hotfixes para 2017. Depois de habilitar o TF 4199, o plano de execução voltou ao que esperávamos.

    • 3

  2. Até onde posso ver, o grande problema são suas estatísticas. Na consulta SQL2008, o índice search into Remote_Additional_Datafrom Local_Additional_Record_Dataespera que apenas uma linha seja retornada, enquanto em 2017, ele espera que mais de mil registros sejam retornados. Isso alterou a junção de loop aninhado anterior em uma junção de mesclagem (o que pode ser incorreto para o grande diferencial de linha entre elas, mas ainda é uma possibilidade com mais de 1.000 linhas para corresponder).

    Portanto, seu problema básico parece ser manutenção de estatísticas ou dados incorretos. Não parece um problema real com a consulta em si.

    • -1

relate perguntas