Início / dba / Perguntas / 23773
Accepted
peter
Asked: 2012-09-06 18:27:34 +0800 CST

O otimizador não está escolhendo o plano de união de índice

  • 772

Por que a consulta a seguir é lenta?

select count(*) 

from [dbo].[mt_dispatch_link] 
   , [dbo].[_mt_dispatch] [_mt_dispatch]

where   (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id 
     and mt_dispatch_link.dispatch_id_1 = _mt_dispatch.dispatch_id)

   or   (mt_dispatch_link.contract_id_2 = _mt_dispatch.contract_id 
     and mt_dispatch_link.dispatch_id_2 = _mt_dispatch.dispatch_id)

insira a descrição da imagem aqui

Demora mais de 10 minutos e, em seguida, costumo pará-lo nesse ponto. Minha pergunta é mais sobre como entender o plano de consulta.

Olhando para o plano de consulta, posso ver que a varredura de índice clusterizado inferior está retornando cerca de 250.000 registros, mas o custo é 0% e está sendo colocado em uma tabela temporária.

A varredura de índice superior é de cerca de 25.000 registros.

Mas o custo de 95% vem da junção aninhada. Que conclusões devo tirar disso?

O plano de consulta acima mostra duas varreduras de índice, isso significa que está fazendo 25.000 + 250.000 varreduras de índice ou significa que está fazendo 25.000 * 250.000 varreduras de índice?

Se eu mudar a consulta para isso (adicionando FORCESEEK):

select count(*) 

from [dbo].[mt_dispatch_link] 
   , [dbo].[_mt_dispatch] [_mt_dispatch] 

    WITH (FORCESEEK)

where   (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id 
     and mt_dispatch_link.dispatch_id_1 = _mt_dispatch.dispatch_id)

   or   (mt_dispatch_link.contract_id_2 = _mt_dispatch.contract_id 
     and mt_dispatch_link.dispatch_id_2 = _mt_dispatch.dispatch_id)

Acabo com um plano muito melhor e a consulta é executada instantaneamente:

insira a descrição da imagem aqui

Executei estatísticas de atualização em ambas as tabelas. Não corrigiu infelizmente. O design da tabela não é muito bom, então acho que o SQL Server realmente não entende e, portanto, apresenta um plano de consulta ruim. Mais algumas informações sobre o design da tabela em Como otimizar a consulta .

Por que o otimizador de consulta não apresenta o plano ideal?

sql-server optimization

1 respostas

  1. Best Answer

    O otimizador nem sempre considera planos de união de índice (como o mostrado em seu segundo gráfico) para resolver disjunções ( ORpredicados), a menos que uma dica FORCESEEKou INDEXseja especificada. Esta é uma heurística* baseada em algumas considerações práticas:

    1. A união do índice geralmente não é uma boa seleção de plano para consultas gerais.
    2. O número de maneiras pelas quais os índices podem ser combinados cresce exponencialmente.

    O uso de uma dica altera a forma como o otimizador pesquisa o espaço de planos possíveis. Desativa algumas das heurísticas gerais e segue uma estratégia mais orientada para objetivos.

    O objetivo primário usual do otimizador é encontrar um bom plano rapidamente. Ele não procura exaustivamente o 'melhor' plano (mesmo consultas relativamente simples poderiam levar anos para serem compiladas se assim fosse).

    Junções com várias condições separadas por ORtêm sido problemáticas há muito tempo. Ao longo dos anos, o otimizador adicionou novos truques, como convertê-los em UNIONformas equivalentes, mas as transformações disponíveis são limitadas, por isso é muito fácil se soltar.

    No que diz respeito ao plano de consulta:

    1. A primeira linha do DispatchLink causa uma verificação completa da tabela Dispatch
    2. O resultado da verificação é armazenado em uma tabela de trabalho tempdb interna (o Table Spool)
    3. A junção verifica cada linha da tabela de trabalho em relação ao ORpredicado completo
    4. A próxima linha é buscada no DispatchLink e o processo é repetido a partir da etapa 3

    Se houver 25.000 linhas na tabela Dispatch Link, o spool será totalmente verificado 25.000 vezes. É claro que isso é um desastre (e sem interseção de índice, o melhor que o otimizador pode fazer é executar tudo em vários encadeamentos).

    Os custos percentuais nos planos de consulta são apenas estimativas do otimizador . Eles nunca refletem os custos reais de execução e estão sujeitos ao modelo do otimizador e geralmente têm pouca semelhança com o custo 'verdadeiro' de execução do plano em seu hardware específico.

    Os números de custeio existem para serem informativos, mas não devem ser interpretados literalmente. O modelo específico que o otimizador usa produz planos muito bons para a maioria das consultas na maioria dos sistemas em todo o mundo - isso não significa que o modelo se aproxime da realidade de alguém, apenas funciona razoavelmente bem na prática.

    Alterar o design para que os pares (Despacho, Contrato) sejam armazenados em linhas, em vez de repetidos nas colunas, fará com que todo o problema de interseção de índice desapareça. Projetos relacionais com restrições e índices úteis quase sempre tiram o melhor proveito do otimizador.

    * Isso pode ser substituído pelo sinalizador de rastreamento não documentado 8726

    • 13

relate perguntas