O servidor SQL Server possui a configuração Optimize for ad-hoc workloads, sobre a qual já escrevi uma dica antes.
O SQL Server sempre cria um cache do plano de execução das queries que executamos, quer sejam stored procedures ou queries ad hoc. Porém, quando nossa atividade no servidor é demasiadamente focada em queries ad hoc – instruções SQL enviadas diretamente ao servidor, ao contrário do uso de procedures -, corremos o risco de que a criação dos planos de execução consuma memória de forma inadequada.
Isso pode acontecer porque é possível que muitas das queries ad hoc que executamos no servidor jamais se repitam, por isso a armazenagem do plano de execução pode ser um desperdício de memória.
Para resolver este problema, podemos utilizar a configuração ‘Optimize for Ad hoc queries’. Quando esta configuração está ativada, o servidor apenas guarda o plano de execução da query em cache na segunda vez que ela for executada, não da primeira.
Na primeira execução é criado um stub em cache, contendo apenas uma contagem de execução da query. Na segunda execução, o stub é substituído pelo plano de execução real.
Para ativar essa configuração, precisamos primeiro ativar a exibição de configurações avançadas:
sp_configure 'show advanced options',1 GO reconfigure GO
Em seguida, ativar a configuração:
sp_configure 'optimize for ad hoc workloads',1 GO reconfigure go
Para desativar, utilizamos as mesmas instruções, trocando “1” por “0”.
Vamos fazer uma simulação para que você veja o efeito gerado. Utilizando o banco AdventureWorks, rode em um ambiente de teste as seguintes instruções:
dbcc freeproccache go select * from person.person Where LastName='Raheem' go
Agora vamos verificar o resultado dessa execução no cache:
select usecounts,cacheobjtype,objtype,size_in_bytes,[text] from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle)
Observe na imagem abaixo o tamanho guardado no cache, indicando que o plano de execução está no cache.
Agora ative o ‘optimize for ad hoc workloads’, conforme indiquei acima, e repita a execução dos mesmos trechos acima. Veja o resultado no cache:
Desta vez, apenas um stub foi guardado no cache; não o plano inteiro. Com isso, temos uma economia de memória caso essa query não venha a ser utilizada novamente. Execute novamente a query (sem limpar o cache) e confira o resultado no cache:
Agora o stub foi transformado em plano de execução realmente compilado. Ele não soma as contagens, que continua sendo um, afinal o plano compilado ainda não foi re-utilizado. Execute novamente a mesma query e confira o cache; assim poderá observar a contagem aumentando, como na imagem abaixo.
Todo servidor sempre possui alguma quantidade de queries ad-hoc. Considero essa configuração uma boa prática na grande maioria dos servidores. São poucas as exceções.