Como melhorar a taxa de acertos do cache de procedimento?

Deixe-me resumir (e arredondar!) os pontos de dados importantes em sua planilha:

      Total                                     Use Count 1
      ---------------------------------------   -----------------------
      Total Plans   Total MBs   Avg Use Count   Total Plans   Total MBs   
      -----------   ---------   -------------   -----------   ---------
Adhoc      55,987       3,054               3        38,314       2,036
Proc          709       1,502           1,549           135         527

Portanto, a primeira linha mostra as coisas ruins, ocupando cerca de 2/3 do cache do seu plano (coisas que geralmente são usadas apenas uma vez, com algumas exceções muito pequenas). Você precisa tentar se livrar deles o máximo que puder. A segunda linha mostra as coisas boas. Estas são as coisas que você deseja em seu cache de plano (planos com uma alta quantidade de reutilização). O restante dos dados é IMHO amplamente irrelevante. Um outro ponto, porém: você diz que o acesso é exclusivamente por meio de procedimentos armazenados, mas se esses procedimentos usam SQL dinâmico, essas instruções são armazenadas em cache como AdHocplanos, não como Procplanos.

Em 2008 ou superior, eu diria para ligar optimize for ad hoc workloadse passar para o próximo problema - isso reduziria a quantidade de MBs que seus planos de uso único ocupam atualmente para quase nada. Infelizmente, em 2005, suas opções são bastante limitadas, além de refatorar esses procedimentos armazenados para usar nível de instrução OPTION (RECOMPILE)e/ou menos/sem SQL dinâmico, ou ativar a parametrização forçada no nível do banco de dados - o que tenta obter uma melhor reutilização do plano de consultas semelhantes tratando literais como parâmetros para fins de correspondência de plano. Hesito em mencionar guias de planos porque eles não são para os tímidos e - como discuto mais adiante nesta resposta - não tenho certeza se vale a pena seguir esse caminho, a menos que você saiba que o cache do seu plano é definitivamente a fonte do seu desempenho questão.

Eu perguntei @@VERSIONporque, antes do SP2, o algoritmo para a quantidade de memória que poderia ser alocada para o cache do plano era relativamente vago. A partir do SP2 eles apertaram um pouco isso (a mudança está documentada e explicada neste post e neste post ). No seu caso, o cache do plano está relativamente cheio, portanto, não é de surpreender que você esteja perdendo o cache. 26 GB = um limite superior de 5,8 GB; Vejo ~ 4,5 GB na planilha, mas pode haver alguma diferença de cálculo ou configuração aqui que eu não conheço.

Este artigo do MSDN fala sobre a optimize for ad hoc workloadsconfiguração do servidor adicionada em 2008 e menciona o sinalizador de rastreamento 8032, que permitirá a você alocar mais memória para seus caches (presumivelmente na ausência de configuração dessa configuração no nível do servidor, que agora recomendo a todos nossos clientes, ou pelo menos os 99% que não estão mais em 2005). Nunca testei esse sinalizador de rastreamento em 2005 SP3 ou SP4 e, honestamente, nem tenho certeza de quando foi introduzido. Também não sei se isso resolverá seu problema ou apenas o mudará, pois acho que mesmo se você tivesse um pouco mais de RAM alocada para caches, ainda estaria preenchendo e tendo muitos erros de cache devido à natureza de seus procedimentos armazenados.

Ou, é claro, se houver algum problema a resolver relacionado diretamente ao cache do plano. Só porque a taxa de acerto do cache não é tão alta quanto você espera, não significa que esteja causando o problema e, é claro, o inverso é que, mesmo com taxa de acerto do cache de 100% - o que não parece realista, considerando que tantos de seus planos são de uso único e ad hoc - seus usuários ainda podem estar sofrendo de problemas de desempenho causados ​​por algo totalmente diferente.

Minha sugestão é procurar armas fumegantes melhores do que planejar a taxa de acertos do cache. Obtenha mais detalhes sobre as reclamações de desempenho de seus usuários. Todas as consultas são sempre lentas? Certas perguntas? Certas horas do dia / semana / ciclo de negócios? Apenas as consultas de relatórios são lentas? Faça uma leitura séria deste documento reconhecidamente seco e longo sobre as melhores práticas do SQL Server - em particular, a seção sobre esperas e filas, que pode ajudá-lo a formular uma abordagem lógica para identificar, diagnosticar e resolver problemas de desempenho. Fazer com que algum número em um painel pareça melhor - um número que você nem sabe que contribui diretamente para o problema - pode ser muito satisfatório, mas se não resolver os problemas de desempenho de seus usuários, então não o afetou. qualquer lugar.

Eles também podem ser úteis para ler sobre compilação/recompilação e planejar a reutilização do cache. Algumas delas são focadas em 2008 (particularmente aquelas sobre a configuração de cargas de trabalho ad hoc), mas muitas das informações ainda são úteis para 2005 e/ou para entender melhor os benefícios da atualização (dica, dica).

• http://technet.microsoft.com/en-us/library/ms181055(v=sql.90).aspx
• http://www.sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options
• http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx
• http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
• http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/
• http://www.sqlskills.com/blogs/kimberly/clearing-the-cache-are-there-other-options/
• http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/
• http://www.sqlskills.com/blogs/kimberly/prepared-statements-and-caching/
• http://www.sommarskog.se/query-plan-mysteries.html
• http://www.sommarskog.se/dynamic_sql.html