Como o otimizador trata tabelas #temp ainda não preenchidas ao estimar a cardinalidade?

O SQL Server geralmente cria um conjunto completo de planos de execução para as instruções em um procedimento armazenado antes que o procedimento comece a ser executado.

Essa abordagem é ligeiramente modificada quando as tabelas temporárias estão presentes:

• A compilação de cada instrução que faz referência a uma tabela temporária é adiada até que a instrução seja realmente executada.
• O SQL Server oferece suporte à recompilação em nível de instrução desde a versão 2005. Somente a(s) instrução(ões) afetada(s) é(ão) recompilada(s); o restante dos planos do procedimento não são afetados.
• Compilação adiada significa que o SQL Server conhece a cardinalidade de tempo de execução da tabela temporária durante a otimização.
• O SQL Server também pode criar estatísticas automaticamente para dar suporte à consulta que está sendo compilada. Isso acontece durante a compilação e, tecnicamente, resulta em uma recompilação.
• O plano de execução construído, portanto, se beneficia de informações precisas de cardinalidade e objetos estatísticos criados automaticamente (se essa opção estiver ativada).

Da documentação sobre Recompilando Planos de Execução :

Nas versões do SQL Server anteriores a 2005, sempre que uma instrução em um lote causa recompilação, todo o lote, seja enviado por meio de um procedimento armazenado, gatilho, lote ad-hoc ou instrução preparada, era recompilado. A partir do SQL Server 2005 (9.x), somente a instrução dentro do lote que aciona a recompilação é recompilada. Além disso, há tipos adicionais de recompilações no SQL Server 2005 (9.x) e posterior devido ao seu conjunto de recursos expandido.

A recompilação em nível de instrução beneficia o desempenho porque, na maioria dos casos, um pequeno número de instruções causa recompilações e suas penalidades associadas, em termos de tempo de CPU e bloqueios. Essas penalidades são, portanto, evitadas para as outras instruções no lote que não precisam ser recompiladas.

e da Compilação Diferida de Variáveis ​​de Tabela :

Com a compilação adiada de variável de tabela, a compilação de uma instrução que faz referência a uma variável de tabela é adiada até a primeira execução real da instrução. Esse comportamento de compilação adiada é idêntico ao comportamento de tabelas temporárias.

Esse é o esboço básico, mas não toda a história.

Como já apontado em outra resposta , tabelas temporárias em procedimentos armazenados também podem ser armazenadas em cache para reutilização . Esse armazenamento em cache pode melhorar significativamente o desempenho e a escalabilidade, mas complica as condições para recompilação do plano e como as informações estatísticas são mantidas.

Descrevo essas complexidades adicionais para o leitor interessado (e avançado) em Cache de Tabela Temporária Explicado e Cache de Tabela Temporária em Procedimentos Armazenados .

Grosso modo, as estimativas de cardinalidade para a criação de estatísticas em tabelas #temp seguem as de tabelas regulares e não temporárias. Dito isto, como Paul White aponta aqui , tabelas temporárias dentro de procedimentos armazenados podem sofrer com valores de estatísticas em cache.

Na página acima, a conclusão mais importante é:

Tabelas temporárias em procedimentos armazenados têm várias características inesperadas:

• Objetos temporários podem ser armazenados em cache nas execuções, apesar das instruções CREATE e DROP explícitas
• As estatísticas associadas a um objeto temporário em cache também são armazenadas em cache
• As estatísticas podem estar 100% erradas em comparação com o conteúdo atual de uma tabela temporária
• Uma TRUNCATE TABLE implícita no final do procedimento dobra o impacto esperado nos contadores de modificação de coluna
• É possível atualizar as estatísticas em cache antes da instrução que fez com que elas fossem criadas
• OPTION (RECOMPILE) altera a maneira como as modificações de coluna são transportadas entre as chamadas de procedimento
• UPDATE STATISTICS manual não é suficiente para forçar uma recompilação

Sugiro dar uma olhada no post para entender completamente como você pode ser afetado pelo cache de tabela temporária.