Utilizando o Optimize for ad-hoc workloads

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.