Início / dba / Perguntas / 331776
Accepted
Morgeth888
Asked: 2023-10-03 01:16:05 +0800 CST

Por que o SQL Server não pode executar um TOP N SORT entre uma busca de índice e uma pesquisa de chave?

  • 772

Estou analisando um pequeno problema de desempenho, em que uma ferramenta otimizadora está basicamente dizendo: "ei, vá em frente e inclua todas as colunas desta tabela neste índice", o que é uma solução horrível na minha opinião.

Meu processo de pensamento me levou a pensar "por que não vou em frente e dou tudo neste índice existente para fazer sua classificação e operação TOP(N) e cortar essa enorme operação de pesquisa de chave de 100k, certamente o SQL Server pode fazer isso e mude de pesquisas de chave de 100 mil para N."

Não foi isso que eu vi, o que vi foi que nada mudou, ele ainda fazia todas as principais pesquisas e classificava depois disso. Como abaixo

insira a descrição da imagem aqui

Simplesmente remover a seleção das outras colunas que não estão no índice faz com que ele não precise de nenhuma pesquisa de chave, é claro.

Já vi muitas soluções alternativas usando CTE para contornar isso, mas estou usando o Entity Framework para esta consulta e simplesmente brincar com a consulta não é tão fácil assim.

Eu gostaria que o objetivo principal desta pergunta fosse POR QUE isso acontece? Parece uma operação trivial fazer a cláusula sort e top antes do loop de pesquisa de chave, se você puder. Não fazer isso parece uma fraqueza gritante na plataforma.

Estou perguntando por que esse comportamento existe, e não como posso melhorar o desempenho dessa consulta.

sql-server

2 respostas

  1. amor próprio

    Você provavelmente pode obter a forma do plano que deseja fazendo uma auto-junção, o que é muito mais fácil de fazer no Entity Framework do que em todas as coisas do CTE. Também é uma opção mais confiável porque os CTEs podem ser instáveis.

    Se tivermos este índice:

    CREATE INDEX 
    whatever 
ON dbo.Users 
    (Reputation, Age, CreationDate)
WITH(SORT_IN_TEMPDB = ON);

    E esta consulta:

    SELECT TOP (1000) 
    u2.*
FROM dbo.Users AS u
JOIN dbo.Users AS u2
    ON u.Id = u2.Id
WHERE u.Reputation = 2
ORDER BY 
    u.CreationDate DESC;

    O plano resultante é assim:

    NOZES

    os detalhes

    O que você deseja fazer é que uma instância da auto-junção (neste caso, o ualias) cuide da cláusula where e ordene por, e a outra referência (neste caso u2) cuide da lista de seleção.

    Você pode ver no plano de consulta que a classificação atende à meta de 1.000 linhas antes de entrar na junção.

    No caso de um plano de execução paralelo (como o da sua pergunta), você poderá ver um número um pouco maior entrando na classificação antes de ser descartado pelo TOP.

    NOZES

    Há alguns detalhes adicionais sobre por que isso está aqui:

    • 3
  2. Best Answer

    Eu gostaria que o objetivo principal desta pergunta fosse POR QUE isso acontece? Parece uma operação trivial fazer a cláusula sort e top antes do loop de pesquisa de chave, se você puder.

    Existem três razões principais:

    1. Uma pesquisa está fortemente vinculada ao seu operador pai.

      A operação lógica é GETretornar atributos de uma relação. A implementação física dessa operação lógica pode assumir várias formas:

      • Digitalizar uma tabela, índice ou visualização indexada correspondente
      • Procure em um índice, uma ou várias vezes
      • Digitalizar ou procurar(ões) mais pesquisa
      • Predicado residual na varredura, busca ou pesquisa

      Qualquer que seja a opção física escolhida, o SQL Server deve respeitar a intenção e a semântica originais do GET, incluindo o tempo de vida do bloqueio e outras garantias de consistência e invariantes internos.

      Como resultado, há poucos operadores permitidos entre uma varredura ou busca e qualquer pesquisa relacionada. Isso inclui uma classificação nas chaves de cluster introduzidas para otimizar E/S sequencial na pesquisa e um spool pronto para proteção de Halloween .

    2. Um "Top" não é um operador relacional. A maior parte do otimizador é construída sobre princípios relacionais e equivalências. Algum apoio específico foi adicionado (ou omitido deliberadamente) ao Top ao longo do tempo, mas estes ainda são uma minoria.

      Conseqüentemente, o otimizador não considera muito as colocações alternativas dos operadores principais.

    3. Um "Top N Sort" é uma reescrita pós-otimização para potencialmente executar uma classificação de seleção de substituição em vez de usar o algoritmo geral.

      Por ser uma reescrita pós-otimização, ela não está sujeita ao controle de custos e não faz parte do raciocínio do otimizador.

      A reescrita é limitada aos casos em que o operador Top físico termina imediatamente após um operador Sort no plano de execução escolhido pelo otimizador.

      Dado que o otimizador não explora muito a movimentação do Top pela árvore do plano, é fácil acabar com o Top separado do Sort (que pode se movimentar muito mais, mas não tanto quanto os verdadeiros operadores relacionais).

    Não fazer isso parece uma fraqueza gritante na plataforma.

    Bem, é. As pessoas têm escrito sobre maneiras de expressar dúvidas para obter melhores resultados há uma década ou mais. Também não é a única fraqueza.

    Por outro lado, o otimizador do SQL Server tem como objetivo encontrar rapidamente um plano de execução aparentemente razoável. Ele não tem os mesmos objetivos de um compilador otimizador encontrado em linguagens de programação, que têm muito mais liberdade e tempo para encontrar e aplicar seus truques.

    Estou usando o Entity Framework para esta consulta e simplesmente brincar com a consulta não é tão fácil assim.

    Lamento ouvir isso.

    • 3

relate perguntas