Atualmente, a pergunta afeta C#/.NET, acesso ao banco de dados baseado em ADO.NET e SQL Server 2008 R2, mas acho que também se aplica a outros bancos de dados.
Percebi que alguns módulos antigos de um sistema têm consultas SQL não ideais, com várias strings de valor concatenadas em vez de espaços reservados para parâmetros. Eles fazem uma votação em uma tabela, como a cada 10 segundos, para obter itens adicionados durante os últimos minutos, o que gera um novo plano de consulta a cada execução.
O desempenho deles não é tão ruim, não há risco de injeção de SQL (sem formulários web/usuário), eles são antigos e daria muito trabalho alterar suas consultas para corrigir a parametrização. Sugiro fazer essa mudança, mas há o debate de que seria uma perda de tempo, sendo outras coisas mais importantes.
Editar: o banco de dados deve ser executado principalmente com consultas parametrizadas (que todos os módulos mais recentes usam), então gostaria de evitar a opção "otimizar para ad hoc". As consultas parcialmente parametrizadas criam um plano de qualquer maneira. Existe uma desvantagem ao executar no modo otimizado ad-hoc, com consultas principalmente parametrizadas?
Para mim, parece que esses módulos antigos ocupam uma grande parte dos recursos do banco de dados, embora sejam poucos. Mesmo um único módulo desse tipo criaria milhares de planos de consulta ao longo do tempo, enquanto todos os módulos mais novos juntos têm menos.
É importante alterá-los ou posso deixá-los em seu estado, com otimização/consultas parametrizadas apenas nos módulos atuais/futuros?
SQL é como:
select ItemId, ItemName from Items
where ItemType=3 and ItemCreator=1234
and ItemDate >= '2013-11-23 12:30:00'
onde os valores variam e a data é alguns minutos antes de agora. Em alguns casos, a data foi alterada para um parâmetro como "@startDate", para evitar problemas de formato, mas os valores ItemType e ItemCreator ainda são strings concatenadas.
Ao monitorar planos de consulta com DMV ou Activity Monitor (Recent Expensive Queries - Plan Count coluna), percebo que algumas dessas consultas têm mais de 8.000 planos de consulta equivalentes no cache:
select count(*), query_plan_hash
from sys.dm_exec_query_stats
group by query_plan_hash
order by count(*) desc
em seguida, selecionando o plano XML com um CROSS APPLY em sys.dm_exec_query_plan, com identificadores de plano selecionados pelo hash do plano de consulta.
Edição/Conclusão temporária: Parece que é melhor deixar os aplicativos muito antigos como estão, mesmo ao criar toneladas de consultas ad hoc. Meu maior medo era que a enxurrada de consultas ad hoc de uso único fizesse com que os bons planos de consulta parametrizados e preparados para vários usos fossem removidos do cache. Isso não acontece porque, quando a limpeza é feita, os planos ad hoc são removidos primeiro e outros são classificados por fatores como complexidade, número de usos etc. Portanto, as consultas parametrizadas com alta taxa de uso provavelmente serão mantidas, não importa quantas planos ad hoc ou parcialmente parametrizados inundam. A otimização ad hoc reduz o tamanho do plano (na verdade, nenhum plano real é armazenado no primeiro uso), mas ainda mais planos podem ser mantidos, com um uso de memória semelhante (está correto?). Mesmo o SQL parcialmente parametrizado (parâmetros DateTime para evitar problemas de formato local) será removido rapidamente se não for usado novamente, mesmo quando enviado com sp_executesql, que força a parametrização e o armazenamento em cache do plano. Ter um grande número (5.000 a 8.000+) de planos de consulta ad hoc equivalentes não é bom, mas provavelmente menos prejudicial do que ter que vasculhar anos C#, C++, talvez até código do Visual Studio 6.0, para corrigir consultas (ninguém paga por isso, e o material ainda está funcionando sem problemas reconhecíveis).
"Quão ruim é isso?" depende do grau em que você está sofrendo agora ou poderia sofrer com o aumento da carga de trabalho no futuro.
Um ponto importante de sofrimento com a poluição do cache do plano pode ser o excesso de planos de uso único que incham o cache do plano, levando ao uso ineficiente do cache.
Outro ponto de sofrimento pode ser compilações altas/segundo - portanto, em um ambiente com uma carga de trabalho pesada e muita atividade, há um custo associado à compilação repetida.
Você pode ver o impacto das compilações/s em perfmon (SQL Server Statistics:Compilations/sec). Isso pode parecer pressão da CPU. Para seu desempenho/aplicativos, isso pode parecer uma duração de consulta aumentada, esperando por compilações desnecessárias toda vez que é executada.
Você pode ver o impacto no cache do plano do inchaço da memória por esta consulta emprestada dos scripts de diagnóstico de Glenn Berry . Qual é o tamanho do cache do seu plano SQLCP?
Além disso, a consulta que foi usada na pergunta para identificar o número de planos também ajuda.
Isso é sempre uma coisa boa?
Existem alguns casos em que isso pode ser bom, mas a situação é rara. Basicamente, se você estava sofrendo com o sniffing de parâmetros que deu errado (resumindo: se os dados podem variar amplamente de execução para execução com base nos parâmetros, uma compilação para um conjunto de parâmetros ideais pode gerar um excelente plano de consulta para essa consulta, mas ruim para outras. ). Meu palpite é que você provavelmente não lidaria com isso tão mal quanto com as implicações da reutilização de plano ruim.
O que você pode fazer sobre isso?
Otimizar para cargas de trabalho ad hoc certamente pode ajudar com as implicações de memória, pois apenas um esboço do plano é armazenado no cache na primeira execução, e o plano completo não é armazenado até que seja executado uma segunda vez com o mesmo plano.
A parametrização forçada também pode ajudar aqui. Às vezes, pode forçar a parametrização e ajudar a resolver o problema do cache bloat e o custo de recompilar.
Corrija as consultas Idealmente, você não deveria ter que recorrer a essas opções, mas, em vez disso, pode ser mais rigoroso no desenvolvimento de seu banco de dados, incentivar a reutilização de planos, considerar procedimentos armazenados para todos os seus benefícios e tentar evitar o problema dessa maneira. As formas de ajudar a corrigir isso por meio de parametrização forçada ou otimizar para ad hoc são boas para ajudar, mas a melhor solução é sempre voltada para a causa raiz.
Há um excelente recurso aqui que fala sobre alguns dos perigos da poluição do cache do plano e algumas coisas que você pode fazer. Eu recomendaria uma leitura aqui. Ele foi escrito para SQL Server 2012, mas os conceitos e soluções se aplicam.