Eu tenho duas tabelas com colunas de chave identicamente nomeadas, digitadas e indexadas. Um deles possui um índice clusterizado exclusivo , o outro possui um índice não exclusivo .
A configuração do teste
Script de configuração, incluindo algumas estatísticas realistas:
DROP TABLE IF EXISTS #left;
DROP TABLE IF EXISTS #right;
CREATE TABLE #left (
a char(4) NOT NULL,
b char(2) NOT NULL,
c varchar(13) NOT NULL,
d bit NOT NULL,
e char(4) NOT NULL,
f char(25) NULL,
g char(25) NOT NULL,
h char(25) NULL
--- and a few other columns
);
CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h)
UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000;
CREATE TABLE #right (
a char(4) NOT NULL,
b char(2) NOT NULL,
c varchar(13) NOT NULL,
d bit NOT NULL,
e char(4) NOT NULL,
f char(25) NULL,
g char(25) NOT NULL,
h char(25) NULL
--- and a few other columns
);
CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h)
UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000;
A reprodução
Quando eu uno essas duas tabelas em suas chaves de cluster, espero uma junção MERGE de um para muitos, assim:
SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
l.a=r.a AND
l.b=r.b AND
l.c=r.c AND
l.d=r.d AND
l.e=r.e AND
l.f=r.f AND
l.g=r.g AND
l.h=r.h
WHERE l.a='2018';
Este é o plano de consulta que eu quero:
(Não importa os avisos, eles têm a ver com estatísticas falsas.)
No entanto, se eu alterar a ordem das colunas na junção, assim:
SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
l.c=r.c AND -- used to be third
l.a=r.a AND -- used to be first
l.b=r.b AND -- used to be second
l.d=r.d AND
l.e=r.e AND
l.f=r.f AND
l.g=r.g AND
l.h=r.h
WHERE l.a='2018';
... isto acontece:
O operador Sort parece ordenar os fluxos de acordo com a ordem declarada da junção, ou seja c, a, b, d, e, f, g, h, que adiciona uma operação de bloqueio ao meu plano de consulta.
Coisas que eu olhei
- Eu tentei alterar as colunas para
NOT NULL, mesmos resultados. - A tabela original foi criada com
ANSI_PADDING OFF, mas criá-la comANSI_PADDING ONnão afeta este plano. - Eu tentei
INNER JOINem vez deLEFT JOIN, nenhuma alteração. - Eu descobri em um 2014 SP2 Enterprise, criei uma reprodução em um 2017 Developer (CU atual).
- A remoção da cláusula WHERE na coluna de índice principal gera o bom plano, mas afeta os resultados.. :)
Por fim, chegamos à questão
- Isso é intencional?
- Posso eliminar a classificação sem alterar a consulta (que é o código do fornecedor, então prefiro não...). Eu posso mudar a tabela e os índices.
É por design, sim. Infelizmente, a melhor fonte pública para essa afirmação foi perdida quando a Microsoft retirou o site de comentários do Connect, eliminando muitos comentários úteis dos desenvolvedores da equipe do SQL Server.
De qualquer forma, o design atual do otimizador não procura ativamente evitar ordenações desnecessárias per se . Isso é mais frequentemente encontrado com funções de janelas e similares, mas também pode ser visto com outros operadores que são sensíveis à ordenação e, em particular, à ordenação preservada entre operadores.
No entanto, o otimizador é muito bom (em muitos casos) em evitar ordenação desnecessária, mas esse resultado normalmente ocorre por outras razões além de tentar agressivamente diferentes combinações de ordenação. Nesse sentido, não é tanto uma questão de 'espaço de busca', mas sim das interações complexas entre os recursos do otimizador ortogonal que demonstraram aumentar a qualidade geral do plano a um custo aceitável.
Por exemplo, a classificação muitas vezes pode ser evitada simplesmente combinando um requisito de ordenação (por exemplo, nível superior
ORDER BY) a um índice existente. Trivialmente, no seu caso, isso pode significar adicionar,ORDER BY l.a, l.b, l.c, l.d, l.e, l.f, l.g, l.h;mas isso é uma simplificação excessiva (e inaceitável porque você não deseja alterar a consulta).Mais geralmente, cada grupo de memorandos pode ser associado a propriedades necessárias ou desejadas, que podem incluir ordenação de entrada. Quando não há razão óbvia para impor uma ordem específica (por exemplo, para satisfazer um
ORDER BY, ou para garantir resultados corretos de um operador físico sensível à ordem), há um elemento de 'sorte' envolvido. Eu escrevi mais sobre as especificidades disso no que diz respeito à junção de mesclagem (no modo união ou junção) em Evitar classificações com concatenação de junção de mesclagem . Muito disso vai além da área de superfície suportada do produto, portanto, trate-o como informativo e sujeito a alterações.No seu caso particular, sim, você pode ajustar a indexação como sugere jadarnel27 para evitar as ordenações; embora haja poucas razões para realmente preferir uma junção de mesclagem aqui. Você também pode sugerir uma escolha entre junção física de hash ou loop
OPTION(HASH JOIN, LOOP JOIN)usando um Guia de plano sem alterar a consulta, dependendo do seu conhecimento dos dados e da compensação entre o melhor, o pior e o desempenho de caso médio.Por fim, como curiosidade, observe que os tipos podem ser evitados com um simples
ORDER BY l.b, ao custo de uma junção de mesclagem muitos para muitos potencialmente menos eficientebsozinha, com um resíduo complexo. Menciono isso principalmente como uma ilustração da interação entre os recursos do otimizador que mencionei anteriormente e a maneira como os requisitos de nível superior podem se propagar.Se você puder alterar os índices, alterar a ordem do índice
#rightpara corresponder à ordem dos filtros na junção remove a classificação (para mim):Surpreendentemente (para mim, pelo menos), isso resulta em nenhuma consulta terminando com uma classificação.
Observando a saída de alguns sinalizadores de rastreamento estranhos , há uma diferença interessante na estrutura final do Memo:
Como você pode ver no "Root Group" na parte superior, ambas as consultas têm a opção de usar um Merge Join como a principal operação física para executar essa consulta.
Boa consulta
A junção sem a classificação é orientada pelo grupo 29 opção 1 e grupo 31 opção 1 (cada um dos quais são varreduras de intervalo nos índices envolvidos). Ele é filtrado pelo grupo 27 (não mostrado), que é a série de operações de comparação lógica que filtram a junção.
Consulta incorreta
Aquele com a ordenação é orientado pelas (novas) opções 3 que cada um desses dois grupos (29 e 31) possui. A opção 3 realiza uma classificação física nos resultados das varreduras de intervalo mencionadas anteriormente (opção 1 de cada um desses grupos).
Por quê?
Por algum motivo, a opção de usar 29.1 e 31.1 diretamente como fontes para a junção de mesclagem não está disponível para o otimizador na segunda consulta. Caso contrário, acho que seria listado no grupo raiz entre as outras opções. Se estivesse disponível, definitivamente os escolheria em vez das operações de classificação massivamente mais caras.
Só posso concluir que:
Espero que alguém possa aparecer e explicar por que a classificação é necessária, mas achei que a diferença no edifício Memo era interessante o suficiente para postar como uma resposta.