Eu tenho um servidor executando o SQL Server Enterprise 2016 SP2 e notei que a cada minuto várias centenas de planos de consulta são invalidados. Eu corro sp_BlitzCache @expertmode=1para verificar a coluna 'Created At' para ver quando o plano é gerado.
A cada minuto, cerca de 500 planos são recriados e isso continua o dia inteiro e são sempre planos diferentes que são recompilados.
Quando uso o DMV sys.dm_db_index_usage_statspara verificar as user_updates vejo que acontecem entre 10.000 e 1.000.000 atualizações nos índices. Algumas tabelas têm mais de 10.000.000 registros, mas a maioria delas tem entre 100.000 e 2.000.000 registros.
Tanto quanto sei, um plano de consulta é invalidado nas seguintes circunstâncias:
- Modificações feitas na definição de tabela/visualização por ALTER TABLE/ALTER VIEW
- Alterações feitas em quaisquer índices usados pelo plano de execução
- Atualizações das estatísticas utilizadas pelo plano de execução (manual ou automático)
- Descartando um índice usado pelo queryplan
- sp_recompile / com recompilação
- Grande número de alterações na tabela
- Adicionando/eliminando um gatilho em uma tabela usada pelo plano de execução
- Pressão de memória
Todas as consultas usam KEEP PLANe KEEPFIXED PLAN, então acho que a atualização automática ou manual das estatísticas não invalidará o plano. Este é um aplicativo de terceiros, portanto, não posso alterar as consultas. Quando executo UPDATE STATISTICS [TableTest] WITH FULLSCANnão vejo uma recompilação das consultas que usam essa tabela.
Em seguida, usei um script de Jonathan Kehayias para verificar se havia pressão de memória, mas o script só retornou RESOURCE_MEMPHYSICAL_HIGH, então não acho que seja esse o problema.
Nenhuma modificação na definição da tabela, índices aconteceram nem a consulta foi executada com sp_recompile / com recompile
Existe algo mais que pode causar a invalidação de um plano de consulta?
Atualização:
Depois de mais algumas pesquisas, acho que as recompilações são acionadas pela pressão da memória no plancache. Este artigo fornece muitas informações sobre os componentes internos do cache do plano. Trata-se do SQL Server 2005, mas esperamos que isso ainda se aplique ao SQL Server 2016.
De acordo com este artigo, você pode calcular o limite de pressão com base na quantidade total de memória de destino visível.
Minha pesquisa avança lentamente porque é difícil encontrar informações sobre pressão no cache do plano.
Vou mantê-lo informado sobre o progresso
Após uma extensa pesquisa, encontrei a causa do meu problema. Portanto, para calcular o limite de pressão do cache, o SQL Server usa a seguinte fórmula:
Desde o SQL Server 2012 e superior, a pressão de memória é acionada quando um armazenamento de cache aumenta para 62,5% do limite de pressão de cache do plano. Quando isso acontece, os planos são removidos do cache usando um algoritmo chamado política de despejo , que é baseado no custo do plano.
Então em determinados momentos eu vejo uma queda no tamanho do cache do meu plano e esse cenário acontece várias vezes ao dia, causando a cada vez a compilação de novos planos.