Eu tenho uma consulta simples com um agrupamento, que funcionou bem, até adicionar mais uma junção:
select
[ca].Value,
[c].cID,
[c].Name
from ReportingDB..Table1 [t1]
join MainDB..Companies [c] on
[t1].CompanyID = [c].cID
and [c].cID not in (1)
join MainDB..CompanyAttributes [ca] on -- this is the join that causes trouble
[t1].CompanyID = caCID
and caAttr = 26
group by [ca].Value, [c].cID, [c].Name
Informações:
Companies tabela é uma tabela de "pesquisa" e tem 2254 linhas, cIDé PK
CompanyAttributestem muitos para um relacionamento Companiese tem 4055 linhas
Table1tem muitos para um relacionamento com a Companiestabela e tem 3.485.150 linhas e
O plano de execução estimado não parece incomum.
- Quando tento executar a consulta, ela não termina e depois de 1 hora eu a paro, então não consigo ver o que está acontecendo com o plano de execução real
- Estatísticas de consulta ao vivo fizeram meu SSMS travar
- Se remover a cláusula "group by", ele começa a buscar linhas sem nenhum problema rapidamente. Ou quando a última junção é removida, também funciona bem - com agrupamento
- O servidor não está ocupado, tem recursos suficientes e não vejo aumento perceptível da CPU ao iniciar a consulta
- olhando para
sys.dm_exec_requests, wait_type é NULLcpu_timeelogical_readscontinua a crescer, para a sessão que executa a consulta
Qual é a causa ROOT da consulta original rodando mais de 1 h sem terminar?
Resolvi o gargalo de desempenho em si (veja minha resposta), mas não entendo o QUE exatamente faz com que a consulta original seja executada por 1 hora e não termine em um servidor decente, as tabelas consultadas não são enormes. Esperaria que a consulta original terminasse em menos de 1 minuto.
O principal problema com o plano de execução estimado mostrado é o operador Top acima do Clustered Index Scan da Tabela1. A varredura tem um predicado residual:
O otimizador tenta estimar quantas linhas ele precisará ler da varredura antes de passar no teste. A lógica que ele usa é genérica e não é particularmente sólida na minha opinião. Em particular, se não houver correspondência, a verificação será executada até a conclusão, verificando todas as 3.514.200 linhas.
Mais precisamente, essa varredura se repetirá para cada linha retornada pela varredura de Atributos da Empresa, multiplicada pelo número de linhas retornadas pela busca em Empresas. É assim que as junções de loops aninhados funcionam.
O otimizador é super otimista em encontrar correspondências em cada varredura da Tabela1. Isso faz com que esse formato de plano tenha o menor custo estimado das alternativas consideradas. A causa raiz disso é o objetivo de linha introduzido pelo operador Top.
Se você está curioso sobre de onde veio o Top (não está no texto da consulta), dê uma olhada no meu artigo relacionado Objetivos da linha, Parte 4: O padrão anti-junção anti- .
Resumindo: o otimizador introduziu um agregado local (parcial) como parte de sua pesquisa de plano. Esse agregado acabou sendo logicamente redundante e foi substituído pelo Top equivalente. Um efeito colateral infeliz do Top é introduzir uma meta de linha, reduzindo significativamente o custo estimado da verificação.
Um Topo acima de um Scan com um predicado residual (especialmente onde o scan é repetido) é um antipadrão a ser observado.
Acredito que o que você está enfrentando é semelhante ao que experimentei recentemente algumas vezes, conforme mencionado na minha pergunta DBA.StackExchange . Não sou de forma alguma um especialista no que está acontecendo, mas tentarei resumir meu entendimento.
Quando você une duas tabelas, o SQL Engine utiliza estatísticas em seus dados (com base nos campos em seus predicados) para determinar a operação mais eficiente a ser usada ao unir os dados. Muito disso depende das cardinalidades desses predicados, ou seja, quantos dados devem ser retornados por eles.
Existem três operações principais (tipos de junções internas) que o SQL Engine pode escolher usar, dependendo de quantas linhas ele espera que sua junção retorne: Nested Loop Join , Merge Join e Hash Join . Há também uma métrica interna, comumente chamada de " ponto de inflexão ", que é um corte que o SQL Engine usa (com base nas cardinalidades) para determinar quando usar uma das operações de junção mencionadas anteriormente para atender à sua consulta.
As junções de loop aninhadas geralmente são mais eficientes ao unir dois conjuntos de dados pequenos, em vez de as junções de hash serem mais eficientes para unir grandes conjuntos de dados. (Há também algumas outras características para quando um tem mais desempenho do que o outro, como se os dados já estiverem classificados no predicado de junção.) De uma perspectiva de linguagens de programação procedurais, pense em loops aninhados como um loop externo que está processando outro loop interno dentro dele e comparando cada valor do loop interno com o valor atual do loop externo, enquanto Hash Joins faz o que o nome indica e faz um hash dos dados dos predicados para fazer uma pesquisa de hash ao ingressar.
O problema que você pode estar encontrando (se semelhante ao da minha pergunta vinculada acima) é que, por algum motivo, o SQL Engine não acha que seus dados cruzam o limite do ponto de inflexão que garante a atualização de uma operação de junção de loop aninhada para uma Operação de mesclagem ou junção de hash. Eu baseei meu palpite em forçar um Hash Join usando a dica de junção no fato de que você
Table1tem quase ~ 3,5 milhões de linhas e seu plano de execução estimado está mostrando uma operação de loops aninhados ao juntá-lo aoCompanyAttributes.Presumo que quando você fizer uma das poucas coisas que mencionou que ajudam a melhorar o desempenho, se você analisar o plano de execução real, provavelmente notará que a operação de loops aninhados específica agora é substituída por uma mesclagem ou união de hash.
A menção de Erik a um problema de " objetivo de linha " me levou a encontrar alguns recursos relevantes que você também pode achar interessantes:
Row Goals Gone Rogue - Bart Duncan - Isso discute um pouco sobre as razões pelas quais um Nested Loop Join pode ser favorecido (para atingir um objetivo de linha) em vez de um Hash Join.
Definindo e identificando metas de linha em planos de execução - Paul White - Aprofunda mais as metas de linha, o que pode acioná-las e discute ainda as causas para que loops aninhados sejam favorecidos em relação a junções de hash.
Tentarei elaborar e melhorar ainda mais esta resposta quando tiver mais tempo, mas gostaria de finalmente mencionar que as dicas de junção geralmente não são uma solução ideal e devem ser usadas apenas quando não houver outra opção para atravessar o linha de chegada com ajuste de desempenho. Mas eles certamente são úteis na tentativa de depurar a causa de um problema de desempenho, como o que você está enfrentando no momento.
Descobertas três maneiras de corrigir esse gargalo de desempenho e a consulta é concluída em 1 segundo após:
OU
OU
O que NÃO entendo é por que a consulta original (e quando não há índice NC) não termina.
Ok, eu esperaria que ele fizesse uma varredura de índice clusterizado e concluísse em um minuto ou menos, a Tabela1 não é super grande (3,5 M linhas).
Mas roda 1 hora e não completa. Então, aceitarei outra resposta se alguém puder explicar a causa raiz da consulta original executando mais de 1h e não terminando