Eu tenho uma tabela temporária #terminal que contém 3 linhas
Quando executo a seguinte consulta
SELECT t.termin, ttw.tourid, twt.va_nummer_int
FROM #term AS t
INNER JOIN plinfo.t_touren_werbeflaechentermine AS ttw
ON ttw.termin = t.termin
INNER JOIN wtv.t_werbeflaechentermine AS twt
ON twt.jahr = t.jahr
AND twt.termin = t.termin
AND twt.ID_Wt = ttw.id_wt
GROUP BY t.termin, ttw.tourid, twt.va_nummer_int
Vou obter o seguinte plano de execução:
Cada tabela é unida usando um índice correspondente. Ambas as tabelas são particionadas por ps_termin(termin).
Para a primeira tabela (t_touren_werbeflaechentermine) ele faz uma eliminação de partição e lê apenas um subconjunto de linhas, enquanto para a segunda tabela (t_werbeflaechentermine) ele varre todo o índice ( jahr, termin, id_wt include (va_nummer_int)).
Então minha pergunta: por que faz uma varredura de índice (e não busca) e por que não elimina as partições para a segunda tabela.
PS: ao usar WITH (FORCESEEK)na segunda tabela, ele alterna as duas tabelas no plano de execução e faz a varredura completa do índice na primeira...
PPS: O plano de execução pode ser encontrado aqui
Por que uma verificação de índice?
É provável que o SQL Server tenha estimado um custo menor para a verificação dos dados (apenas 1,7 milhões de linhas, portanto, uma tabela relativamente pequena) em comparação com uma abordagem de busca de loop.
A varredura da
t_werbeflaechenterminetabela processará todas as1,708,658linhas e seu plano mostra que isso funciona7,347 logical reads.Estima-se o desempenho de uma busca de loop
~70K seeks(a estimativa de cardinalidade para o lado externo do loop). Se assumirmos que uma busca é uma busca binária, podemos estimar que isso terá uma complexidade de70109.4 * LOG(1708658,2), ou1,451,575.Isso é um pouco menor do que as
1,708,658linhas processadas pela varredura, mas o número de leituras lógicas será muito maior, pois cada uma das buscas (estimadas) de ~70K executará várias leituras lógicas, produzindo muito mais leituras lógicas do que a varredura escolhida. Esta pode ser a razão pela qual um plano de busca de loop produz um custo estimado mais alto e não foi selecionado.Visualizando o plano sem digitalizações
Se você quiser comparar este plano com o plano de execução que usa um loop-seek é usado para ambas as junções, você pode tentar adicionar a
OPTION (LOOP JOIN)dica de consulta à sua consulta para que ambas as junções usem uma junção de loop. Seria informativo postar este plano de execução real para comparação.Por que não há eliminação de partição?
Acredito que a eliminação de partição não ocorre
t_werbeflaechentermineporque o SQL Server não possui um algoritmo de junção de hash que executa a eliminação de partição com base nas partições observadas no lado da compilação de uma junção de hash. Isso seria uma boa otimização em alguns casos, mas que eu saiba não está disponível no otimizador atual: A eliminação de partição está disponível para o lado interno de uma junção de loop, mas não para o lado de teste de uma junção de hash (a menos que a consulta contenha um predicado explícito na coluna de partição).Para leitura adicional, o SQL Server tem o conceito de uma junção colocada, na qual a junção de hash é aplicada independentemente para cada partição de duas tabelas particionadas da mesma maneira. No entanto, essa otimização só está disponível em junções bidirecionais, portanto, sua consulta não se qualifica. Paul White descreve esta e outras considerações de junção de tabela particionada com muito mais detalhes em Melhorando o desempenho da junção de tabela particionada .