Diagnóstico de Desempenho de Bases de Dados SQL: Identificar Bottlenecks

Uma base de dados lenta não é um problema — é um sintoma. O bottleneck pode estar no CPU, na memória, no disco, nos locks, no plano de execução de uma query ou na configuração do servidor. Este guia apresenta uma metodologia sistemática para identificar a causa real do degradação de desempenho em SQL Server, PostgreSQL e MySQL, com comandos práticos para cada motor.

Neste artigo:

• Metodologia de Diagnóstico
• Ferramentas e Acesso — Onde Executar
• Bottleneck de CPU
• Bottleneck de Memória
• Bottleneck de Disco (I/O)
• Locks e Bloqueios
• Queries Lentas e Planos de Execução
• Índices em Falta e Fragmentação
• Configuração do Servidor
• Checklist de Diagnóstico

Metodologia de Diagnóstico

O erro mais comum é tentar optimizar antes de medir. A metodologia correcta segue quatro passos:

1. Recolher baseline: medir o desempenho actual antes de qualquer alteração. Sem baseline, é impossível saber se a optimização funcionou.
2. Identificar o tipo de bottleneck: CPU, memória, disco, locks ou queries. Cada tipo exige ferramentas e soluções diferentes.
3. Isolar a causa: não aplicar várias optimizações em simultâneo — mudar uma coisa de cada vez e medir o impacto.
4. Validar a melhoria: comparar com o baseline após cada alteração. Se não houver melhoria, reverter.

Ferramentas e Acesso — Onde Executar os Comandos

Antes de executar qualquer comando de diagnóstico, é necessário saber onde e como aceder ao servidor de base de dados. O acesso varia consoante o motor e o ambiente (on-premises ou nuvem).

Permissões Necessárias

Os comandos de diagnóstico neste artigo exigem permissões elevadas. Executar com a conta de administrador do servidor ou um utilizador com as seguintes roles:

• SQL Server: role sysadmin ou serveradmin. Para ler DMVs (Dynamic Management Views) como sys.dm_exec_query_stats, basta a permissão VIEW SERVER STATE.
• PostgreSQL: role superuser ou permissão pg_read_all_stats. As vistas pg_stat_activity e pg_stat_statements exigem a extensão pg_stat_statements activa em postgresql.conf.
• MySQL: privilégio PROCESS para SHOW FULL PROCESSLIST e SELECT sobre performance_schema para as tabelas de diagnóstico.

Todos os comandos apresentados nas secções seguintes são queries SQL que se executam directamente no editor de queries da ferramenta escolhida — não são comandos de sistema operativo nem scripts externos.

Bottleneck de CPU

CPU elevada indica geralmente queries mal optimizadas — table scans em tabelas grandes, funções escalares em loops, ou aggregações sem índice. A primeira acção é identificar quais as queries que consomem mais CPU.

SQL Server

-- Top 10 queries por consumo de CPU (desde último restart)
SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_per_exec,
    qs.execution_count,
    qs.total_worker_time AS total_cpu,
    SUBSTRING(qt.text, 1, 200) AS query_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_worker_time DESC;

-- Sessões activas a consumir CPU neste momento
SELECT session_id, status, cpu_time, logical_reads, text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'running';

PostgreSQL

-- Queries activas neste momento
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       state, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- Top queries por consumo (requer pg_stat_statements)
SELECT calls, total_exec_time, mean_exec_time, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

MySQL

-- Processos activos
SHOW FULL PROCESSLIST;

-- Top queries por consumo (requer Performance Schema)
SELECT digest_text, count_star,
       sum_timer_exec/1000000000 AS total_seg,
       avg_timer_exec/1000000000 AS media_seg
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_exec DESC
LIMIT 10;

Bottleneck de Memória

A memória é o recurso mais crítico para desempenho de bases de dados. Quando a cache não cabe em memória, o servidor passa a ler do disco — ordens de magnitude mais lento. O indicador chave é o buffer cache hit ratio.

SQL Server — Buffer Cache Hit Ratio

-- Hit ratio ideal: > 99%
SELECT
    CAST(SUM(cntr_value) AS bigint) /
    NULLIF(CAST(SUM(cntr_value) AS bigint) -
    SUM(CASE WHEN counter_name = 'Buffer cache hit ratio'
        THEN cntr_value ELSE 0 END), 0) * 100 AS buffer_cache_hit_ratio
FROM sys.dm_os_performance_counters
WHERE counter_name IN
    ('Buffer cache hit ratio', 'Buffer cache hit ratio base');

PostgreSQL — Cache Hit Ratio

-- Por tabela (ideal: > 99%)
SELECT relname,
       heap_blks_read,
       heap_blks_hit,
       round(100.0 * heap_blks_hit /
       NULLIF(heap_blks_hit + heap_blks_read, 0), 2) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 10;

MySQL — InnoDB Buffer Pool

-- Hit ratio do InnoDB Buffer Pool (ideal: > 99%)
SELECT
    (1 - (Innodb_buffer_pool_reads /
           Innodb_buffer_pool_read_requests)) * 100 AS hit_ratio
FROM (SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') a,
    (SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') b;

Bottleneck de Disco (I/O)

Discos lentos afectam todas as queries. O sintoma mais comum são tempos de resposta altos mesmo para queries simples. Medir os IOPS (Input/Output Operations Per Second) e a latência das operações:

SQL Server — Latência de Disco

-- Latência por ficheiro (ideal: < 10ms)
SELECT
    DB_NAME(fs.database_id) AS database_name,
    fs.physical_name,
    fs.io_stall_read_ms / NULLIF(fs.num_of_reads, 0) AS read_latency_ms,
    fs.io_stall_write_ms / NULLIF(fs.num_of_writes, 0) AS write_latency_ms,
    fs.size_on_disk_bytes / 1024 / 1024 AS size_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) fs
ORDER BY fs.io_stall_read_ms + fs.io_stall_write_ms DESC;

PostgreSQL — I/O por Tabela

-- Blocos lidos do disco vs cache
SELECT relname,
       heap_blks_read AS blocos_disco,
       heap_blks_hit AS blocos_cache,
       seq_scan,
       seq_tup_read
FROM pg_stat_user_tables
ORDER BY heap_blks_read DESC
LIMIT 10;

-- Verificar se há demasiados seq scans
SELECT relname, seq_scan, seq_tup_read,
       n_tup_ins + n_tup_upd + n_tup_del AS writes
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC;

Locks e Bloqueios

Locks ocorrem quando duas transacções tentam aceder ao mesmo recurso. Deadlocks ocorrem quando duas transacções bloqueiam-se mutuamente. O sintoma típico são timeouts intermitentes sem causa aparente.

SQL Server — Locks Activos

-- Locks activos neste momento
SELECT
    r.session_id AS blocked_session,
    r.blocking_session_id AS blocking_session,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

-- Deadlocks recentes (Extended Events)
SELECT *
FROM sys.fn_xe_file_target_read_file(
    'system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report';

PostgreSQL — Locks

-- Sessões bloqueadas
SELECT
    blocked.pid AS blocked_pid,
    blocking.pid AS blocking_pid,
    blocked.query AS blocked_query,
    blocking.query AS blocking_query,
    now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.state = 'active';

MySQL — InnoDB Locks

-- Locks do InnoDB
SELECT * FROM performance_schema.data_locks;

-- Sessões bloqueadas
SELECT * FROM performance_schema.data_lock_waits;

-- Estado detalhado do InnoDB
SHOW ENGINE INNODB STATUS;

Queries Lentas e Planos de Execução

A causa mais frequente de degradação não é hardware — é uma query mal escrita. O plano de execução mostra exactamente como o motor processa a query e onde está o problema.

Análise de Plano de Execução

-- SQL Server: plano de execução real
SET STATISTICS IO, TIME ON;
-- executar a query aqui
SET STATISTICS IO, TIME OFF;
-- Verificar: logical reads elevados = table scan

-- PostgreSQL: plano detalhado
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- MySQL: plano de execução
EXPLAIN FORMAT=JSON SELECT ...;

Sinais de problema no plano de execução:

• Table Scan / Seq Scan: a query lê a tabela inteira em vez de usar um índice
• Key Lookup / Bookmark Lookup: o índice não é covering — o motor precisa de voltar à tabela
• Sort com custo elevado: falta índice na coluna ORDER BY
• Hash Match: join sem índices — o motor constrói uma tabela hash em memória
• Spool: o motor materializa resultados intermédios em disco (tempdb) — sinal de memória insuficiente

Índices em Falta e Fragmentação

Índices em falta são a causa nº 1 de queries lentas. Índices fragmentados são a causa nº 1 de degradação gradual ao longo do tempo.

SQL Server — Índices Sugeridos pelo Motor

-- Índices em falta sugeridos pelo Query Optimizer
SELECT TOP 10
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.avg_total_user_cost AS avg_cost,
    migs.avg_user_impact AS impact_percent
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig
    ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs
    ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact *
    (migs.user_seeks + migs.user_scans) DESC;

-- Fragmentação de índices existentes
SELECT
    i.name AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i
    ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

PostgreSQL — Índices Não Usados

-- Indices que nunca foram usados (candidatos a remoção)
SELECT relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Configuração do Servidor

Parâmetros de configuração mal definidos podem limitar o desempenho mesmo com hardware adequado. Os mais impactantes:

Checklist de Diagnóstico

• ☐ Recolher baseline de desempenho (tempo de resposta, throughput)
• ☐ Verificar CPU — identificar top queries por consumo
• ☐ Verificar buffer cache hit ratio (mínimo 95%, ideal 99%)
• ☐ Verificar latência de disco (máximo 10-20ms)
• ☐ Verificar locks activos e deadlocks recentes
• ☐ Analisar plano de execução das queries mais lentas
• ☐ Verificar índices em falta (sugestões do motor)
• ☐ Verificar fragmentação de índices (rebuild se > 30%)
• ☐ Remover índices não usados (peso desnecessário em writes)
• ☐ Verificar configuração de memória (cache, work_mem, buffer pool)
• ☐ Verificar paralelismo e configuração de CPU
• ☐ Activar slow query log / Query Store para monitorização contínua
• ☐ Documentar cada alteração e medir impacto individual

O diagnóstico de desempenho de bases de dados exige método, não adivinhação. Seguir a sequência — medir, identificar o tipo de bottleneck, isolar a causa, validar — evita optimizações inúteis e garante que cada alteração produz melhoria mensurável.