Observei (e reproduzi) o seguinte cenário com o SQL Server 2022.
O padrão em uso
- o código é executado via sp_executesql (nenhum procedimento armazenado está envolvido)
- A primeira consulta seleciona dados em uma tabela temporária
- Uma instrução DDL cria então um índice clusterizado na tabela temporária. A tabela temporária definitivamente NÃO é armazenável em cache-- primeiro de tudo, isso não é um módulo (sproc ou função), mas também estamos criando um índice depois que a tabela temporária é preenchida. Então eu não esperaria que estatísticas deixadas para trás em um objeto temporário em cache estivessem envolvidas aqui.
- Uma consulta seleciona dados da tabela temporária. Esta consulta obtém otimização COMPLETA a cada vez (não é um plano TRIVIAL)
Este lote pode ser executado tanto para conjuntos de dados pequenos quanto maiores, de modo que a tabela temporária pode ter apenas uma linha ou milhares de linhas.
Esse comportamento normalmente ocorre em um secundário legível. Não há armazenamento de consulta gravável e nenhum plano automático forçando como um fator.
Verifiquei que posso reproduzir o comportamento também na réplica primária. (A correção automática do plano foi instruída a ignorar a consulta e confirmei que não há imposição de plano na primária quando reproduzida.)
Script de reprodução
- Script de configuração - Eu executei isso no SQL Server 2022 CU15. Isso desativa o armazenamento de consultas e usa o nível de compatibilidade 130.
- Consulta de reprodução - Tenho executado isso por meio do SQL Query Stress para poder executá-lo facilmente e simultaneamente em um ou mais threads
- Plan Generation Num e tabelas temporárias - Uma consulta muito simples para observar o plan_generation_num em sys query stats ("Um número de sequência que pode ser usado para distinguir entre instâncias de planos após uma recompilação.") e a lista atual de tabelas temporárias
O que normalmente acontece - e o comportamento que espero
Normalmente, alterar grandes quantidades de linhas na tabela temporária entre execuções de consultas causa automaticamente recompilações, e vejo que a consulta que seleciona os dados da tabela temporária tem uma estimativa de linha correspondente às linhas na tabela temporária.
Quando isso funciona como esperado, o desempenho é bom.
Com a consulta de reprodução : se eu limpar o cache do plano e executar a consulta de reprodução 40 iterações em um único thread no SQL Query Stress, o plan_generation_number acaba sendo 82. Ao amostrar planos de consulta com sp_WhoIsActive, as estimativas de linha que consultam a tabela temporária correspondem ao número de linhas na tabela temporária, conforme esperado.
O que às vezes acontece -- e parece um bug para mim
Em raras ocasiões, vejo que um plano está em uso onde há um plano de estimativa de 1 linha para a tabela temporária, mas uma quantidade muito grande de linhas está realmente na tabela temporária. MUITAS linhas foram alteradas, mas não recompilou automaticamente:
Isso leva a um desempenho muito lento porque o plano de estimativa baixa decide usar um loop aninhado sem pré-busca, o que o torna um queimador de CPU.
Com a consulta de reprodução : Se eu limpar o cache do plano e executar a consulta de reprodução 20 iterações em 2 threads no SQL Query Stress, o plan_generation_number acaba sendo menor que 82 — varia conforme a execução, mas pode ser 72 ou 59, indicando menos recompilações. Enquanto isso está em execução, também posso amostrar ocasiões com sp_WhoIsActive em que há uma única contagem de linhas estimada, mas muito mais linhas na tabela temporária. Captura de tela:
Só consigo reproduzir isso ao executar código de reprodução em várias sessões simultâneas
Não consegui reproduzir esse comportamento com uma única sessão no SQL Server. A única maneira de reproduzir isso é configurar um bloco de código que:
- Executa pelo menos 1 iteração da instrução sp_executesql que tem 1 linha na tabela temporária
- Em seguida, executa 1 iteração da instrução sp_executesql que tem muito mais linhas na tabela temporária
Se eu executar isso em uma única sessão, não consegui reproduzir os problemas. Mas se eu executar isso simultaneamente em quatro ou cinco sessões, ocasionalmente poderei ter o problema "QUE NÃO RECOMPILA COMO DEVERIA" aparecendo. (Observação: usando o SQL Query Stress, posso reproduzir isso com apenas 2 sessões/iterações.)
Isso parece um bug para mim, estou curioso para saber se mais alguém viu. O comportamento de recompilação e estatísticas com tabelas temporárias é super complexo, então pode haver alguma nuance que estou perdendo com a forma como isso funciona com tabelas temporárias não armazenáveis em cache.
PS: Eu acho que tabelas temporárias armazenáveis em cache são geralmente melhores. Só estou tentando descobrir por que esse comportamento aconteceria em um cenário de tabela temporária não armazenável em cache neste momento.
Soluções alternativas
Após adicionar um option (recompile)à consulta, não consigo mais reproduzir a reutilização do plano de 1 linha consultando a tabela temporária. Isso é suficiente, mas estou intrigado sobre o porquê de ser necessário.