Por que adicionar um TOP 1 piora drasticamente o desempenho?

Desde que você obtenha o plano correto com o ORDER BY, talvez você possa apenas rolar seu próprio TOPoperador?

SELECT DOCUMENT_ID, COPIES, REQUESTOR, D_ID, FILE_NUMBER
FROM (
    SELECT dc.DOCUMENT_ID,
           dc.COPIES,
           dc.REQUESTOR,
           dc.D_ID,
           cj.FILE_NUMBER,
           ROW_NUMBER() OVER (ORDER BY cj.FILE_NUMBER) AS _rownum
    FROM DOCUMENT_QUEUE dc
    INNER JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
    WHERE dc.QUEUE_DATE <= GETDATE()
      AND dc.PRINT_LOCATION = 2
) AS sub
WHERE _rownum=1;

Na minha opinião, o plano de consulta ROW_NUMBER()acima deve ser o mesmo como se você tivesse um arquivo ORDER BY. O plano de consulta agora deve ter um segmento, um projeto de sequência e, finalmente, um operador de filtro, o restante deve se parecer com o seu bom plano.

Tente forçar um hash join*

SELECT TOP 1 
       dc.DOCUMENT_ID,
       dc.COPIES,
       dc.REQUESTOR,
       dc.D_ID,
       cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
INNER HASH JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
       AND dc.QUEUE_DATE <= GETDATE()
       AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

O otimizador provavelmente pensou que um loop seria melhor com o top 1 e isso faz sentido, mas na realidade não funcionou aqui. Apenas um palpite aqui, mas talvez o custo estimado desse spool esteja fora - ele usa TEMPDB - você pode ter um TEMPDB com baixo desempenho.

* Tenha cuidado com as dicas de junção , porque elas forçam a ordem de acesso à tabela do plano para corresponder à ordem escrita das tabelas na consulta (como se OPTION (FORCE ORDER)tivesse sido especificada). No link da documentação:

Isso pode não produzir nenhum efeito indesejável no exemplo, mas, em geral, pode muito bem. FORCE ORDER(implícito ou explícito) é uma dica muito poderosa que vai além de impor a ordem; ele evita a aplicação de uma ampla variedade de técnicas de otimizador, incluindo agregações parciais e reordenação.

Uma dica de OPTION (HASH JOIN) consulta pode ser menos intrusiva em casos adequados, pois isso não implica FORCE ORDER. No entanto, aplica-se a todas as junções na consulta. Outras soluções estão disponíveis.

Editar: +1 funciona nessa situação porque FILE_NUMBERé uma versão de string preenchida com zeros de um número inteiro. Uma solução melhor aqui para strings é anexar ''(a string vazia), pois anexar um valor pode afetar a ordem ou para números adicionar algo que é uma constante, mas contém uma função não determinística, como sign(rand()+1). A ideia de 'quebrar a classificação' ainda é válida aqui, só que meu método não era o ideal.

+1

Não, não quero dizer que estou concordando com nada, quero dizer isso como uma solução. Se você alterar sua consulta para ORDER BY cj.FILE_NUMBER + 1, o TOP 1se comportará de maneira diferente.

Veja bem, com o objetivo de linha pequena definido para uma consulta ordenada, o sistema tentará consumir os dados em ordem, para evitar ter um operador Sort. Ele também evitará a construção de uma tabela de hash, imaginando que provavelmente não terá muito trabalho para encontrar a primeira linha. No seu caso, isso está errado - pela espessura dessas setas, parece que é necessário consumir muitos dados para encontrar uma única correspondência.

A espessura dessas setas sugere que sua DOCUMENT_QUEUEmesa (DQ) é muito menor que sua CORRESPONDENCE_JOURNALmesa (CJ). E que o melhor plano seria, na verdade, verificar as linhas DQ até que uma linha CJ fosse encontrada. Na verdade, isso é o que o Query Optimizer (QO) faria se não tivesse esse incômodo ORDER BYlá, que é bem suportado por um índice de cobertura em CJ.

Portanto, se você descartar ORDER BYcompletamente, espero obter um plano que envolva um loop aninhado, iterando sobre as linhas em DQ, procurando em CJ para garantir que a linha exista. E com TOP 1, isso pararia depois que uma única linha fosse puxada.

Mas se você realmente precisa da primeira linha em FILE_NUMBERordem, pode induzir o sistema a ignorar esse índice que parece (incorretamente) ser tão útil, fazendo ORDER BY CJ.FILE_NUMBER+1- o que sabemos que manterá a mesma ordem de antes, mas, principalmente, o QO não. O QO se concentrará em obter todo o conjunto, para que um operador Top N Sort possa ser satisfeito. Este método deve produzir um plano que contém um operador Compute Scalar para calcular o valor para ordenação e um operador Top N Sort para obter a primeira linha. Mas à direita deles, você deve ver um belo Nested Loop, fazendo muitas buscas no CJ. E melhor desempenho do que percorrer uma grande tabela de linhas que não correspondem a nada no DQ.

O Hash Match não é necessariamente horrível, mas se o conjunto de linhas que você está retornando de DQ for muito menor que CJ (como eu esperaria que fosse), então o Hash Match vai escanear muito mais CJ do que precisa.

Observação: usei +1 em vez de +0 porque o otimizador de consulta provavelmente reconhecerá que +0 não altera nada. Claro, a mesma coisa pode se aplicar ao +1, se não agora, então em algum momento no futuro.

Eu li os resultados desta postagem e entendi o conceito de uma meta de linha etc. O que estou curioso é como posso alterar a consulta para que ela use o melhor plano

A adição OPTION (QUERYTRACEON 4138)desativa o efeito das metas de linha apenas para essa consulta, sem ser excessivamente prescritiva sobre o plano final e provavelmente será a maneira mais simples/direta.

Se adicionar esta dica resultar em um erro de permissão (obrigatório para DBCC TRACEON), você poderá aplicá-la usando um guia de plano:

Usando QUERYTRACEONem guias de plano por spaghettidba

... ou apenas use um procedimento armazenado:

Quais permissões são QUERYTRACEONnecessárias? por Kendra Little

Versões mais recentes do SQL Server oferecem opções diferentes (e indiscutivelmente melhores) para lidar com consultas que obtêm desempenho abaixo do ideal quando o otimizador é capaz de aplicar otimizações de meta de linha. O SQL Server 2016 SP1 introduziu a DISABLE_OPTIMIZER_ROWGOALdica de uso que tem o mesmo efeito que o sinalizador de rastreamento 4138. (Consulte esta postagem de blog para obter um exemplo de como usá-lo.)

Se você não estiver nessa versão, também pode considerar o uso da OPTIMIZE FORdica de consulta para obter um plano de consulta projetado para retornar todas as linhas em vez de apenas 1. A consulta abaixo retornará os mesmos resultados da pergunta, mas não ser criado com o objetivo de obter apenas 1 linha.

DECLARE @top INT = 1;

SELECT TOP (@top) dc.DOCUMENT_ID,
        dc.COPIES,
        dc.REQUESTOR,
        dc.D_ID,
        cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
JOIN CORRESPONDENCE_JOURNAL cj
    ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
WHERE dc.QUEUE_DATE <= GETDATE()
  AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER
OPTION (OPTIMIZE FOR (@top = 987654321));

Como você está fazendo um TOP(1), recomendo fazer o ORDER BYdeterminístico para começar. No mínimo, isso garantirá que os resultados sejam funcionalmente previsíveis (sempre útil para testes de regressão). Parece que você precisa adicionar DC.D_IDe CJ.CORRESPONDENCE_IDpara isso.

Ao examinar os planos de consulta, às vezes acho instrutivo simplificar a consulta: possivelmente selecione todas as linhas dc relevantes em uma tabela temporária com antecedência, para eliminar problemas com a estimativa de cardinalidade em QUEUE_DATEe PRINT_LOCATION. Isso deve ser rápido devido à baixa contagem de linhas. Você pode adicionar índices a essa tabela temporária, se necessário, sem alterar a tabela permanente.