Estou fazendo esta pergunta para entender melhor o comportamento do otimizador e entender os limites em torno dos spools de índice. Suponha que eu coloque inteiros de 1 a 10000 em um heap:
CREATE TABLE X_10000 (ID INT NOT NULL);
truncate table X_10000;
INSERT INTO X_10000 WITH (TABLOCK)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
E force uma junção de loop aninhado com MAXDOP 1:
SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID = b.ID
OPTION (LOOP JOIN, MAXDOP 1);
Esta é uma ação bastante hostil para o SQL Server. As junções de loop aninhadas geralmente não são uma boa opção quando ambas as tabelas não têm índices relevantes. Aqui está o plano:
A consulta leva 13 segundos na minha máquina com 100000000 linhas buscadas no spool da tabela. No entanto, não vejo por que a consulta deve ser lenta. O otimizador de consulta tem a capacidade de criar índices em tempo real por meio de spools de índice . Esta consulta parece ser um candidato perfeito para um spool de índice.
A consulta a seguir retorna os mesmos resultados que a primeira, tem um spool de índice e termina em menos de um segundo:
SELECT *
FROM X_10000 a
CROSS APPLY (SELECT TOP (9223372036854775807) b.ID FROM X_10000 b WHERE a.ID = b.ID) ca
OPTION (LOOP JOIN, MAXDOP 1);
Essa consulta também possui um spool de índice e termina em menos de um segundo:
SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID >= b.ID AND a.ID <= b.ID
OPTION (LOOP JOIN, MAXDOP 1);
Por que a consulta original não tem um spool de índice? Existe algum conjunto de dicas documentadas ou não documentadas ou sinalizadores de rastreamento que fornecerão um spool de índice? Encontrei esta pergunta relacionada , mas ela não responde totalmente à minha pergunta e não consigo fazer com que o sinalizador de rastreamento misterioso funcione para essa consulta.
Como você sabe, a pesquisa do otimizador não é exaustiva. Ele tenta coisas que fazem sentido no contexto e que frequentemente pagam dividendos em consultas reais. Forçar uma junção de loop entre duas tabelas de heap não indexadas de coluna única não é um cenário desse tipo. Dito isso, seguem alguns detalhes:
O SQL Server gosta de transformar se aplica a junções antecipadamente, porque conhece mais truques com junções. Mais tarde, ele pode explorar a conversão da junção de volta para uma aplicação. A diferença entre os dois sendo parâmetros correlacionados (referências externas). Aplica-se quando há um índice adequado no lado interno. Seu exemplo não tem índices, então o otimizador não é persuadido a explorar a tradução para um aplicativo.
Uma junção simples (não aplicável) tem o predicado de junção no operador de junção em vez de referências externas. A otimização de spool para uma não aplicação normalmente é um spool de tabela preguiçoso, pois não há predicado no lado interno, apenas na junção.
O otimizador não considera a construção de um índice em tempo real para permitir uma aplicação; em vez disso, a sequência de eventos geralmente é o inverso: transforme para aplicar porque existe um bom índice.
Às vezes, você pode incentivar uma aplicação em vez de uma junção usando
APPLYa sintaxe em sua consulta. O sinalizador de rastreamento não documentado 9114 pode ajudar nisso, dissuadindo o otimizador de traduzir uma aplicação lógica para uma junção inicial. Por exemplo:Um spool de índice é favorecido para aplicação porque a referência externa significa que a seleção é aplicada no lado interno da junção. Você verá isso frequentemente,
SelToIndexOnTheFlymas existem outros caminhos. Veja meu artigo The Eager Index Spool and The Optimizer .