Módulo 19 — Estatísticas, Parameter Sniffing & Query Store

1) Estatísticas: Conceitos

O otimizador estima cardinalidades usando estatísticas, compostas por histograma (distribuição do valor líder) e densidade (seletividade média).

• Index stats: criadas junto com índices (histograma para a 1ª coluna da chave).
• Column stats: criadas automaticamente em colunas não indexadas quando AUTO_CREATE_STATISTICS está ON.
• Estatísticas filtradas: com WHERE, focadas em subconjuntos de dados.

2) Criação & Manutenção

-- Habilitando opções no banco
ALTER DATABASE MeuDB SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE MeuDB SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE MeuDB SET AUTO_UPDATE_STATISTICS_ASYNC ON;  -- opcional

-- Atualização manual
UPDATE STATISTICS dbo.Pedidos;                  -- todas as stats do objeto
UPDATE STATISTICS dbo.Pedidos IX_Pedidos_Cliente_Data WITH FULLSCAN;
EXEC sp_updatestats;                            -- database inteira (heurístico)

-- Criação personalizada
CREATE STATISTICS ST_Pedidos_Status
ON dbo.Pedidos (Status);  -- estatística de coluna

-- Estatística filtrada (apenas “ativos”)
CREATE STATISTICS ST_Clientes_Ativos
ON dbo.Clientes (UF)
WHERE Ativo = 1;

-- Amostragem
UPDATE STATISTICS dbo.Pedidos ST_Pedidos_Status WITH SAMPLE 50 PERCENT;
-- ou FULLSCAN para precisão (custo maior)

3) DBCC SHOW_STATISTICS

-- Inspecionar cabeçalho, vetor de densidade e histograma
DBCC SHOW_STATISTICS ('dbo.Pedidos', 'IX_Pedidos_Cliente_Data') WITH STAT_HEADER;
DBCC SHOW_STATISTICS ('dbo.Pedidos', 'IX_Pedidos_Cliente_Data') WITH DENSITY_VECTOR;
DBCC SHOW_STATISTICS ('dbo.Pedidos', 'IX_Pedidos_Cliente_Data') WITH HISTOGRAM;

• HISTOGRAM: mostra steps (intervalos) para a coluna líder.
• DENSITY: seletividade média para prefixos de colunas.
• Data de atualização e linhas amostradas ajudam a decidir quando recalcular.

4) Parameter Sniffing: causas & sinais

Quando uma consulta é compilada, os valores dos parâmetros influenciam o plano. O plano é cacheado e reutilizado para valores diferentes — o que pode ser ótimo ou péssimo se houver distribuição enviesada (skew).

• Sinais: tempos variáveis para a mesma consulta, trocas entre seek/scan, estimativas de linhas muito diferentes das reais.
• Onde ver: Plano real (linha amarela ≠ linha azul), Query Store (regressões), DMVs de cache.

-- Exemplo de proc suscetível (Cliente “quente” vs “frio”)
CREATE OR ALTER PROC dbo.sp_PedidosPorCliente
  @IdCliente int
AS
BEGIN
  SELECT IdPedido, DataPedido, ValorTotal
  FROM dbo.Pedidos
  WHERE IdCliente = @IdCliente AND Status = 'Pago';
END
GO

-- Execução 1 (compila com valor “frio” → plano A)
EXEC dbo.sp_PedidosPorCliente @IdCliente = 1;
-- Execução 2 (reusa plano A para cliente “quente” → ruim)

5) Mitigações

-- 1) Recompile por execução (custo de compilação, plano ótimo por valor)
CREATE OR ALTER PROC dbo.sp_PedidosPorCliente_Recompile
  @IdCliente int
AS
BEGIN
  SELECT IdPedido, DataPedido, ValorTotal
  FROM dbo.Pedidos
  WHERE IdCliente = @IdCliente AND Status = 'Pago'
  OPTION (RECOMPILE);
END

-- 2) OPTIMIZE FOR (valor específico ou UNKNOWN)
SELECT IdPedido, DataPedido, ValorTotal
FROM dbo.Pedidos
WHERE IdCliente = @IdCliente
OPTION (OPTIMIZE FOR (@IdCliente = 1));       -- “otimize como se fosse 1”
-- ou
OPTION (OPTIMIZE FOR (@IdCliente UNKNOWN));   -- use estimativa média

-- 3) Variável local (evita sniffing, usa densidade média)
CREATE OR ALTER PROC dbo.sp_PedidosPorCliente_LocalVar
  @IdCliente int
AS
BEGIN
  DECLARE @Local int = @IdCliente;
  SELECT IdPedido, DataPedido, ValorTotal
  FROM dbo.Pedidos
  WHERE IdCliente = @Local AND Status = 'Pago';
END

-- 4) Split de lógica (rotas quentes x frias), índices/estatísticas filtradas, etc.
IF (@IdCliente IN (/*lista VIPs*/))
BEGIN
  -- caminho otimizado para “quentes”
END
ELSE
BEGIN
  -- caminho para “frias”
END

6) Query Store: habilitar & usar

-- Habilitar no banco
ALTER DATABASE MeuDB SET QUERY_STORE = ON;
ALTER DATABASE MeuDB SET QUERY_STORE (
  OPERATION_MODE = READ_WRITE,
  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
  MAX_STORAGE_SIZE_MB = 1024,
  INTERVAL_LENGTH_MINUTES = 15,
  QUERY_CAPTURE_MODE = AUTO,            -- ALL / AUTO / CUSTOM
  SIZE_BASED_CLEANUP_MODE = AUTO
);

-- Top consumidores por CPU no período recente
SELECT TOP 20
  qt.query_sql_text,
  rs.avg_cpu_time, rs.last_execution_time, p.plan_id, q.query_id
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q     ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p      ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(day,-7, SYSUTCDATETIME())
ORDER BY rs.avg_cpu_time DESC;

-- Forçar um plano “bom” encontrado
EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;
-- Remover força
EXEC sp_query_store_unforce_plan @query_id = 123, @plan_id = 456;

7) Auto Tuning & Baselines

• Baselines: capture o plano “bom” e force via Query Store quando necessário.
• Tuning automático (quando disponível): opção de “forçar último bom plano” pode corrigir regressões sem intervenção.
• Compatibilidade: verifique o nível de compatibilidade e o cardinality estimator mais adequado para o seu workload.

8) Laboratório Guiado

8.1) Setup (tabelas & dados enviesados)

IF OBJECT_ID('dbo.Pedidos','U') IS NOT NULL DROP TABLE dbo.Pedidos;
GO
CREATE TABLE dbo.Pedidos(
  IdPedido   int IDENTITY PRIMARY KEY,
  IdCliente  int NOT NULL,
  DataPedido date NOT NULL,
  Status     varchar(20) NOT NULL,
  ValorTotal decimal(12,2) NOT NULL
);
GO
-- Índice mais comum
CREATE NONCLUSTERED INDEX IX_Pedidos_Cliente_Status ON dbo.Pedidos (IdCliente, Status) INCLUDE (DataPedido, ValorTotal);
GO

-- Dados enviesados: cliente 999 tem MUITOS pedidos
INSERT INTO dbo.Pedidos (IdCliente, DataPedido, Status, ValorTotal)
SELECT TOP (5000) 999, DATEADD(day, ABS(CHECKSUM(NEWID())) % 365, '2024-01-01'), 'Pago', 100.00
FROM sys.all_objects a CROSS JOIN sys.all_objects b;
-- Outros clientes com poucos
INSERT INTO dbo.Pedidos (IdCliente, DataPedido, Status, ValorTotal)
VALUES (1,'2025-08-01','Pago',1200.00), (2,'2025-08-10','Pago',999.90), (3,'2025-08-15','Aberto',350.50);
GO

-- Estatística dedicada ao Status (se não criada automaticamente)
CREATE STATISTICS ST_Pedidos_Status ON dbo.Pedidos(Status);

8.2) Ver estatísticas

DBCC SHOW_STATISTICS ('dbo.Pedidos', 'IX_Pedidos_Cliente_Status') WITH STAT_HEADER, HISTOGRAM;

8.3) Reproduzindo parameter sniffing

CREATE OR ALTER PROC dbo.sp_BuscaPedidos
  @IdCliente int
AS
BEGIN
  SELECT IdPedido, DataPedido, ValorTotal
  FROM dbo.Pedidos
  WHERE IdCliente = @IdCliente AND Status = 'Pago';
END
GO

-- 1) Compila com cliente “frio”
EXEC dbo.sp_BuscaPedidos @IdCliente = 1;
-- 2) Reusa para cliente “quente” (999) — observe o plano/tempo
EXEC dbo.sp_BuscaPedidos @IdCliente = 999;

8.4) Mitigando com RECOMPILE

CREATE OR ALTER PROC dbo.sp_BuscaPedidos_Recompile
  @IdCliente int
AS
BEGIN
  SELECT IdPedido, DataPedido, ValorTotal
  FROM dbo.Pedidos
  WHERE IdCliente = @IdCliente AND Status = 'Pago'
  OPTION (RECOMPILE);
END
GO
EXEC dbo.sp_BuscaPedidos_Recompile @IdCliente = 999;

8.5) Habilitando Query Store e forçando plano

ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

-- Depois de executar consultas e avaliar planos:
-- (Descubra @query_id e @plan_id via consultas nas visões do Query Store)
EXEC sp_query_store_force_plan @query_id = <id>, @plan_id = <id>;

9) Exercícios

1. Criação de estatísticas: Habilite AUTO_CREATE/UPDATE e crie uma estatística filtrada adequada ao seu cenário.
2. DBCC SHOW_STATISTICS: Extraia a data da última atualização e a amostragem das estatísticas de um índice.
3. Parameter sniffing: Construa um proc suscetível e corrija com OPTIMIZE FOR UNKNOWN e compare com RECOMPILE.
4. Query Store: Ative, identifique o top 5 por CPU nos últimos 7 dias e force um plano.
5. Plano base: Guarde um “bom” plano como baseline e documente quando forçar/desforçar.

10) Gabarito (sugestões)

-- 1) Estatísticas (habilitar e filtrada)
ALTER DATABASE MeuDB SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE MeuDB SET AUTO_UPDATE_STATISTICS ON;
CREATE STATISTICS ST_Ativos_UF ON dbo.Clientes(UF) WHERE Ativo = 1;

-- 2) SHOW_STATISTICS
DBCC SHOW_STATISTICS ('dbo.Pedidos', 'IX_Pedidos_Cliente_Status') WITH STAT_HEADER, DENSITY_VECTOR;

-- 3) Sniffing vs UNKNOWN
CREATE OR ALTER PROC dbo.sp_TestSniff
  @IdCliente int
AS
BEGIN
  SELECT IdPedido FROM dbo.Pedidos WHERE IdCliente = @IdCliente
  OPTION (OPTIMIZE FOR (@IdCliente UNKNOWN));
END

-- 4) Query Store top 5 / forçar plano
SELECT TOP 5 q.query_id, p.plan_id, rs.avg_cpu_time, qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
ORDER BY rs.avg_cpu_time DESC;

EXEC sp_query_store_force_plan @query_id = 101, @plan_id = 204;

-- 5) Baseline (documentação + unforce)
EXEC sp_query_store_unforce_plan @query_id = 101, @plan_id = 204;

11) Erros Comuns & Checklist

12) Próximos Passos

• Auditar estatísticas e agendar rotinas de atualização conforme volatilidade dos dados.
• Usar Query Store para detectar regressões após deploys e aplicar correções.
• Preparar o Módulo 20: Planos de Execução & Tuning Avançado (joins, memory grant, spills, hints).