Início / dba / Perguntas / 108868
Accepted
sharptooth
Asked: 2015-08-04 07:15:51 +0800 CST

Por que DistinctSort é seguido por TopNSort em meu plano de consulta?

  • 772

Eu tenho a seguinte definição:

CREATE TABLE [dbo].[JobItems] (
    [ItemId]            UNIQUEIDENTIFIER NOT NULL,
    [ItemState]         INT              NOT NULL,
    [ItemCreationTime]  DATETIME         NULL DEFAULT GETUTCDATE(),
    [ItemPriority]      TINYINT          NOT NULL DEFAULT(0),
    [ItemRefreshTime]   DATETIME         NULL,
    -- lots of other columns
    CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
    ON [dbo].[JobItems]([ItemId] ASC);

CREATE INDEX [GetTaskToProcessIndex]
    ON [dbo].[JobItems]([ItemState], [ItemPriority], [ItemCreationTime])

e a seguinte consulta:

SELECT TOP(1) ItemId FROM JobItems
WHERE ItemState = 5 OR
   ( ( ItemState = 11 ) AND ( DATEDIFF( SECOND, ItemRefreshTime, GETUTCDATE() ) > 14 ) )
ORDER BY ItemPriority ASC, ItemCreationTime ASC

Eu executo esta consulta e inspeciono o plano de execução real e aqui está o que está acontecendo:

  1. A busca de índice é feita para recuperar itens com ItemState=5.
  2. A busca de índice é feita para recuperar itens com ItemState=11e, em seguida, para cada linha, uma busca separada é feita para recuperar ItemRefreshTimee os resultados de duas buscas são filtrados usando loops aninhados.
  3. Conjuntos de 1 e 2 contendo ItemId, ItemCreationTimee ItemPrioritysão concatenados e então...
  4. Mágico DistinctSortacontece com ORDER BY ItemId ASCe finalmente
  5. TopNSortacontece comORDER BY ItemPriority ASC, ItemCreationTime ASC

TopNSorte DistinctSortpegar algo como 32 por cento cada, então ficaria feliz em me livrar DistinctSort- nem mesmo entendo seu propósito.

O que é este mágico TopNSortque é útil DistinctSorte por que ele está lá?

sql-server execution-plan

1 respostas

  1. Best Answer

    Posso reproduzir o plano que você descreve no SQL Server 2012 (no local) executando o DDL em sua pergunta e, em seguida, mexendo nas estatísticas para que o SQL Server pense que a tabela é muito maior que a realidade.

    UPDATE STATISTICS [dbo].[JobItems] WITH ROWCOUNT = 10000000, pagecount = 10000000

    E, em seguida, executando a consulta com OPTION (MAXDOP 1, CONCAT UNION, ORDER GROUP).

    insira a descrição da imagem aqui

    Este é um plano de união de índice . O operador de concatenação implementa UNION ALL. O Distinct Sort altera a semântica para uma UNIONoperação para evitar que a mesma linha seja retornada várias vezes. (No caso de a tabela não ter uma chave de índice para atuar como um identificador de linha exclusivo, o rid físico teria sido usado aqui para evitar a duplicação incorreta de diferentes linhas que possuem os mesmos valores de coluna)

    Um exemplo de onde isso pode ser necessário está na consulta abaixo. (observe que os dois parâmetros são definidos com o mesmo valor, portanto, um plano de união de índice buscaria as mesmas linhas duas vezes)

    DECLARE @ItemState1   INT = 5
        , @ItemState2 INT = 5

SELECT ItemId
FROM   JobItems
WHERE  ItemState = @ItemState1
        OR ( ( ItemState = @ItemState2 )
             AND ( DATEDIFF(SECOND, ItemRefreshTime, GETUTCDATE()) > 14 ) )

    O Top N Sort então reclassifica os dados para implementar o TOP 1.

    No seu caso, o Distinct Sort não é logicamente necessário por vários motivos. As ramificações ItemState = 5e ItemState = 11são mutuamente exclusivas (e isso pode ser determinado em tempo de compilação) e, além disso, a TOP 1 ... ORDER BY ItemPriority ASC, ItemCreationTime ASCsemântica não seria afetada mesmo se houvesse duplicatas incorretas.

    Uma maneira alternativa de escrever a consulta (que fornece um plano melhor usando os índices para evitar qualquer classificação) é

    SELECT TOP(1) ItemId
FROM   (SELECT ItemId,
               ItemPriority,
               ItemCreationTime
        FROM   JobItems
        WHERE  ItemState = 5
        UNION ALL
        SELECT ItemId,
               ItemPriority,
               ItemCreationTime
        FROM   JobItems
        WHERE  ( ( ItemState = 11 )
                 AND ( DATEDIFF(SECOND, ItemRefreshTime, GETUTCDATE()) > 14 ) )) T
ORDER  BY ItemPriority ASC,
          ItemCreationTime ASC

    insira a descrição da imagem aqui

    Você pode considerar adicionar ItemRefreshTimeuma coluna incluída ao índice para evitar a pesquisa de chave se, na prática, algumas forem necessárias antes de localizar uma única linha que satisfaça o predicado residual.

    • 7

relate perguntas