Diagnóstico de Anomalias no SQL Server: Guia Prático para Sysadmins

✎ Duarte Spínola  |  2026-07-12

SQL Server com CPU alta, queries lentas sem razão aparente, bloqueios que impedem utilizadores de trabalhar, ou corrupção detectada pelo DBCC. O diagnóstico começa pelas Dynamic Management Views (DMVs) — vistas de sistema que mostram o estado interno do SQL Server em tempo real. As ferramentas principais são sys.dm_os_wait_stats (o que o SQL Server está à espera), sys.dm_exec_requests (queries a correr agora), sys.dm_tran_locks (bloqueios activos), Query Store (histórico de performance por query) e DBCC CHECKDB (integridade da base de dados). Referência: Monitor and Tune for Performance — Microsoft Learn.

⚠ **Atenção

** Comandos como DBCC CHECKDB com opção REPAIR_ALLOW_DATA_LOSS podem apagar dados. Sempre executar com PHYSICAL_ONLY primeiro (verificação sem reparação) e tentar restaurar de backup antes de reparar.

1. O que Está a Acontecer — Camadas de Diagnóstico

O SQL Server tem quatro camadas onde anomalias se manifestam. Identificar qual camada está afectada determina a ferramenta de diagnóstico correcta.

Camada Sintoma DMV/Ferramenta
CPU CPU >80% persistente sys.dm_os_schedulers, sys.dm_exec_requests
Memory Memory pressure, página life expectancy baixa sys.dm_os_memory_clerks, sys.dm_os_process_memory
IO Disco lento, high latency, log full sys.dm_io_virtual_file_stats, DBCC SQLPERF(logspace)
Locks/Blocks Queries bloqueadas, deadlocks sys.dm_tran_locks, sys.dm_exec_requests
Corrupção Erro 824, 825, suspect database DBCC CHECKDB, errorlog
Wait stats Não se sabe o que está lento sys.dm_os_wait_stats

2. Passo 1 — Wait Stats: O Que o SQL Server Está à Espera

A primeira pergunta num diagnóstico de performance é: o que o SQL Server está à espera? As wait stats mostram exactamente onde o tempo está a ser gasto.

<span style=”color:#6c7086;”>– Top 10 wait types desde o último restart do SQL Server</span>
SELECT TOP 10
wait_type,
waiting_tasks_count,
wait_time_ms,
(wait_time_ms / NULLIF(waiting_tasks_count, 0)) AS avg_wait_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
‘SLEEP_TASK’, ‘BROKER_TASK_STOP’, ‘SQLTRACE_BUFFER_FLUSH’,
‘CLR_AUTO_EVENT’, ‘BROKER_RECEIVE_WAITFOR’, ‘WAITFOR’,
‘LAZYWRITER_SLEEP’, ‘CHECKPOINT_QUEUE’, ‘REQUEST_FOR_DEADLOCK_SEARCH’,
‘XE_DISPATCHER_WAIT’, ‘LOGMGR_QUEUE’, ‘FT_IFTS_SCHEDULER_IDLE_WAIT’,
‘BROKER_TRANSMITTER’, ‘SQLTRACE_WAIT_ENTRIES’, ‘SLEEP_BPOOL_FLUSH’,
‘SLEEP_DBSTARTUP’, ‘TRACEWRITE’, ‘DIRTY_PAGE_POLL’,
‘HADR_FILESTREAM_IOMGR_IOCOMPLETION’, ‘SP_SERVER_DIAGNOSTICS_SLEEP’,
‘DISPATCHER_QUEUE_SEMAPHORE’, ‘XE_TIMER_EVENT’, ‘BROKER_EVENTHANDLER’,
‘BROKER_FORWARDER’, ‘SQLTRACE_LOCK’, ‘LOGMGR_FLUSH’, ‘CHECKPOINT’
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

Interpretação dos waits mais comuns

Wait type Significado Causa provável Aco
CXPACKET Paralelismo — queries a usar paralelismo excessivo MAXDOP mal configurado Reduzir MAXDOP (Cost Threshold para 50+)
PAGEIOLATCH_* À espera de ler páginas do disco Falta de memória (buffer pool pequeno) Adicionar RAM ou optimizar queries com índices
LCK_M_* À espera de locks Bloqueios entre queries Verificar queries longas, índices em falta
ASYNC_NETWORK_IO Cliente não lê resultados rápido Aplicação a processar linhas uma a uma Optimizar aplicação para ler em batch
WRITELOG À espera de escrever no transaction log Disco de log lento Mover log para SSD/NVMe
SOS_SCHEDULER_YIELD CPU saturado — queries a ceder CPU CPU insuficiente Adicionar CPUs ou reduzir carga de queries
CMEMTHREAD Conflito na alocação de memória Muitas queries concorrentes Optimizar ad-hoc queries, usar stored procedures
OLEDB À espera de linked server Linked server lento Evitar linked servers, usar ETL

Referência: sys.dm_os_wait_stats — Microsoft Learn.

3. Passo 2 — Queries Activas e CPU

Para ver o que está a correr agora e quais queries estão a consumir CPU:

<span style=”color:#6c7086;”>– Queries activas com CPU e tempo decorrido</span>
SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.wait_resource,
r.cpu_time,
r.total_elapsed_time,
r.reads,
r.logical_reads,
r.writes,
SUBSTRING(st.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END – r.statement_start_offset)/2) + 1) AS query_text,
DB_NAME(r.database_id) AS database_name,
r.blocking_session_id,
r.row_count
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50 <span style=”color:#6c7086;”>– Ignorar sessions de sistema</span>
ORDER BY r.total_elapsed_time DESC;

O blocking_session_id mostra qual session está a bloquear. Se for diferente de NULL e 0, a query está à espera de outra.

Referência: sys.dm_exec_requests — Microsoft Learn.

Top queries por CPU acumulada

<span style=”color:#6c7086;”>– Top 10 queries por CPU total desde o último restart</span>
SELECT TOP 10
qs.total_worker_time / qs.execution_count AS avg_cpu_per_exec,
qs.total_worker_time AS total_cpu,
qs.execution_count,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_per_exec,
SUBSTRING(st.text, 1, 200) AS query_text,
DB_NAME(qp.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

Referência: sys.dm_exec_query_stats — Microsoft Learn.

4. Passo 3 — Bloqueios e Deadlocks

4.1 Verificar locks activos

<span style=”color:#6c7086;”>– Locks activos no momento</span>
SELECT
t.request_session_id AS session_id,
t.resource_type,
t.resource_database_id,
t.resource_associated_entity_id,
t.request_mode,
t.request_status,
DB_NAME(t.resource_database_id) AS database_name,
(SELECT text FROM sys.dm_exec_sql_text(r.sql_handle)) AS query_text
FROM sys.dm_tran_locks t
LEFT JOIN sys.dm_exec_requests r ON t.request_session_id = r.session_id
WHERE t.request_status = ‘WAIT’
ORDER BY t.request_session_id;

Referência: sys.dm_tran_locks — Microsoft Learn.

4.2 Árvore de bloqueios

Quando uma query bloqueia outra que bloqueia outra, é preciso ver a cadeia completa:

<span style=”color:#6c7086;”>– Árvore de bloqueios (blocking chain)</span>
WITH blocking_chain AS (
SELECT
r.session_id,
r.blocking_session_id,
r.wait_type,
r.wait_time,
r.wait_resource,
CAST(” AS VARCHAR(1000)) AS chain,
1 AS level
FROM sys.dm_exec_requests r
WHERE r.blocking_session_id = 0
AND r.session_id IN (
SELECT blocking_session_id FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
)

UNION ALL

SELECT
r.session_id,
r.blocking_session_id,
r.wait_type,
r.wait_time,
r.wait_resource,
CAST(bc.chain + ‘ -> ‘ + CAST(r.session_id AS VARCHAR(10)) AS VARCHAR(1000)),
bc.level + 1
FROM sys.dm_exec_requests r
INNER JOIN blocking_chain bc ON r.blocking_session_id = bc.session_id
WHERE r.blocking_session_id > 0
)
SELECT * FROM blocking_chain
ORDER BY chain, level;

4.3 Quem está a bloquear (o head blocker)

<span style=”color:#6c7086;”>– Head blocker — a query que está na origem de todos os bloqueios</span>
SELECT
r.session_id,
r.status,
r.cpu_time,
r.total_elapsed_time,
r.reads,
r.logical_reads,
(SELECT text FROM sys.dm_exec_sql_text(r.sql_handle)) AS query_text,
s.host_name,
s.program_name,
s.login_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.session_id IN (
SELECT blocking_session_id FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
)
AND r.blocking_session_id = 0;

4.4 Activar captura de deadlocks

Para capturar deadlocks automaticamente, activar trace flags ou Extended Events:

<span style=”color:#6c7086;”>– Activar flag de trace 1222 (deadlock graph no errorlog)</span>
DBCC TRACEON(1222, -1);

<span style=”color:#6c7086;”>– Ou via Extended Events (mais detalhado, SQL Server 2012+)</span>
<span style=”color:#6c7086;”>– SSMS → Management → Extended Events → Sessions → system_health</span>
<span style=”color:#6c7086;”>– O system_health já captura deadlocks por defeito</span>

5. Passo 4 — Memória e Buffer Pool

5.1 Memory pressure

<span style=”color:#6c7086;”>– Estado da memória do SQL Server</span>
SELECT
physical_memory_kb / 1024 AS physical_memory_mb,
locked_page_allocations_kb / 1024 AS locked_pages_mb,
large_page_allocations_kb / 1024 AS large_pages_mb,
pagefault_count,
system_high_memory_signal_state,
system_low_memory_signal_state,
available_physical_memory_kb / 1024 AS available_memory_mb,
total_page_file_kb / 1024 AS total_page_file_mb
FROM sys.dm_os_process_memory;

Referência: sys.dm_os_process_memory — Microsoft Learn.

5.2 Buffer pool — Page Life Expectancy

<span style=”color:#6c7086;”>– Page Life Expectability (PLE) — quanto tempo as páginas ficam em memória</span>
<span style=”color:#6c7086;”>– PLE < 300 = memory pressure severa</span>
SELECT
cntr_value AS page_life_expectancy_seconds
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Page life expectancy’
AND instance_name = ‘_Total’;

Interpretação:

  • PLE > 3000 — memória saudável
  • PLE 300-3000 — atenção, possível pressão
  • PLE < 300 — memory pressure severa, queries a ler do disco constantemente

5.3 Top memory consumers

<span style=”color:#6c7086;”>– Top consumers de memria no SQL Server</span>
SELECT TOP 10
memory_clerk_name,
SUM(pages_kb) / 1024 AS memory_mb
FROM sys.dm_os_memory_clerks
GROUP BY memory_clerk_name
ORDER BY SUM(pages_kb) DESC;

Referência: sys.dm_os_memory_clerks — Microsoft Learn.

6. Passo 5 — IO e Disco

6.1 Latência por ficheiro de base de dados

<span style=”color:#6c7086;”>– Latncia de IO por ficheiro — identifica discos lentos</span>
SELECT
DB_NAME(fs.database_id) AS database_name,
mf.physical_name,
fs.sample_ms / 1000 AS uptime_seconds,
fs.num_of_reads,
fs.num_of_writes,
fs.bytes_read / 1024 / 1024 AS read_mb,
fs.bytes_written / 1024 / 1024 AS written_mb,
fs.io_stall_read_ms,
fs.io_stall_write_ms,
CASE WHEN fs.num_of_reads > 0
THEN fs.io_stall_read_ms / fs.num_of_reads
ELSE 0
END AS avg_read_latency_ms,
CASE WHEN fs.num_of_writes > 0
THEN fs.io_stall_write_ms / fs.num_of_writes
ELSE 0
END AS avg_write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) fs
JOIN sys.master_files mf ON fs.database_id = mf.database_id
AND fs.file_id = mf.file_id
ORDER BY (fs.io_stall_read_ms + fs.io_stall_write_ms) DESC;

Interpretação:

  • < 5ms — excelente (SSD/NVMe)
  • 5-15ms — aceitável (SSD)
  • 15-30ms — atenção (disco lento ou sobrecarga)
  • > 30ms — problema grave (IO bottleneck)

Referência: sys.dm_io_virtual_file_stats — Microsoft Learn.

6.2 Transaction log

<span style=”color:#6c7086;”>– Espao usado no transaction log</span>
DBCC SQLPERF(LOGSPACE);

Se o log estiver com mais de 80% usado e não encolhe após backup, pode haver uma transacção aberta que impede o truncamento:

<span style=”color:#6c7086;”>– Transaces abertas há mais tempo</span>
SELECT
s.session_id,
s.host_name,
s.program_name,
s.login_name,
t.transaction_begin_time,
DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS open_seconds,
at.name AS transaction_name,
(SELECT text FROM sys.dm_exec_sql_text(r.sql_handle)) AS query_text
FROM sys.dm_tran_session_transactions t
JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
JOIN sys.dm_tran_active_transactions at ON t.transaction_id = at.transaction_id
ORDER BY t.transaction_begin_time;

Referência: DBCC SQLPERF — Microsoft Learn.

7. Passo 6 — Query Store: Histórico de Performance

O Query Store (disponível desde SQL Server 2016) é a ferramenta mais potente para diagnóstico histórico de performance. Registra automaticamente performance de todas as queries sem necessidade de tracing.

7.1 Verificar se Query Store está activo

<span style=”color:#6c7086;”>– Estado do Query Store por base de dados</span>
SELECT
name AS database_name,
is_query_store_on,
actual_state_desc
FROM sys.databases
WHERE is_query_store_on = 1;

7.2 Top queries por tempo médio

<span style=”color:#6c7086;”>– Top 10 queries por tempo médio no Query Store</span>
SELECT TOP 10
qs.query_id,
qt.query_sql_text,
rs.avg_duration / 1000 AS avg_duration_ms,
rs.avg_cpu_time / 1000 AS avg_cpu_ms,
rs.avg_logical_io_reads,
rs.count_executions,
p.plan_id
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
ORDER BY rs.avg_duration DESC;

7.3 Queries com regressão de performance

<span style=”color:#6c7086;”>– Queries que pioraram de performance recentemente</span>
SELECT
q.query_id,
qt.query_sql_text,
rs1.avg_duration / 1000 AS old_avg_ms,
rs2.avg_duration / 1000 AS new_avg_ms,
(rs2.avg_duration – rs1.avg_duration) / 1000 AS delta_ms
FROM sys.query_store_runtime_stats rs1
JOIN sys.query_store_runtime_stats rs2 ON rs1.plan_id = rs2.plan_id
JOIN sys.query_store_plan p ON rs1.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rs1.runtime_stats_id < rs2.runtime_stats_id
AND rs2.avg_duration > rs1.avg_duration * 2
ORDER BY (rs2.avg_duration – rs1.avg_duration) DESC;

Referência: Monitoring Performance by Using the Query Store — Microsoft Learn.

8. Passo 7 — DBCC CHECKDB: Verificar Integridade

7.1 Verificação sem reparação (sempre primeiro)

<span style=”color:#6c7086;”>– Verificar integridade sem reparar (recomendado)</span>
DBCC CHECKDB(‘nome_da_base’) WITH PHYSICAL_ONLY;

<span style=”color:#6c7086;”>– Verificação completa (mais lenta, mais detalhada)</span>
DBCC CHECKDB(‘nome_da_base’) WITH ALL_ERRORMSGS, NO_INFOMSGS;

7.2 Se CHECKDB detectar corrupção

Se o resultado mostra erros:

  1. NÃO usar REPAIR_ALLOW_DATA_LOSS — tenta restaurar de backup primeiro
  2. Verificar se o erro é lógico (página corrompida) ou físico (disco avariado)
  3. Se há backup recente, restaurar a base de dados
  4. Se não há backup, colocar a base em EMERGENCY mode e extrair dados
<span style=”color:#6c7086;”>– Colocar base em EMERGENCY para extrair dados</span>
ALTER DATABASE nome_da_base SET EMERGENCY;

<span style=”color:#6c7086;”>– Tentar leitura dos dados</span>
DBCC CHECKDB(‘nome_da_base’) WITH TABLOCK;

Referência: DBCC CHECKDB — Microsoft Learn.

7.3 Agendar CHECKDB automático

<span style=”color:#6c7086;”>– Job do SQL Server Agent para CHECKDB semanal</span>
<span style=”color:#6c7086;”>– Correr de noite, fora de horas de pico</span>
<span style=”color:#6c7086;”>– PHISICAL_ONLY é mais rápido e detecta a maioria dos problemas</span>
USE msdb;
GO

<span style=”color:#6c7086;”>– Criar job (via SSMS ou script)</span>
<span style=”color:#6c7086;”>– Schedule: Semanal, Domingo às 02:00</span>
<span style=”color:#6c7086;”>– Command:</span>
<span style=”color:#6c7086;”>– EXEC sp_MSforeachdb ‘DBCC CHECKDB(”?”) WITH PHYSICAL_ONLY, NO_INFOMSGS’;</span>

9. Passo 8 — Errorlog e Eventos do Sistema

9.1 Erros recentes no SQL Server errorlog

<span style=”color:#6c7086;”>– Ler errorlog via T-SQL</span>
EXEC sp_readerrorlog 0, 1, ‘Error’;

<span style=”color:#6c7086;”>– Procurar erros de severidade alta (17+)</span>
EXEC sp_readerrorlog 0, 1, ‘Severity: 1[789]’;

Erros importantes a procurar:

Erro Significado Aco
824 Página lógica ilegível — checksum falhou DBCC CHECKDB, verificar disco
825 IO retry — leitura falhou mas tentou novamente Disco em falha iminente
823 Erro de IO a nível do SO Verificar Event Viewer do Windows
829 Página marcada como suspect DBCC CHECKDB, restore de backup
701 Out of memory Aumentar max server memory
1204 Deadlock victim — lock monitor não pode alocar Optimizar queries

9.2 Schedulers — CPU health

<span style=”color:#6c7086;”>– Estado dos schedulers de CPU</span>
SELECT
scheduler_id,
cpu_id,
is_online,
current_tasks_count,
runnable_tasks_count,
pending_disk_io_count,
work_queue_count,
yield_count,
failed_to_create_worker
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255 <span style=”color:#6c7086;”>– Ignorar hidden schedulers</span>
ORDER BY scheduler_id;

Se runnable_tasks_count é consistentemente > 0, o SQL Server tem mais queries prontas para executar do que CPUs disponíveis — CPU bottleneck.

Referência: sys.dm_os_schedulers — Microsoft Learn.

10. Erros Comuns e Resolução

  • CPU 100% sem queries visíveis — pode ser compilação de ad-hoc queries. Verificar sys.dm_exec_query_stats por query_hash duplicados. Solução: activar OPTIMIZE_FOR_AD_HOC_WORKLOADS ou converter ad-hoc queries em stored procedures.
  • TempDB cheio — queries com sorts e joins grandes enchem TempDB. Verificar sys.dm_db_file_space_usage para ver o que está a consumir. Solução: adicionar ficheiros TempDB (1 por core, máximo 8), aumentar tamanho.
  • Transaction log não encolhe após backup — há uma transacção aberta que impede o truncamento. Verificar com sys.dm_tran_session_transactions (Passo 6) e matar a transacção se for de uma sessão zombie.
  • Database Suspect — corrupção ou desligamento abrupto. Verificar errorlog, tentar DBCC CHECKDB em EMERGENCY mode, e se não resolver, restaurar de backup.
  • Deadlocks frequentes — tipicamente causados por ordem de acesso a tabelas diferente entre queries. Solução: garantir que todas as stored procedures acedem às tabelas na mesma ordem, e usar READ COMMITTED SNAPSHOT isolation level.
  • Memory pressure com RAM suficiente — max server memory pode estar mal configurado. Verificar com sp_configure 'max server memory'. Deixar 4-8 GB para o SO e dar o resto ao SQL Server.
  • Linked server lento — ASYNC_NETWORK_IO wait. Linked servers forçam processamento no servidor remoto. Para grandes volumes, usar OPENQUERY ou ETL em vez de joins cross-server.
  • Query lenta subitamente — plan change. Verificar no Query Store se houve regressão (Passo 7.3). Se a query tinha um plano bom que mudou, usar sp_query_store_force_plan para forar o plano anterior.

11. Checklist de Diagnóstico

Quando o SQL Server está lento ou com anomalias, seguir esta sequência:

  1. Wait stats — sys.dm_os_wait_stats para identificar a categoria do problema (CPU, IO, locks, memória)
  2. Requests activos — sys.dm_exec_requests para ver o que está a correr agora
  3. Blocking chain — identificar o head blocker e a query de origem
  4. CPU — sys.dm_os_schedulers para confirmar se há CPU bottleneck
  5. Memória — Page Life Expectancy e sys.dm_os_memory_clerks
  6. IO — sys.dm_io_virtual_file_stats para latência por ficheiro
  7. Query Store — histórico de performance para identificar regressões
  8. Errorlog — sp_readerrorlog para erros 824/823/701
  9. DBCC CHECKDB — integridade da base de dados
  10. Action plan — baseado no diagnóstico, aplicar a correco adequada

12. Artigos Relacionados

Este artigo foi útil?

Duarte Spínola

Artigos  

Deixe um Comentário