Início / dba / Perguntas / 331081
Accepted
Geezer
Asked: 2023-09-11 16:26:59 +0800 CST

Obtendo um operador SORT quando tenho um índice

  • 772

Em um banco de dados SQL do Azure (compatibilidade com SQL2019), tenho um processo ETL que preenche tabelas HISTORY em um padrão DeltaTrack.

No Proc, há um UPDATE na tabela HISTORY que o mecanismo de consulta está usando um SORT, mas tenho um índice que deve cobri-lo.

O caso de uso para este UPDATE é para linhas existentes onde adicionamos colunas adicionais à ingestão desde que a linha foi adicionada pela primeira vez à tabela HISTORY.

Este SORT está fazendo com que os Procs em que as atualizações estão em nossas tabelas maiores/mais largas sejam dolorosamente lentos.

Como ajusto o índice ou consulta para remover o SORT na consulta 3 ?

Aqui está o plano de execução atualizado conforme solicitado por JD

Aqui está o DDL.

DROP TABLE IF EXISTS dbo.STAGE;
GO
CREATE TABLE dbo.STAGE
(
    Id varchar(18) NULL,
    CreatedDate varchar(4000) NULL,
    LastModifiedDate varchar(4000) NULL,
    LastReferencedDate varchar(4000) NULL,
    [Name] varchar(4000) NULL,
    OwnerId varchar(4000) NULL,
    SystemTimestamp datetime2(7) NULL
)
GO

DROP TABLE IF EXISTS dbo.HISTORY;
GO
CREATE TABLE dbo.HISTORY
(
    HistoryRecordId int IDENTITY(1,1) NOT NULL,
    [Hash] binary(64) NOT NULL,
    [IsActive]  BIT NOT NULL ,
    ActiveFromDateTime datetime2(7) NOT NULL,
    ActiveToDateTime datetime2(7) NOT NULL,
    Id varchar(18) NOT NULL,
    CreatedDate datetime2(7) NULL,
    LastModifiedDate datetime2(7) NULL,
    LastReferencedDate datetime2(7) NULL,
    [Name] varchar(80) NULL,
    OwnerId varchar(18) NULL,
    SystemTimestamp datetime2(7) NULL
) 
GO
CREATE UNIQUE CLUSTERED INDEX [CL__HISTORY] ON dbo.HISTORY
(
    Id , 
    [ActiveToDateTime] ASC,
    [IsActive] ASC
)
GO
CREATE NONCLUSTERED INDEX [IX__HISTORY_IsActive] ON dbo.HISTORY
(
    [Id] ASC
)
INCLUDE([IsActive],[ActiveToDateTime]) 
GO

DROP TABLE IF EXISTS #updates;
GO


WITH src AS (
  SELECT 
    CONVERT(VARCHAR(18), t.[Id]) AS [Id]
  , CONVERT(DATETIME2, t.[CreatedDate]) AS [CreatedDate]
  , CONVERT(DATETIME2, t.[LastModifiedDate]) AS [LastModifiedDate]
  , CONVERT(DATETIME2, t.[LastReferencedDate]) AS [LastReferencedDate]
  , CONVERT(VARCHAR(80), t.[Name]) AS [Name]
  , CONVERT(VARCHAR(18), t.[OwnerId]) AS [OwnerId]
  , CONVERT(DATETIME2, t.SystemTimestamp) AS SystemTimestamp
  , dgst.[Hash]
  , CONVERT(DATETIME2, SystemTimestamp) AS [ActiveFromDateTime]
  , RN = ROW_NUMBER() OVER ( 
            PARTITION BY 
                t.[Id] 
                ORDER BY CONVERT(DATETIME2, SystemTimestamp) DESC
        ) 
  FROM dbo.STAGE t
    OUTER APPLY (
      SELECT 
        CAST(HASHBYTES('SHA2_256',
          COALESCE(CAST([CreatedDate] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST([LastModifiedDate] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST([LastReferencedDate] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST([Name] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST([OwnerId] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST(SystemTimestamp AS NVARCHAR(4000)), N'')
        ) AS BINARY(64)) AS [Hash]
      ) dgst
), tgt AS (
  SELECT *
  FROM dbo.HISTORY t
  WHERE t.[ActiveToDateTime] > GETUTCDATE()
  AND 1 = 1  
)
SELECT 
  tgt.HistoryRecordId
, src.*
INTO #updates
FROM src
  LEFT JOIN tgt 
    ON tgt.[Id] = src.[Id] WHERE src.RN = 1;  
GO

--Create index on temp table (#updates) 
CREATE NONCLUSTERED INDEX NCCI_#updates__Kimble_HISTORY_ForecastStatus 
    ON #updates ( [Id] , ActiveFromDateTime, [Hash] );
GO  


    UPDATE  tgt 
    SET
      tgt.[Hash]        = src.[Hash] 
    , tgt.IsActive      = 1
    , tgt.[CreatedDate] = src.[CreatedDate]
    , tgt.[LastModifiedDate]    = src.[LastModifiedDate]
    , tgt.[LastReferencedDate]  = src.[LastReferencedDate]
    , tgt.[Name]            = src.[Name]
    , tgt.[OwnerId]         = src.[OwnerId]
    , tgt.SystemTimestamp   = src.SystemTimestamp
    FROM dbo.HISTORY tgt
      INNER JOIN #updates src   
            ON tgt.[Id] = src.[Id]
            AND src.[ActiveFromDateTime] = tgt.[ActiveFromDateTime] 
            AND tgt.[Hash]  <> src.[Hash] ; 
GO
sql-server

3 respostas

  1. Best Answer

    A Idcoluna na sua tabela temporária é única, mas você não está informando isso ao otimizador.

    Substitua o índice não clusterizado existente na tabela temporária por:

    CREATE UNIQUE CLUSTERED INDEX CCI_#updates__Id
ON #updates ([Id]);

    Observe que o índice é UNIQUEe CLUSTERED.

    Isso removerá o Hash Match Aggregate do plano (escolhendo valores de linha arbitrários por chave não declarada).

    Agora adicione uma FORCESEEKdica à atualização final:

    UPDATE tgt 
SET
    tgt.[Hash] = src.[Hash], 
    tgt.IsActive = 1, 
    tgt.[CreatedDate] = src.[CreatedDate], 
    tgt.[LastModifiedDate] = src.[LastModifiedDate],
    tgt.[LastReferencedDate] = src.[LastReferencedDate],
    tgt.[Name] = src.[Name],
    tgt.[OwnerId] = src.[OwnerId],
    tgt.SystemTimestamp = src.SystemTimestamp
FROM dbo.HISTORY tgt
INNER JOIN #updates src   
    WITH (FORCESEEK) -- NEW!
    ON tgt.[Id] = src.[Id]
    AND src.[ActiveFromDateTime] = tgt.[ActiveFromDateTime] 
    AND tgt.[Hash]  <> src.[Hash];

    Você deve obter um plano sem classificações ou hash como este:

    Plano esperado

    O Eager Table Spool é necessário para proteção de Halloween porque você está atualizando uma chave de cluster ( IsActive ).

    Você pode descobrir que esse formato de plano funciona melhor. Você não está atualizando muitas linhas.

    A classificação original foi introduzida para apresentar linhas ao operador Clustered Index Update em ordem de chave. Isso ajuda a produzir um padrão de acesso sequencial em vez de procurar no índice clusterizado para cada atualização. O plano acima depende da preservação dessa ordem chave, portanto, nenhuma classificação é necessária.

    Eu sei que você disse que está seguindo um padrão ou outro, mas muitos aspectos do seu script parecem redundantes, ineficientes ou inseguros.

    1. A tabela de histórico não possui uma restrição exclusiva na coluna de identidade.
    2. O cálculo de hash poderia usar CONCAT_WS.
    3. O cálculo de hash não usa formatos de estilo para conversões de data.
    4. A coluna HistoryRecordId salva na tabela temporária nunca é usada.
    5. Não está claro se você está salvando alguma coisa com os hashes ao comparar colunas diretamente.
    6. Sua atualização final altera a coluna-chave do cluster IsActive incondicionalmente, exigindo proteção de Halloween. Você pode tentar não fazer isso ou apenas fazê-lo quando for absolutamente necessário, talvez em uma atualização separada. Tudo isso depende do que essa coluna significa e do que o seu processo garante.
    • 7

  2. agrupe-o

    O principal problema com seu plano de consulta é o uso de uma classificação em modo de lote. A razão pela qual isso o arrasta tanto é porque, a menos que eles sejam operadores filhos de um Window Aggregate, todas as linhas terminam em um único thread:

    NOZES

    A razão pela qual seu índice na #updatestabela é ineficaz é porque ele não é usado. O SQL Server não deseja fazer 2 milhões de pesquisas para obter todas as colunas solicitadas que não fazem parte do índice não clusterizado.

    NOZES

    Você pode ter mais sorte criando um índice clusterizado na #updatestabela, que ordenaria os dados pelas colunas principais e incluiria todas as outras colunas da tabela.

    CREATE CLUSTERED INDEX 
    NCCI_#updates__Kimble_HISTORY_ForecastStatus 
ON 
    #updates 
    ([Id], ActiveFromDateTime, [Hash])
WITH
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

    No entanto! Você ainda pode obter um plano de modo em lote e provavelmente usará um Hash Join, já que esse é o único tipo de junção que o modo em lote pode usar. Como Hash Joins não preservam a ordem (Merge e certos tipos de Nested Loops preservam), você provavelmente ainda acabaria com um operador Sort.

    Suas opções seriam usar OPTION(MERGE JOIN) para forçar esse tipo de junção ou desabilitar o modo em lote para a consulta com OPTION(USE HINT('DISALLOW_BATCH_MODE')).

    • 5

  3. A última consulta parece estar aguardando principalmente o BSORT (mais fácil de ver na visualização XML): insira a descrição da imagem aqui

    Quase 4 minutos de espera. Isso aponta para a classificação em modo de lote. E, de fato, a classificação nesta consulta está em modo lote:

    insira a descrição da imagem aqui

    Também digno de nota é que o tempo da CPU é de apenas 10 segundos, enquanto o tempo real (relógio de parede) é de 46 segundos.

    Tudo isso aponta para problemas com o modo em lote. Este artigo sugere o uso do sinalizador de rastreamento 9358 e outras soluções alternativas. Mas é para o SQL Server 2016, então não tenho certeza de como funciona em 2019. Eu pessoalmente tentaria forçar a execução da consulta com nível de compatibilidade mais baixo. Significa adicionar OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'))no final da consulta. O modo em lote no rowstore (que vemos aqui) está disponível a partir do nível de compatibilidade 150, portanto, usar um modo inferior deve desativá-lo.

    Como observação lateral, o índice [IX__HISTORY_IsActive] ON dbo.HISTORY parece redundante com o índice clusterizado. Possui a mesma primeira coluna-chave e as demais estão incluídas. Eu consideraria abandoná-lo. Isso aceleraria qualquer atualização na mesa e economizaria espaço.

    • 2

relate perguntas