Por que o otimizador escolhe loops aninhados sobre junções de mesclagem aqui?

Por que funciona assim e o que devo fazer para obter duas junções de mesclagem?

Com três referências de tabela (o mínimo necessário), a consulta se qualifica para o estágio de Processamento de Transação (também conhecido como pesquisa 0) de otimização baseada em custo.

Esta etapa é voltada para consultas OLTP, que normalmente se beneficiam de uma estratégia de navegação (baseada em índice). A junção de loops aninhados é o principal tipo de junção física disponível (hash e mesclagem são considerados apenas se nenhum plano de loops aninhado válido puder ser encontrado neste estágio).

Se este estágio encontrar um plano de baixo custo (bom o suficiente), a otimização baseada em custo para por aí. Isso evita gastar mais tempo em otimização do que podemos esperar economizar em relação à melhor solução encontrada até agora. Se o custo exceder um limite, o otimizador passará para as fases Plano rápido (pesquisa 1), Plano rápido paralelo e Otimização completa (pesquisa 2).

A consulta com duas referências de tabela não se qualificou para Processamento de Transação e foi direto para o Quick Plan , onde as junções de mesclagem e hash estão disponíveis.

Veja minha série Deep Dive do Query Optimizer para obter mais informações.

Sem inner merge joindica.

Se você absolutamente precisar sugerir um tipo de junção física, prefira fortemente OPTION (MERGE JOIN). Isso permite que o otimizador ainda considere alterar a ordem de junção.

Dicas de junção como INNER MERGE JOINvem com um implícito OPTION (FORCE ORDER), que limita severamente a liberdade do otimizador, com consequências que a maioria das pessoas (incluindo especialistas) não aprecia.

Eu acho que o motivo é porque você não tem dados suficientes em suas tabelas, então o SQL Server não escolhe o plano BEST , ele escolhe o plano Good Enough para executar sua consulta.

eu tento seguir

create table #a (a int not null, primary key (a asc))
create table #b (b int not null, primary key (b asc))
create table #c (c int not null, primary key (c asc))

insert into #a (a)
select h+10*g
from  
(values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))g(g)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))h(h)


insert into #b (b)
select a from #a where a % 5 > 0

insert into #c (c)
select a from #a where a % 4 > 0

Agora se eu correr

select *
from #a a
inner join #b b on a = b
inner join #c c on a = c

Aqui está o plano de execução

Se eu usar a dica de junção de mesclagem, recebo o seguinte plano de execução.

select *
from #c c
inner merge join #a a on a = c
inner merge join #b b on a = b

Portanto, é claro que a junção de mesclagem será melhor neste caso. O custo estimado da subárvore é menor para a consulta com junções de mesclagem, mas ambas as consultas são rápidas o suficiente, ambas fazem apenas 12 leituras lógicas, então o SQL Server decide que a junção NESTED LOOP também é uma boa solução.

Não vamos adicionar mais dados às nossas tabelas.

create table #a (a int not null, primary key (a asc))
create table #b (b int not null, primary key (b asc))
create table #c (c int not null, primary key (c asc))

insert into #a (a)
select h+10*g + 100*f + 1000*e+ 10000*z + 100000*y + 1000000*x
from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))x(x)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))y(y)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))z(z)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))e(e)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))f(f)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))g(g)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))h(h)


insert into #b (b)
select a from #a where a % 5 > 0

insert into #c (c)
select a from #a where a % 4 > 0

Agora temos 10 milhões de dados em #a, 8 milhões em #b e 6 milhões em #c. Se eu executar a consulta original sem nenhuma dica, recebo junções de mesclagem conforme o esperado.

select *
from #a a
inner join #b b on a = b
inner join #c c on a = c

Se uma entrada de junção for pequena (menos de 10 linhas) e a outra entrada de junção for bastante grande e indexada em suas colunas de junção, uma junção de loops aninhados de índice será a operação de junção mais rápida porque requer menos E/S e menos comparações.

Se as duas entradas de junção não forem pequenas, mas forem classificadas em sua coluna de junção (por exemplo, se tiverem sido obtidas por varredura de índices classificados), uma junção de mesclagem será a operação de junção mais rápida.

As junções de hash podem processar com eficiência entradas grandes, não classificadas e não indexadas.

Conceitos avançados de ajuste de consulta

Tipos de junção LOOP, HASH e MERGE

Noções básicas sobre associações físicas do SQL Server

O otimizador escolhe entre mesclagem/loop aninhado/junção de hash com base nas estatísticas existentes, tamanho das tabelas e presença de índices. Em geral, o loop aninhado é preferível se as entradas forem muito menores que as outras, e ambas estiverem indexadas na coluna de junção, a mesclagem será melhor se o tamanho das duas entradas for bastante igual e indexado. O preenchimento de tabelas envolvidas com mais valores (algumas centenas de milhares de linhas) provavelmente inclinará o otimizador para escolher o algoritmo de junção de mesclagem. Você pode encontrar mais detalhes sobre como ele é implementado no SQLServer aqui .
Você também pode tentar dicas (digamos FORCE ORDER), mas mesmo que funcione uma vez, não é garantido que sempre gerará o mesmo plano .

Além disso, tenha em mente que a tarefa do otimizador não é encontrar o melhor plano, mas devolver o plano que é bom o suficiente em tempo hábil.