Entendendo a configuração Optimize for Ad Hoc Workloads

Tempo de leitura: 7 minutos

Fala pessoal!

Espero que estejam todos bem!

Hoje vamos mergulhar em uma das configurações mais subestimadas do SQL Server que pode literalmente dar um grande folego para o seu ambiente no gerenciamento de problemas de memória. Estamos falando do “Optimize for Ad Hoc Workloads”. Se você já teve dor de cabeça com plan cache inchado ou performance degradada por causa de consultas ad hoc, esse artigo é para você.

O que são consultas Ad Hoc?

Antes de entrarmos no assunto principal, vamos entender o que são essas famosas consultas ad hoc. O termo “ad hoc” vem do latim e significa “para este propósito específico”. No contexto do SQL Server, são consultas que:

São executadas dinamicamente (não são stored procedures)
Geralmente são executadas apenas uma vez ou raramente
Contêm valores literais hardcoded no código
São geradas por aplicações que constroem SQL dinamicamente

Exemplo comum de uma consulta ad hoc:

SELECT Nome, Email, DataCadastro
FROM Clientes
WHERE ClienteID = 12345

SELECT Nome, Email, DataCadastro

FROM Clientes

WHERE ClienteID = 12345

Cada vez que essa consulta é executada com um ID diferente, o SQL Server trata como uma consulta completamente nova!

– Tá Gustavo, mas qual problema isso pode gerar?

Toda vez que o SQL Server executa uma consulta, ele cria um plano de execução e armazena no plan cache para reutilização futura. Isso é ótimo para stored procedures e consultas parametrizadas, mas para consultas ad hoc talvez não seja necessário guardar o mesmo plano infinitas vezes.

Vamos ver na Prática

Imagine que você tem uma aplicação que gera consultas assim:

SELECT * FROM Produtos WHERE ProdutoID = 1001
SELECT * FROM Produtos WHERE ProdutoID = 1002
SELECT * FROM Produtos WHERE ProdutoID = 1003
SELECT * FROM Produtos WHERE ProdutoID = 1004
SELECT * FROM Produtos WHERE ProdutoID = 1005
SELECT * FROM Produtos WHERE ProdutoID = 1006
SELECT * FROM Produtos WHERE ProdutoID = 1007
SELECT * FROM Produtos WHERE ProdutoID = 1008
SELECT * FROM Produtos WHERE ProdutoID = 1009
SELECT * FROM Produtos WHERE ProdutoID = 1010
-- ... e assim por diante

SELECT * FROM Produtos WHERE ProdutoID = 1001

SELECT * FROM Produtos WHERE ProdutoID = 1002

SELECT * FROM Produtos WHERE ProdutoID = 1003

SELECT * FROM Produtos WHERE ProdutoID = 1004

SELECT * FROM Produtos WHERE ProdutoID = 1005

SELECT * FROM Produtos WHERE ProdutoID = 1006

SELECT * FROM Produtos WHERE ProdutoID = 1007

SELECT * FROM Produtos WHERE ProdutoID = 1008

SELECT * FROM Produtos WHERE ProdutoID = 1009

SELECT * FROM Produtos WHERE ProdutoID = 1010

-- ... e assim por diante

Para o SQL Server, cada uma dessas consultas é única e merece seu próprio plano de execução no cache. Se você tem 100.000 produtos, terá potencialmente 100.000 planos diferentes ocupando memória.

Optimize for Ad Hoc Workloads

O SQL Server 2008 introduziu uma feature muito importante. “Optimize for Ad Hoc Workloads”. Quando habilitada, essa configuração muda drasticamente como o SQL Server lida com consultas ad hoc.

Como Funciona?

Em vez de armazenar o plano completo na primeira execução, o SQL Server:

1. Primeira execução – Armazena apenas um “stub” (esqueleto) do plano

2. Segunda execução – Se a mesma consulta for executada novamente, substitui o stub pelo plano completo

3. Resultado – Economia massiva de memória para consultas que executam apenas uma vez

Verificando se você precisa dessa configuração no seu ambiente

Antes de sair habilitando tudo, vamos diagnosticar se você realmente precisa. Use este script para analisar seu plan cache:

-- Verificar o percentual de consultas ad hoc no plan cache
SELECT
    AdHoc_Plan_MB,
    Total_Cache_MB,
    AdHoc_Plan_MB * 100.0 / Total_Cache_MB AS 'AdHoc_Percentage'
FROM (
    SELECT
        SUM(CASE WHEN objtype = 'Adhoc' THEN size_in_bytes ELSE 0 END) / 1048576.0 AS AdHoc_Plan_MB,
        SUM(size_in_bytes) / 1048576.0 AS Total_Cache_MB
    FROM sys.dm_exec_cached_plans
) AS CacheStats;

-- Verificar o percentual de consultas ad hoc no plan cache

SELECT

AdHoc_Plan_MB,

Total_Cache_MB,

AdHoc_Plan_MB * 100.0 / Total_Cache_MB AS 'AdHoc_Percentage'

FROM (

SELECT

SUM(CASE WHEN objtype = 'Adhoc' THEN size_in_bytes ELSE 0 END) / 1048576.0 AS AdHoc_Plan_MB,

SUM(size_in_bytes) / 1048576.0 AS Total_Cache_MB

FROM sys.dm_exec_cached_plans

) AS CacheStats;

Dica : Se consultas ad hoc representarem mais de 20-30% do seu plan cache, pode ser uma boa hora para habilitar o Optimize for Ad Hoc Workloads no seu ambiente.

Habilitando a Configuração

Via T-SQL

-- Habilitar configurações avançadas
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Habilitar Optimize for Ad Hoc Workloads
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

-- Desabilitar configurações avançadas (boa prática)
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

-- Habilitar configurações avançadas

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

-- Habilitar Optimize for Ad Hoc Workloads

EXEC sp_configure 'optimize for ad hoc workloads', 1;

RECONFIGURE;

-- Desabilitar configurações avançadas (boa prática)

EXEC sp_configure 'show advanced options', 0;

RECONFIGURE;

Via SQL Server Management Studio

Clique com botão direito na instância do SQL Server.
Vá em “Properties” → “Advanced”.
Localize “Optimize for Ad Hoc Workloads”.
Altere para “True”.

Demonstração Prática Antes e Depois

Vamos criar um cenário real para ver a diferença. Vou usar o banco AdventureWorks2016:

Cenário de Teste

-- Limpar o cache para teste limpo 
DBCC FREEPROCCACHE;

1 2	-- Limpar o cache para teste limpo DBCC FREEPROCCACHE;

-- Verificar configuração atual 
SELECT name, value_in_use FROM sys.configurations WHERE name = 'optimize for ad hoc workloads';

1 2	-- Verificar configuração atual SELECT name, value_in_use FROM sys.configurations WHERE name = 'optimize for ad hoc workloads';

-- Simular aplicação gerando consultas ad hoc 
DECLARE @SQL NVARCHAR(MAX); 
DECLARE @Counter INT = 1; 

WHILE @Counter <= 100 
BEGIN     
		SET @SQL = 'SELECT BusinessEntityID, FirstName, LastName                 
		FROM Person.Person                 
		WHERE BusinessEntityID = ' + CAST(@Counter AS NVARCHAR(10));     
EXEC sp_executesql @SQL; 

SET @Counter = @Counter + 1; 

END;

-- Simular aplicação gerando consultas ad hoc

DECLARE @SQL NVARCHAR(MAX);

DECLARE @Counter INT = 1;

WHILE @Counter <= 100

BEGIN

SET @SQL = 'SELECT BusinessEntityID, FirstName, LastName

FROM Person.Person

WHERE BusinessEntityID = ' + CAST(@Counter AS NVARCHAR(10));

EXEC sp_executesql @SQL;

SET @Counter = @Counter + 1;

END;

Vamos fazer alguns testes

Sem Optimize for Ad Hoc Workloads:

SELECT
    objtype,
    COUNT(*) AS PlanCount,
    SUM(size_in_bytes) / 1024 / 1024 AS TotalSizeMB,
    AVG(size_in_bytes) AS AvgPlanSize
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
GROUP BY objtype;

SELECT

objtype,

COUNT(*) AS PlanCount,

SUM(size_in_bytes) / 1024 / 1024 AS TotalSizeMB,

AVG(size_in_bytes) AS AvgPlanSize

FROM sys.dm_exec_cached_plans

WHERE objtype = 'Adhoc'

GROUP BY objtype;

Análise

Cada consulta ad hoc criou um plano completo
105 planos diferentes ocupando 2MB de memória
Média de ~21KB por plano de execução

Com Optimize for Ad Hoc Workloads habilitado:

Agora limpe o cache novamente, habilite a configuração e rode o loop novamente.

--limpar o cache
DBCC FREEPROCCACHE()

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Habilitar Optimize for Ad Hoc Workloads
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

-- Desabilitar configurações avançadas (boa prática)
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

-- Simular aplicação gerando consultas ad hoc 
DECLARE @SQL NVARCHAR(MAX); 
DECLARE @Counter INT = 1; 

WHILE @Counter <= 100 
BEGIN     
		SET @SQL = 'SELECT BusinessEntityID, FirstName, LastName                 
		FROM Person.Person                 
		WHERE BusinessEntityID = ' + CAST(@Counter AS NVARCHAR(10));     
EXEC sp_executesql @SQL; 

SET @Counter = @Counter + 1; 

END;

--limpar o cache

DBCC FREEPROCCACHE()

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

-- Habilitar Optimize for Ad Hoc Workloads

EXEC sp_configure 'optimize for ad hoc workloads', 1;

RECONFIGURE;

-- Desabilitar configurações avançadas (boa prática)

EXEC sp_configure 'show advanced options', 0;

RECONFIGURE;

-- Simular aplicação gerando consultas ad hoc

DECLARE @SQL NVARCHAR(MAX);

DECLARE @Counter INT = 1;

WHILE @Counter <= 100

BEGIN

SET @SQL = 'SELECT BusinessEntityID, FirstName, LastName

FROM Person.Person

WHERE BusinessEntityID = ' + CAST(@Counter AS NVARCHAR(10));

EXEC sp_executesql @SQL;

SET @Counter = @Counter + 1;

END;

Rode em seguida a seguinte query :

-- Verificar stubs vs planos completos
SELECT
    cacheobjtype,
    COUNT(*) AS ObjectCount,
    SUM(size_in_bytes) / 1024 / 1024 AS TotalSizeMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
GROUP BY cacheobjtype;

-- Verificar stubs vs planos completos

SELECT

cacheobjtype,

COUNT(*) AS ObjectCount,

SUM(size_in_bytes) / 1024 / 1024 AS TotalSizeMB

FROM sys.dm_exec_cached_plans

WHERE objtype = 'Adhoc'

GROUP BY cacheobjtype;

Análise

15 Compiled Plans – Consultas que foram reutilizadas (executadas mais de uma vez)
5 Compiled Plan Stubs – Consultas únicas (executadas apenas uma vez)
Stubs ocupando espaço mínimo – TotalSizeMB = 0

Conclusão do Teste

Stubs vs Planos Completos – Economia de ~21KB por consulta única
Impacto Real – Em ambientes com milhares de consultas ad hoc, isso pode representar GBs de economia
Eficiência – Apenas consultas reutilizadas mantêm planos completos

Monitorando Queries Ad hoc

Para manter o controle, utilize esta query para monitorar regularmente:

-- Query completa para análise do plan cache
SELECT
    cp.objtype,
    cp.cacheobjtype,
    cp.usecounts,
    cp.size_in_bytes / 1024 AS SizeKB,
    st.text AS QueryText
FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.objtype = 'Adhoc'
    AND cp.usecounts = 1  -- Mostra planos usados apenas uma vez
ORDER BY cp.size_in_bytes DESC;

-- Query completa para análise do plan cache

SELECT

cp.objtype,

cp.cacheobjtype,

cp.usecounts,

cp.size_in_bytes / 1024 AS SizeKB,

st.text AS QueryText

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

WHERE cp.objtype = 'Adhoc'

AND cp.usecounts = 1 -- Mostra planos usados apenas uma vez

ORDER BY cp.size_in_bytes DESC;

Identificando Compiled Plan Stubs

Quando a configuração estiver ativa, você verá entradas com “cacheobjtype = ‘Compiled Plan Stub'”:

-- Verificar stubs no cache
SELECT
    cacheobjtype,
    COUNT(*) AS Count,
    SUM(size_in_bytes) / 1024 / 1024 AS TotalMB
FROM sys.dm_exec_cached_plans
WHERE cacheobjtype IN ('Compiled Plan', 'Compiled Plan Stub')
GROUP BY cacheobjtype;

-- Verificar stubs no cache

SELECT

cacheobjtype,

COUNT(*) AS Count,

SUM(size_in_bytes) / 1024 / 1024 AS TotalMB

FROM sys.dm_exec_cached_plans

WHERE cacheobjtype IN ('Compiled Plan', 'Compiled Plan Stub')

GROUP BY cacheobjtype;

Script de Diagnóstico Completo

Aqui está um script completo para ajudar no diagnóstico:

-- Diagnóstico completo do Plan Cache
WITH CacheAnalysis AS (
    SELECT
        objtype,
        cacheobjtype,
        COUNT(*) AS PlanCount,
        SUM(size_in_bytes) / 1024 / 1024 AS TotalMB,
        SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS SingleUsePlans,
        SUM(CASE WHEN usecounts > 1 THEN 1 ELSE 0 END) AS ReusedPlans
    FROM sys.dm_exec_cached_plans
    GROUP BY objtype, cacheobjtype
)
SELECT
    *,
    CASE
        WHEN objtype = 'Adhoc' AND SingleUsePlans > (PlanCount * 0.8)
        THEN 'RECOMENDA HABILITAR Optimize for Ad Hoc Workloads'
        ELSE 'Configuração atual adequada'
    END AS Recomendacao
FROM CacheAnalysis
ORDER BY TotalMB DESC;

-- Diagnóstico completo do Plan Cache

WITH CacheAnalysis AS (

SELECT

objtype,

cacheobjtype,

COUNT(*) AS PlanCount,

SUM(size_in_bytes) / 1024 / 1024 AS TotalMB,

SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS SingleUsePlans,

SUM(CASE WHEN usecounts > 1 THEN 1 ELSE 0 END) AS ReusedPlans

FROM sys.dm_exec_cached_plans

GROUP BY objtype, cacheobjtype

)

SELECT

CASE

WHEN objtype = 'Adhoc' AND SingleUsePlans > (PlanCount * 0.8)

THEN 'RECOMENDA HABILITAR Optimize for Ad Hoc Workloads'

ELSE 'Configuração atual adequada'

END AS Recomendacao

FROM CacheAnalysis

ORDER BY TotalMB DESC;

Conclusão

O Optimize for Ad Hoc Workloads é uma configuração que pode fazer uma diferença significativa na performance do seu SQL Server, especialmente em ambientes com muitas consultas dinâmicas. A economia de memória pode ser gigantesca dependendo do volume de queries ad hoc gerada pela sua aplicação. Já vi casos onde o plan cache reduziu de 2GB para 200MB.

E aí, já conhecia essa configuração? Conta nos comentários se já teve problemas com plan cache inflado e como resolveu.

Até a próxima pessoal.

Gustavo Larocca

Consultor SQL Server

Referências:

[Microsoft Docs – optimize for ad hoc workloads](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option?view=sql-server-ver16)

[sys.dm_exec_cached_plans](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-cached-plans-transact-sql?view=sql-server-ver16)

[sys.dm_exec_query_stats](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-ver16)

performance Plan Cache Ad Hoc Queries Otimização Memória sql server microsoft

Deixe uma respostaCancelar resposta

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Entendendo a configuração Optimize for Ad Hoc Workloads