Módulo 30 — Monitoramento & Observabilidade: DMVs, Extended Events & Wait Stats

1) Métricas-Chave & Baselines

• CPU, IO, memória, waits, batches/sec.

2) DMVs

SELECT * FROM sys.dm_exec_requests;
SELECT * FROM sys.dm_os_wait_stats;
SELECT * FROM sys.dm_db_index_usage_stats;

3) Wait Statistics

SELECT TOP 10 * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;

4) Extended Events

CREATE EVENT SESSION SlowQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
  ACTION(sqlserver.client_app_name,sqlserver.sql_text)
  WHERE duration >= 5000000)
ADD TARGET package0.event_file(SET filename='D:\\xe\\slowqueries.xel');

5) PerfMon & Alerts

• Contadores do SO e alertas por Agent.

6) Query Store

• Detecte regressões e force plano se preciso.

7) Laboratório

-- Correlacionar waits e top queries.

8) Exercícios

1. XE para deadlocks; top waits.

9) Gabarito

ALTER EVENT SESSION SlowQueries ON SERVER STATE=START;

10) Boas Práticas

• Construa baseline; compare variações.

11) Próximos Passos

• Encerrar trilha 31–40.