Spoolum
No SQL Server existem vários tipos de spools. Os dois que me interessam são Table Spool s e Index spools , fora das consultas de modificação .
Consultas somente leitura, principalmente no lado interno de uma junção de loops aninhados, podem usar um spool de tabela ou índice para reduzir potencialmente a E/S e melhorar o desempenho da consulta. Esses carretéis podem ser Ansiosos ou Preguiçosos . Assim como você e eu.
Minhas perguntas são:
- Quais fatores entram na escolha de Tabela vs. Carretel de Índice
- Quais fatores influenciam na escolha entre Carretéis Ansiosos e Preguiçosos
Isso é um pouco amplo, mas acho que entendo a pergunta verdadeira e responderei de acordo. Só vou falar sobre tabela vs carretel de índice. Eu não acho correto ver lá como sendo uma escolha entre spools de tabela e de índice. Como você sabe, é possível em uma única subárvore obter um spool de índice, um spool de tabela ou um spool de índice e um spool de tabela. Acredito que geralmente é correto dizer que você obtém um spool de índice nas seguintes condições:
Você pode ver a maioria deles com demonstrações simples. Comece criando um par de heaps:
Para a primeira consulta, não há nada para procurar:
Portanto, não há motivo para o otimizador transformar a junção em uma aplicação. Você acaba com um carretel de mesa devido a razões de custo. Portanto, essa consulta falha no primeiro teste.
Para a próxima consulta, é justo esperar que o otimizador tenha um motivo para considerar uma aplicação:
Mas não é para ser:
Essa consulta falha no segundo teste. Uma explicação completa está aqui . Citando a parte mais relevante:
Posso reescrever a consulta para incentivar o otimizador a considerar uma aplicação:
Mas ainda não há spool de índice:
Essa consulta falha no terceiro teste. No SQL Server 2014, havia um limite de comprimento de chave de índice de 900 bytes. Isso foi estendido no SQL Server 2016, mas apenas para índices não clusterizados. O índice de um spool é um índice clusterizado, portanto, o limite permanece em 900 bytes . De qualquer forma, a regra de spool de índice não pode ser aplicada porque pode levar a um erro durante a execução da consulta.
Reduzir o comprimento do tipo de dados para 800 finalmente fornece um plano com um spool de índice:
O plano de spool de índice, não surpreendentemente, custa significativamente mais barato do que um plano sem spool: 89,7603 unidades contra 598,832 unidades. Você pode ver a diferença com a
QUERYRULEOFF BuildSpool
dica de consulta não documentada:Esta não é uma resposta completa, mas espero que seja um pouco do que você estava procurando.