Módulo 18 — Índices & Otimização de Consultas (Parte I)

1) Fundamentos de Índices

• Heap: tabela sem índice clusterizado. RID Lookup em nonclustered.
• Índice Clusterizado (CI): define a ordenação física (B-Tree). Apenas um por tabela.
• Índice Não-Clusterizado (NCI): estrutura B-Tree separada com ponteiros para a linha (chave do CI ou RID).

2) Tipos: Clustered, Nonclustered, Unique, Filtered

-- Clustered (escolha com parcimônia: estável, seletiva, usada em buscas/ordenações)
CREATE UNIQUE CLUSTERED INDEX CI_Pedidos ON dbo.Pedidos (IdPedido);

-- Nonclustered com INCLUDE (cobertura)
CREATE NONCLUSTERED INDEX IX_Pedidos_Cliente_Data
ON dbo.Pedidos (IdCliente, DataPedido)
INCLUDE (ValorTotal, Status);

-- Filtered Index (conjunto menor = menos custo de escrita/armazenamento)
CREATE NONCLUSTERED INDEX IX_Pedidos_Abertos
ON dbo.Pedidos (IdCliente)
WHERE Status = 'Aberto';

3) Como a busca funciona: Seek, Scan, Lookup

• Index Seek: navegação por chave (rápido) — requer condição SARGable.
• Index/Table Scan: varredura completa — OK para pequenas tabelas/baixa seletividade.
• Key/RID Lookup: busca residual na tabela/CI para colunas não cobertas pelo NCI.

-- Ex.: lookup por falta de cobertura
SELECT DataPedido, ValorTotal
FROM dbo.Pedidos
WHERE IdCliente = 123 AND Status = 'Pago';  -- se NCI cobre (IdCliente,Status) mas não as colunas selecionadas, haverá lookup

4) Composição de chaves & INCLUDE

• Regra do prefixo à esquerda: um NCI em (A,B,C) serve para filtros em A; em A+B; em A+B+C — não para WHERE B=... isolado.
• Ordene colunas por seletividade e padrão de consultas (joins/ordenação/filtros).
• Colunas grandes/voláteis → prefira INCLUDE (não fazem parte da chave).
• Evite chaves clusterizadas largas/voláteis (impactam todos os NCIs).

-- Cobrindo consulta: filtro por Cliente+Data, retorna Valor e Status
CREATE NONCLUSTERED INDEX IX_Pedidos_Cliente_Data_incl
ON dbo.Pedidos (IdCliente, DataPedido)
INCLUDE (ValorTotal, Status);

5) SARGability (filtros indexáveis)

Condições devem permitir que o otimizador navegue pela árvore do índice — evite funções na coluna, conversões implícitas, curingas iniciais etc.

-- Não SARGable (função em coluna)
WHERE YEAR(DataPedido) = 2025
-- SARGable (intervalo)
WHERE DataPedido >= '2025-01-01' AND DataPedido < '2026-01-01'

-- Conversão implícita (coluna varchar, parâmetro int) invalida seek
WHERE CPF = 12345678901    -- ERRADO se CPF é varchar
WHERE CPF = '12345678901'  -- CERTO (ou normalize o tipo)

-- LIKE com curinga inicial quebra seek
WHERE Nome LIKE '%Silva'   -- scan
WHERE Nome LIKE 'Silva%'   -- pode usar seek (índice por Nome)

6) Planos de Execução & Métricas

-- Habilitar métricas
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
-- Estimado vs Real (SSMS): Ctrl+L (Estimado), Ctrl+M (Real)

• Operadores-chave: Index Seek/Scan, Key/RID Lookup, Nested Loops, Hash Match, Merge Join.
• Predicado Residual: filtro aplicado após o seek; busque levá-lo à chave/INCLUDE quando possível.
• Custo ≠ Tempo real: é heurístico; use IO/tempo para comparar execuções.

7) Manutenção (Fill Factor & Fragmentação)

• Fill Factor: espaço livre por página; reduzir pode diminuir page splits em cargas de escrita.
• Fragmentação: verifique com sys.dm_db_index_physical_stats.
• Reorganizar (leve) vs Rebuild (pesado). Agende janelas de manutenção.

-- Exemplo de rebuild com fill factor
ALTER INDEX IX_Pedidos_Cliente_Data_incl ON dbo.Pedidos
REBUILD WITH (FILLFACTOR = 90, ONLINE = ON);  -- ONLINE Enterprise

8) DMVs úteis (missing/usage)

-- Índices sugeridos pelo otimizador (use com cautela; avalie workload)
SELECT TOP 20
  migs.group_handle, mid.statement AS TableName,
  mid.equality_columns, mid.inequality_columns, mid.included_columns,
  migs.unique_compiles, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
ORDER BY migs.avg_user_impact DESC;

-- Uso de índices (para achar “não usados” ou “quentes”)
SELECT OBJECT_NAME(s.[object_id]) AS Tabela, i.name AS Indice,
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats AS s
JOIN sys.indexes AS i ON i.[object_id]=s.[object_id] AND i.index_id=s.index_id
WHERE s.database_id = DB_ID()
ORDER BY (s.user_seeks + s.user_scans + s.user_lookups) DESC;

9) Laboratório Guiado

9.1) Setup

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   datetime2(0) NOT NULL,
  Status       varchar(20) NOT NULL,
  ValorTotal   decimal(12,2) NOT NULL
);
GO
-- Índice de cobertura para consultas por cliente/data
CREATE NONCLUSTERED INDEX IX_Pedidos_Cliente_Data
ON dbo.Pedidos (IdCliente, DataPedido)
INCLUDE (ValorTotal, Status);
GO
-- Dados mínimos
INSERT INTO dbo.Pedidos (IdCliente, DataPedido, Status, ValorTotal)
SELECT v.IdCliente, v.DataVenda, 'Pago', v.Valor
FROM (VALUES
  (1, CAST('2025-08-01' AS date), 1200.00),
  (1, CAST('2025-08-15' AS date),  350.50),
  (2, CAST('2025-08-10' AS date),  999.90)
) AS v(IdCliente, DataVenda, Valor);

9.2) Seek vs Scan

-- Seek (SARGable)
SELECT ValorTotal
FROM dbo.Pedidos
WHERE IdCliente = 1 AND DataPedido >= '2025-08-01' AND DataPedido < '2025-09-01';

-- Scan (não SARGable por função)
SELECT ValorTotal
FROM dbo.Pedidos
WHERE IdCliente = 1 AND MONTH(DataPedido) = 8;  -- evitar

9.3) Eliminando Lookup com INCLUDE

-- Selecione Status também; se não estiver em INCLUDE, haverá Key Lookup
SELECT Status, ValorTotal
FROM dbo.Pedidos
WHERE IdCliente = 1 AND DataPedido >= '2025-08-01' AND DataPedido < '2025-09-01';

9.4) Índice filtrado para “abertos”

CREATE NONCLUSTERED INDEX IX_Pedidos_Abertos
ON dbo.Pedidos (IdCliente, DataPedido)
WHERE Status = 'Aberto';

9.5) Medindo IO/TEMPO

SET STATISTICS IO ON; SET STATISTICS TIME ON;
GO
SELECT SUM(ValorTotal)
FROM dbo.Pedidos
WHERE IdCliente = 1 AND DataPedido >= '2025-08-01' AND DataPedido < '2025-09-01';
GO
SET STATISTICS IO OFF; SET STATISTICS TIME OFF;

10) Exercícios

1. Escolha de CI: Dada a tabela Pedidos, proponha uma chave clusterizada melhor que IdPedido (se aplicável) e justifique.
2. Cobertura: Crie um NCI que elimine lookups na consulta por IdCliente+DataPedido que retorna ValorTotal e Status.
3. Filtro de período: Reescreva um filtro não SARGable de mês/ano para intervalo >=/<.
4. Filtered Index: Crie um índice apenas para Status='Pago' e mostre um exemplo que o use.
5. DMVs: Liste os 5 índices mais usados e 5 menos usados no seu banco.

11) Gabarito (sugestões)

-- 1) CI sugerido (depende do workload; exemplo: DataPedido,IdPedido)
-- Útil se a maioria das consultas é por período cronológico
CREATE UNIQUE CLUSTERED INDEX CI_Pedidos_Data_Id
ON dbo.Pedidos (DataPedido, IdPedido);

-- 2) Cobertura (já criada no lab)
CREATE NONCLUSTERED INDEX IX_Pedidos_Cliente_Data
ON dbo.Pedidos (IdCliente, DataPedido)
INCLUDE (ValorTotal, Status);

-- 3) Filtro SARGable
WHERE DataPedido >= '2025-08-01' AND DataPedido < '2025-09-01';

-- 4) Filtered Index + uso
CREATE NONCLUSTERED INDEX IX_Pedidos_Pagos
ON dbo.Pedidos (IdCliente, DataPedido)
WHERE Status = 'Pago';
GO
SELECT SUM(ValorTotal)
FROM dbo.Pedidos WITH (INDEX(IX_Pedidos_Pagos))
WHERE Status = 'Pago' AND IdCliente = 1
  AND DataPedido >= '2025-08-01' AND DataPedido < '2025-09-01';

-- 5) DMVs (mais/menos usados)
SELECT TOP 5 OBJECT_NAME(s.[object_id]) AS Tabela, i.name AS Indice, s.*
FROM sys.dm_db_index_usage_stats AS s
JOIN sys.indexes AS i ON i.[object_id]=s.[object_id] AND i.index_id=s.index_id
WHERE s.database_id = DB_ID()
ORDER BY (s.user_seeks + s.user_scans + s.user_lookups) DESC;

SELECT TOP 5 OBJECT_NAME(i.[object_id]) AS Tabela, i.name AS Indice
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
  ON s.[object_id]=i.[object_id] AND s.index_id=i.index_id AND s.database_id = DB_ID()
WHERE i.is_hypothetical = 0 AND i.index_id > 0
ORDER BY COALESCE(s.user_seeks + s.user_scans + s.user_lookups, 0) ASC;

12) Erros Comuns & Checklist

13) Próximos Passos

• Migrar consultas reais para padrões SARGable.
• Revisar carteira de índices com DMVs (uso × custo).
• Preparar o Módulo 19: Índices & Otimização (Parte II) — Estatísticas, Parameter Sniffing, Query Store.