Módulo 20 — Planos de Execução & Tuning Avançado

1) Como ler planos (Estimado × Real)

• Estimado (Ctrl+L): baseado em estatísticas; não executa a consulta.
• Real (Ctrl+M): inclui contadores reais (linhas, tempo) e warnings (ex.: spills). Use para depurar.
• XML: habilite com SET STATISTICS XML ON para inspecionar detalhes (predicados, Warnings, custos).

SET STATISTICS XML ON;
SELECT ...;
SET STATISTICS XML OFF;

2) Operadores essenciais

3) Estratégias de Join

• Nested Loops: escolha comum para OLTP; sensível a lookups. Garanta índices na tabela interna.
• Hash Match: constrói tabela de hash na entrada menor; robusto para grandes volumes.
• Merge Join: requer ordenação; ótimo quando ambas entradas já estão ordenadas pela chave.

-- Forçando estratégia (apenas para teste!)
SELECT ...
FROM A
JOIN B WITH (INDEX(...)) ON A.K = B.K
OPTION (LOOP JOIN);   -- ou HASH JOIN / MERGE JOIN

4) Paralelismo (MAXDOP & Threshold)

• MAXDOP: grau máximo de paralelismo da consulta (OPTION (MAXDOP 4)) ou nível de servidor/banco.
• Cost Threshold for Parallelism: custo mínimo para o otimizador considerar plano paralelo.
• Operadores Exchange: Distribute/Repartition/Gather Streams coordenam fluxos paralelos.
• Espera comum: CXPACKET / CXCONSUMER indicam sincronização do paralelismo (não necessariamente problema).

-- Forçar/limitar paralelismo na consulta
SELECT ... OPTION (MAXDOP 1);   -- força serial
SELECT ... OPTION (MAXDOP 0);   -- usa padrão do servidor

5) Memory Grant & Spills

Operadores como Sort e Hash pedem memória. Se insuficiente, ocorre spill para tempdb (triângulo amarelo no plano real).

• Subalocação: spills → latência alta, IO no tempdb.
• Superalocação: desperdiça memória e pode pressionar concorrentes.
• Memory Grant Feedback: o motor ajusta o grant em execuções futuras (quando habilitado).

-- Verificar em XML: <Warnings><SpillToTempDb .../></Warnings>
-- Mitigar:
-- 1) Melhorar estima (estatísticas/reescrita)
-- 2) Reduzir linhas antes do Sort/Hash (filtros/índices)
-- 3) Aumentar cobertura para evitar ordenações desnecessárias

6) Cardinality Estimator & Compatibilidade

Diferenças do CE (estimador de cardinalidade) podem trocar o plano de consultas sensíveis.

-- Alternar por banco (testes controlados)
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;  -- antigo
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF; -- moderno

-- Por consulta (sem alterar compatibilidade do DB)
SELECT ... OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150'));

7) Hints (FORCESEEK, FORCE ORDER, JOIN hints, MAXDOP)

Use hints somente quando o modelo/estatísticas não resolvem razoavelmente.

-- FORCESEEK (evita scan quando o índice suporta o predicado)
SELECT ... FROM dbo.Pedidos WITH (FORCESEEK)
WHERE IdCliente = @c AND DataPedido >= @d1 AND DataPedido < @d2;

-- FORCE ORDER (respeita ordem de junção escrita)
SELECT ... FROM A JOIN B ON ... JOIN C ON ...
OPTION (FORCE ORDER);

-- MAXDOP por consulta
SELECT ... OPTION (MAXDOP 2);

8) Anti‑patterns & Padrões de reescrita

• Funções em colunas: troque por intervalos SARGable.
• Conversões implícitas: alinhe tipos (evita Compute Scalar caro).
• SELECT *: traga só o necessário (evita lookups e largura de linha).
• OR em colunas diferentes: considere UNION ALL ou reescreva em consultas separadas.
• Tabela variável grande: prefira #temp (estatísticas) para conjuntos significativos.
• TOP sem ORDER BY: resultado não determinístico.

9) Laboratório Guiado

9.1) Setup

IF OBJECT_ID('dbo.Clientes','U') IS NOT NULL DROP TABLE dbo.Clientes;
IF OBJECT_ID('dbo.Pedidos','U')  IS NOT NULL DROP TABLE dbo.Pedidos;
GO
CREATE TABLE dbo.Clientes(
  IdCliente int IDENTITY PRIMARY KEY,
  Nome      nvarchar(100) NOT NULL,
  UF        char(2) NULL
);
CREATE TABLE dbo.Pedidos(
  IdPedido   int IDENTITY PRIMARY KEY,
  IdCliente  int NOT NULL REFERENCES dbo.Clientes(IdCliente),
  DataPedido date NOT NULL,
  ValorTotal decimal(12,2) NOT NULL,
  Status     varchar(20) NOT NULL
);
GO
-- Índices
CREATE NONCLUSTERED INDEX IX_Pedidos_Cliente_Data ON dbo.Pedidos (IdCliente, DataPedido) INCLUDE (ValorTotal, Status);
CREATE NONCLUSTERED INDEX IX_Clientes_UF ON dbo.Clientes (UF) INCLUDE (Nome);
GO
-- Dados amostra
INSERT INTO dbo.Clientes (Nome, UF)
SELECT CONCAT(N'Cliente ', v.n), CASE WHEN v.n%3=0 THEN 'SP' WHEN v.n%3=1 THEN 'RJ' ELSE 'PR' END
FROM (SELECT TOP (5000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects) AS v;
INSERT INTO dbo.Pedidos (IdCliente, DataPedido, ValorTotal, Status)
SELECT TOP (20000)
  ABS(CHECKSUM(NEWID()))%5000 + 1,
  DATEADD(day, ABS(CHECKSUM(NEWID()))%365, '2024-01-01'),
  (ABS(CHECKSUM(NEWID()))%5000)/1.0,
  CASE WHEN ABS(CHECKSUM(NEWID()))%4=0 THEN 'Aberto' ELSE 'Pago' END
FROM sys.all_objects a CROSS JOIN sys.all_objects b;
GO

9.2) NL vs Hash vs Merge

-- Plano “natural” do otimizador
SELECT c.UF, SUM(p.ValorTotal)
FROM dbo.Pedidos AS p
JOIN dbo.Clientes AS c ON c.IdCliente = p.IdCliente
GROUP BY c.UF;

-- Forçando para comparar (somente testes)
SELECT c.UF, SUM(p.ValorTotal)
FROM dbo.Pedidos AS p
JOIN dbo.Clientes AS c ON c.IdCliente = p.IdCliente
GROUP BY c.UF
OPTION (LOOP JOIN);

SELECT c.UF, SUM(p.ValorTotal)
FROM dbo.Pedidos AS p
JOIN dbo.Clientes AS c ON c.IdCliente = p.IdCliente
GROUP BY c.UF
OPTION (HASH JOIN);

-- Merge requer ordenação prévia compatível
SELECT c.UF, SUM(p.ValorTotal)
FROM dbo.Pedidos AS p WITH (INDEX(IX_Pedidos_Cliente_Data))
JOIN dbo.Clientes AS c WITH (INDEX(IX_Clientes_UF))
  ON c.IdCliente = p.IdCliente
GROUP BY c.UF
OPTION (MERGE JOIN);

9.3) Paralelismo & MAXDOP

-- Compare serial x paralelo
SELECT c.UF, SUM(p.ValorTotal)
FROM dbo.Pedidos AS p
JOIN dbo.Clientes AS c ON c.IdCliente = p.IdCliente
GROUP BY c.UF
OPTION (MAXDOP 1);

SELECT c.UF, SUM(p.ValorTotal)
FROM dbo.Pedidos AS p
JOIN dbo.Clientes AS c ON c.IdCliente = p.IdCliente
GROUP BY c.UF
OPTION (MAXDOP 0);  -- padrão do servidor

9.4) Spills em Sort/Hash

-- Forçar um SORT grande
SELECT TOP (1000000) *
INTO #tmp
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

SELECT * FROM #tmp ORDER BY name;  -- observe warnings no plano real (SpillToTempDb)

9.5) CE & Hints

-- Comparar CE por consulta
SELECT SUM(ValorTotal)
FROM dbo.Pedidos
WHERE DataPedido >= '2024-07-01' AND DataPedido < '2024-10-01'
OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150'));
-- e depois sem o hint, comparando estimativas/planos

10) Exercícios

1. Leitura de Plano: Em uma consulta real, identifique operadores com maior custo e se há predicado residual.
2. Join Strategy: Explique por que o otimizador escolheu Hash em vez de Nested Loops em um caso seu.
3. Spill: Gere um plano com spill e elimine-o com estatísticas/índice/filtro.
4. Paralelismo: Compare tempos com MAXDOP 1 vs padrão e proponha diretriz.
5. Hint responsável: Aplique FORCESEEK em uma consulta elegível e meça o efeito (positivo/negativo).

11) Gabarito (sugestões)

-- 1) Residual: ver no plano XML/SSMS “Predicate” vs “Seek Predicates”
-- 2) Hash escolhido: entradas grandes, sem ordenação compatível, melhor custo estimado
-- 3) Remover spill: crie/atualize stats ou índice alinhado ao ORDER BY / JOIN, ou reduza linhas antes do Sort/Hash
-- 4) MAXDOP: resultados variam; documentar recomendação com números (IO/TIME)
-- 5) FORCESEEK: útil quando existe índice adequado e otimizador insiste em scan; retirar se plano piorar em outros cenários

12) Erros Comuns & Checklist

13) Próximos Passos

• Aprofundar com Columnstore & Batch Mode (consultas analíticas grandes).
• Explorar Scalar UDF inlining e Table Variable Deferred Compilation em versões recentes.
• Preparar o Módulo 21: Columnstore & Batch Mode (DW/HTAP).