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.
Cenário
Digamos que duas sessões A e B executam sua reprodução aproximadamente ao mesmo tempo.
Ambos executam o
sp_executesqllote preparado com uma linha. Nada especialmente interessante acontece aqui.Ambas as sessões passam para a segunda execução com um número muito maior de linhas.
Digamos que a sessão A comece primeiro. Como de costume, ela usa um contexto de execução em cache (MXC) ou deriva um novo do plano em cache (CP).
Lembre-se, um CP é para todo o lote e pode, portanto, conter várias instruções e planos executáveis, cada um dos quais pode ser recompilado independentemente (recompilação em nível de instrução). Tabelas temporárias são referenciadas por nome no CP para que possam ser reutilizadas. O CP é uma estrutura pública e reentrante.
Um MXC é uma cópia leve do CP usado por uma única sessão com todos os detalhes para uma execução específica preenchidos. Esses detalhes incluem valores de variáveis e parâmetros locais, IDs de tabelas temporárias (não nomes) e similares.
O MXC é o biscoito específico que você vai comer agora, modelado a partir do cortador de biscoitos CP, por assim dizer.
Segundo
Enquanto isso, a sessão B também começa na segunda execução de lote preparada. Ela também usa um MXC em cache (ou deriva um novo).
De volta à sessão A. Tendo criado sua tabela e índice temporários de uma forma interessante, mas sem incidentes, ele passa a executar o
SELECTem questão. A sessão A decide que uma recompilação de nível de instrução está em ordem , faz isso, executa oSELECT(com contagens de linhas precisas) e finaliza.A Sessão B agora faz praticamente a mesma coisa, mas decide que uma recompilação não é necessária . Ela continua com o MXC não modificado com base no caso de uma linha e você vê seu problema se manifestar. Mas por quê?
Recompilação
Bem,
SELECTna verdade, o não é recompilado devido às estatísticas atualizadas. Ele nunca é, nem para a sessão A ou B. Essas estatísticas são novas e não modificadas a cada vez, no que diz respeito a elas. Afinal, é uma tabela temporária nova e sem cache.Agora, o
SELECTde fato recompila, é claro, e o motivo declarado indica "estatísticas alteradas". A explicação é que mesmo quando nenhuma estatística interessante é encontrada (ou quando não são encontradas alterações, como aqui), o SQL Server ainda realiza mais um teste antes de decidir que uma recompilação não é necessária.Esse teste final é 'cruzamento de limite' com base somente no número total de linhas na tabela. Citando Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 :
A sessão B sabe quantas linhas ela tem em sua tabela temporária local. Isso é muito maior que um, então por que o teste de limite não causa uma recompilação? Há um motivo, mas isso vai me tomar um parágrafo ou dois.
Detalhes
Pode haver muitas execuções simultâneas de um CP via MXCs. Esta é apenas a situação de rotina de um plano em cache sendo executado de várias sessões ao mesmo tempo.
Se cada sessão comparasse a contagem de linhas atual conhecida para uma tabela com a contagem armazenada em cache em seu MXC privado, um número excessivo de recompilações resultaria. Cada execução simultânea veria o limite excedido, executaria uma recompilação de nível de instrução e substituiria o plano de instrução atual mantido no CP.
Pior, o plano da declaração resultante provavelmente seria o mesmo para cada recompilação porque tabelas permanentes tendem a não mudar extremamente rápido (dentro do prazo de uma única recompilação em nível de declaração, de qualquer forma). No geral, esse esquema seria altamente ineficiente e desperdiçador no caso de tabelas não temporárias.
Então, em vez disso, a primeira execução simultânea realiza sua recompilação e atualiza o CP com o novo valor de cardinalidade. As outras execuções simultâneas então comparam a cardinalidade conhecida com esse valor atualizado e é improvável que causem uma nova recompilação de declaração como resultado.
Além disso, como essa é uma recompilação de otimalidade de plano (não uma recompilação de correção de plano), as outras execuções continuam com o plano em cache que já tinham. Elas pegarão o novo plano de declaração na próxima vez.
Inconveniência temporária
Esse arranjo não é muito adequado para tabelas temporárias porque cada sessão tem uma "cópia" diferente da tabela temporária CP comum, com contagens de linhas legitimamente muito diferentes.
Vamos ver o que acontece:
A sessão B compara sua contagem de linhas de tabela temporária conhecida (grande) com o valor atual armazenado no CP. Mas esse valor de CP acabou de ser atualizado (agora também grande) pela sessão A após executar sua recompilação de nível de declaração!
Então, os números de limite do MXC da sessão B e do CP compartilhado correspondem e nenhum motivo de recompilação é determinado como resultado. Você acaba com o plano de estimativa de uma linha (do MXC da sessão B que não foi recompilado) encontrando muito mais linhas na instância atual da tabela temporária.
Não é o ideal
Agora, essa pode não ser uma situação ideal.
Pode-se argumentar que tabelas temporárias (somente!) devem verificar sua cardinalidade atual conhecida em relação ao valor armazenado em cache do MXC (não o CP compartilhado), porque nenhuma outra sessão pode afetar sua tabela temporária privada.
Ok, mas se recompilarmos, onde colocamos o novo plano de declaração? Pode ser errado substituir a versão compartilhada única no CP porque outras sessões têm tabelas temporárias com contagens muito diferentes e podem precisar de planos diferentes para um bom desempenho. Quem pode dizer qual tabela temporária é melhor e mais representativa do caso comum? Se jogarmos o novo plano fora, acabamos de aplicar um tipo estranho de dica de recompilação sem que ninguém nos peça.
Esse enigma não surge com tabelas permanentes porque há apenas uma delas, com uma contagem de linhas bastante estática.
Alguém pode argumentar que o ID da sessão pode ser adicionado à chave de cache do CP, para que cada sessão tenha seu próprio plano personalizado, assim como acontece com tabelas temporárias criadas fora do lote em que são referenciadas. Bem, não, obrigado; tais casos já produzem poluição de cache de plano suficiente do jeito que estão. E não se esqueça de que isso se aplicaria a todo o lote de instruções, não apenas ao complicado.
Ou talvez tabelas temporárias devam tratar a recompilação de 'otimalidade' como se fosse uma recompilação de 'correção', significando que elas usariam o plano de declaração atualizado no CP. Talvez, mas elas não fazem isso, e provavelmente há muitos casos especiais para tabelas temporárias como estão. Ainda é provável que acabe como uma forma diferente do problema de sensibilidade de parâmetros em qualquer caso.
Conclusão
É um problema de tempo. Uma sessão atualiza o limite compartilhado do CP e um plano de nível de declaração recompilado, enquanto a outra sessão tem um valor de limite MXC em cache que não difere suficientemente (ou não difere em nada, neste caso) do valor atualizado. Isso faz com que a segunda sessão reutilize de forma subótima o plano de declaração existente como armazenado em cache em seu MXC privado.
A melhor solução parece ser adicionar uma
OPTION (RECOMPILE)dica local na declaração problemática, como você já descobriu.Sim, você acaba recompilando essa declaração com mais frequência do que o estritamente necessário, mas talvez isso seja melhor do que o desastre ocasional de desempenho causado pelo cenário acima. Neste caso, certamente é. O tempo de recompilação é baixo, e a declaração é executada por um período de tempo decente em comparação.