Quais são os motivos para um plano estar ausente do cache para procedimentos armazenados?
WITH RECOMPILE- SQL dinâmico
- Código criptografado
- Mudanças significativas de dados
- Atualizar estatísticas
- O que mais?
Eu trabalhei em 2 servidores (SQL Server 2008 R2 e SQL Server 2012) recentemente que não tinham planos em cache para procedimentos armazenados com muitos recursos. Muitas, talvez todas, as instruções dentro dos procedimentos armazenados também não tinham planos em cache. Alguns dos procedimentos armazenados são executados com bastante frequência, algumas vezes por segundo.
Nenhuma pressão de memória. Um dos servidores tem muito mais hardware do que o necessário.
Eu pensei que os planos ausentes eram devido a criações de tabelas temporárias no meio dos procedimentos armazenados, mas isso parece ser uma informação antiga do SQL Server 2000 ou anterior. A partir do SQL Server 2005, as recompilações acontecem no nível de instrução para as instruções após o DDL. Isso é verdade em todos os casos ou ainda pode acontecer em versões mais recentes?
O que mais poderia ser o culpado pelos planos ausentes? Eu dei uma olhada em alguns artigos sobre este tópico, mas nada parece se encaixar.
Otimizar para cargas de trabalho adhoc está habilitado no servidor que estou analisando esta semana. Um dos procedimentos armazenados é executado apenas uma vez por dia. Eu tenho o código para isso. Não tenho o código para aquele que está sendo executado mais de 100 vezes por minuto, mas posso obtê-lo. Não poderei postar o código, mas posso descrevê-lo em relação à minha pergunta.
Não acredito que alguém esteja liberando o cache de procedimento ou descartando buffers limpos. Este cliente está usando o Solarwinds DPA como uma de suas ferramentas de monitoramento. O DPA capturou um dos planos de execução para a instrução no proc armazenado que é chamado uma vez por dia. Essa declaração tem uma quantidade enorme de leituras devido à WHEREcláusula não-sargável. Se o DPA capturou a instrução, então é um plano estimado e estava no cache do plano ao mesmo tempo. Simplesmente não está lá quando estamos solucionando problemas. Vou fazer com que eles comecem a se registrar sp_WhoIsActiveem uma mesa.
estou usando sp_BlitzCache. (Eu trabalho para Brent Ozar Unlimited) Isso mostrará o plano para todo o procedimento armazenado, bem como os planos para as instruções individuais, se existirem. Se eles não existirem, haverá um aviso "Não foi possível encontrar um plano para esta consulta. Os possíveis motivos para isso incluem SQL dinâmico, RECOMPILEdicas e código criptografado". E esse aviso está nas declarações também.
TF 2371 não está no lugar. Estou olhando para as estatísticas de espera. O servidor está bastante entediado. O PLE é superior a 130.000.
Agora tenho o código para mais 2 procedimentos armazenados. Um deles é usar SQL dinâmico exec (@sql)para que saibamos porque não existe um plano para isso. Mas o outro, e este é o que está rodando mais de 100 vezes por minuto, não tem nada fora do comum. A única coisa que se destaca nesse é que as tabelas temporárias estão sendo criadas no meio de mais de 1000 linhas de código. Ele também chama um monte de procedimentos armazenados filhos.
Em relação ao Plan Caching no SQL Server 2008 , não estou vendo nenhum literal >= 8k, mas um dos procedimentos armazenados tem um comentário sobre uma inserção em massa logo antes de chamar outro procedimento armazenado. Mas a inserção em massa não aparece no procedimento armazenado externo que estou vendo. A seção "Limiar de recompilação" do artigo é interessante. O que estou vendo para as tabelas temporárias são toneladas de INSERTs (que podem resultar em milhões de linhas), algumas atualizações e exclusões. Portanto, muitos dados são alterados nas tabelas temporárias. Milhões.
Há dois DMFs de cache de plano:
sys.dm_exec_query_plan - retorna planos em cache no formato XML, mas apenas até um determinado tamanho (e somente enquanto eles podem ser formatados como XML no SQL Server, o que significa até 128 níveis aninhados).
sys.dm_exec_text_query_plan - retorna planos em cache em formato de texto, de qualquer tamanho. Mas a desvantagem é que quando os planos são grandes, você não pode convertê-los em XML dentro do SQL Server, e mesmo TRY_CONVERT como XML retorna um nulo.
sp_BlitzCache atinge apenas o antigo DMV (porque ele precisa analisar planos de consulta como XML para fazer todos os tipos de fatiamento e corte). consultas maiores. No entanto, ainda não poderemos fazer análises XML nele.
Possivelmente você precisa ajustar o tamanho máximo do XML que pode ser retornado pelo SSMS para uma grade?