Plano passo a passo (prático) para melhorar a performance de um banco SQL Server

6

Beleza — segue um plano passo a passo direto ao ponto para você executar no SQL Server. Cada passo tem objetivo, comandos/queries práticos e recomendações de verificação/rollback. Faça em um ambiente de teste antes de aplicar em produção quando possível.

---

1) Medir e criar baseline

Objetivo: saber onde estamos antes de mudar qualquer coisa.

Ações:

• Registrar métricas por 24–72h: CPU, memória, I/O, latência de disco, waits.
• Capturar queries pesadas e plano de execução.

Comandos úteis (execute no SSMS):

-- Top 50 queries por tempo total
SELECT TOP 50
    qs.total_elapsed_time/1000000.0 AS total_seconds,
    qs.execution_count,
    (qs.total_elapsed_time/qs.execution_count)/1000000.0 AS avg_seconds,
    SUBSTRING(qt.text,1,2000) AS sql_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_elapsed_time DESC;

Verifique também waits:

SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

Guarde resultados (CSV) para comparar depois.

---

2) Identificar gargalos principais

Objetivo: descobrir se o problema é CPU, IO, locks, network ou queries.

Como:

• Se PAGEIOLATCH domina → I/O disk.
• Se CXPACKET e alta CPU → paralelismo / MAXDOP.
• Se LCK_* → problemas de locking/long transactions.

Use a query de waits (acima) e sys.dm_io_virtual_file_stats:

SELECT DB_NAME(database_id) AS DBName, file_id, num_of_reads, num_of_writes, io_stall_read_ms, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(null, null)
ORDER BY io_stall_read_ms+io_stall_write_ms DESC;

---

3) Atualizar estatísticas e verificar planos

Objetivo: garantir planos eficientes.

Ações:

-- Atualizar todas as estatísticas do banco (menos intrusivo)
EXEC sp_updatestats;

-- Ou para uma tabela específica
UPDATE STATISTICS dbo.SuaTabela WITH FULLSCAN;

Depois, valide planos (re-run queries problemáticas e capture plano).

---

4) Revisar e otimizar índices (mais impacto)

Objetivo: reduzir leituras físicas e melhorar seeks.

Processo:

1. Detectar índices não usados / com baixa utilidade.
2. Detectar índices fragmentados.
3. Adicionar índices faltantes para queries críticas.
4. Remover índices duplicados ou pouco usados.

Consultas para análise:

-- Índices não usados
SELECT db_name() AS DBName, OBJECT_NAME(s.object_id) AS TableName,
       i.name AS IndexName, i.index_id,
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE database_id = DB_ID()
ORDER BY s.user_seeks + s.user_scans + s.user_lookups;

-- Fragmentação
SELECT OBJECT_NAME(ps.object_id) AS TableName, i.name, ps.index_id, 
       avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE avg_fragmentation_in_percent > 5
ORDER BY avg_fragmentation_in_percent DESC;

Manutenção (reorganize vs rebuild):

• Fragmentação 5–30%: ALTER INDEX ... REORGANIZE
• Fragmentação >30%: ALTER INDEX ... REBUILD WITH (ONLINE = ON) (se edição Enterprise / suporte para ONLINE)

Exemplo:

ALTER INDEX ALL ON dbo.SuaTabela REORGANIZE;
-- ou
ALTER INDEX ALL ON dbo.SuaTabela REBUILD WITH (ONLINE = ON);

Cuidado: rebuild gera I/O e pode consumir espaço tempdb.

---

5) Corrigir queries caras (query tuning)

Objetivo: trocar scans por seeks, reduzir leituras.

Ações práticas:

• Use SET STATISTICS IO ON; SET STATISTICS TIME ON; para medir.
• Reescrever queries, evitar SELECT *, evitar funções sobre colunas em WHERE, evitar subqueries que causem nested loops grandes.
• Considerar hints com cautela (FORCESEEK, OPTION (RECOMPILE) apenas quando apropriado).

Exemplo de análise:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- execute sua query pesada aqui

Para parámetro sniffing: usar OPTIMIZE FOR UNKNOWN, OPTION (RECOMPILE) ou plan guides quando necessário.

---

6) Stored procedures e planos recompilação

Objetivo: reduzir tráfego e melhorar reutilização de planos.

Ações:

• Transformar queries repetidas em stored procedures parametrizadas.
• Monitorar planos com planos compilados frequentemente (sys.dm_exec_cached_plans).

---

7) Configurações do servidor

Objetivo: ajustar engine para o workload.

Itens-chave:

• max server memory — evitar que SQL consuma toda a memória do servidor (deixe memória para SO).
• max degree of parallelism (MAXDOP) — ajustar conforme CPU cores e carga. Regra comum: cores <= 8 → MAXDOP = cores; grandes servidores normalmente 4–8; evitar 0 indiscriminadamente.
• cost threshold for parallelism — aumentar se houver muitas execuções paralelas pequenas (ex.: 50 ou 100).
• affinity e priority boost normalmente em default.

Exemplo para ajustar memória:

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 32768; -- exemplo: 32GB
RECONFIGURE;

MAXDOP:

EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

---

8) Tempdb tuning

Objetivo: evitar contenção em tempdb.

Recomendações:

• Múltiplos arquivos de dados tempdb (1 por vCPU até 8; depois medir).
• Arquivos com mesmo tamanho e autogrowth controlado.
• Colocar tempdb em disco rápido.

Exemplo (reinicia necessário):

-- Adicionar arquivo (ajuste caminho e tamanho)
ALTER DATABASE tempdb 
ADD FILE (NAME = tempdev2, FILENAME = 'E:\MSSQL\TEMPDB\tempdb_mssql2.ndf', SIZE = 2000MB, FILEGROWTH = 512MB);

---

9) Reduzir locking e length of transactions

Objetivo: diminuir bloqueios concorrentes.

Ações:

• Manter transações curtas.
• Usar READ_COMMITTED_SNAPSHOT para reduzir bloqueios de leitura (testar antes):

ALTER DATABASE SeuBD SET READ_COMMITTED_SNAPSHOT ON;

• Evitar cursores ou processamentos linha-a-linha quando possível.

---

10) Automatizar manutenção e monitoramento

Objetivo: garantir rotina contínua de saúde.

Tarefas que agendar:

• Atualização de estatísticas (fullscan ou sampled regularmente).
• Rebuild/Reorganize índices (segundo fragmentação).
• Backup e verificação de integridade (DBCC CHECKDB) agendado em janela de manutenção.
• Coleta de perf counters e armazenamento histórico (para regressão).

Exemplo básico para checar integridade:

DBCC CHECKDB('SeuBD') WITH NO_INFOMSGS;

---

11) Monitorar após mudanças (validação)

Objetivo: confirmar impacto das mudanças.

Como:

• Re-executar os mesmos relatórios do baseline (queries top, waits, I/O) e comparar.
• Medir antes/depois para cada mudança e manter changelog.

---

12) Ações em caso de regressão (rollback)

Objetivo: reverter rapidamente se algo piorar.

Boas práticas:

• Antes de mudança, salvar scripts de criação/remoção de índice.
• Fazer snapshots (quando aplicável), backups completos antes de grandes rebuilds.
• Testar em staging.

Exemplo: se criou índice A e percebeu impacto negativo, rode:

DROP INDEX idx_nome ON dbo.SuaTabela;

---

13) Capacidade e infra (quando problema for I/O ou memória)

Objetivo: escalar infra quando tuning não resolve.

Considerar:

• SSDs NVMe para data/log/tempdb.
• Mais RAM.
• Separar LUNs para logs / data / tempdb.
• Balancear alta disponibilidade (Always On / replication) se leitura for intensiva.

---

14) Checklist rápido de execução (ordem sugerida)

1. Baseline (24–72h) + capturar planos.
2. Atualizar estatísticas (sp_updatestats).
3. Revisar índices: identificar fragmentation + unused.
4. Reorganize/rebuild índices conforme fragmentação.
5. Otimizar queries críticas (com planos e STATISTICS).
6. Ajustar server config (max memory, MAXDOP) se necessário.
7. Tunar tempdb (arquivos iguais).
8. Habilitar RCSI se apropriado (testar).
9. Automatizar manutenção e monitoramento.
10. Validar impacto com baseline comparativo.

---