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).
