Quais fatores de custo entram no otimizador escolhendo diferentes tipos de carretéis?

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:

1. O otimizador de consulta tem um motivo para transformar uma junção em uma aplicação
2. O otimizador de consulta realmente executa a transformação para aplicar
3. O otimizador de consulta usa a regra para adicionar um spool de índice (no mínimo, o spool de índice deve ser seguro para uso)
4. O plano com o spool de índice está selecionado

Você pode ver a maioria deles com demonstrações simples. Comece criando um par de heaps:

DROP TABLE IF EXISTS dbo.X_10000_VARCHAR_901;
CREATE TABLE dbo.X_10000_VARCHAR_901 (ID VARCHAR(901) NOT NULL);

INSERT INTO dbo.X_10000_VARCHAR_901 WITH (TABLOCK)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;


DROP TABLE IF EXISTS dbo.X_10000_VARCHAR_800;
CREATE TABLE dbo.X_10000_VARCHAR_800 (ID VARCHAR(800) NOT NULL);

INSERT INTO dbo.X_10000_VARCHAR_800 WITH (TABLOCK)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

Para a primeira consulta, não há nada para procurar:

SELECT *
FROM dbo.X_10000_VARCHAR_901 a
CROSS JOIN dbo.X_10000_VARCHAR_901 b
OPTION (MAXDOP 1);

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:

SELECT *
FROM dbo.X_10000_VARCHAR_901 a
INNER JOIN dbo.X_10000_VARCHAR_901 b ON a.ID = b.ID 
OPTION (LOOP JOIN, MAXDOP 1);

Mas não é para ser:

Essa consulta falha no segundo teste. Uma explicação completa está aqui . Citando a parte mais relevante:

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.

Posso reescrever a consulta para incentivar o otimizador a considerar uma aplicação:

SELECT *
FROM dbo.X_10000_VARCHAR_901 a
INNER JOIN dbo.X_10000_VARCHAR_901 b ON a.ID >= b.ID AND a.ID <= b.ID
OPTION (MAXDOP 1);

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 BuildSpooldica de consulta não documentada:

Esta não é uma resposta completa, mas espero que seja um pouco do que você estava procurando.