Como criar um alerta automático de CPU alta no SQL Server com identificação de Queries Ofensoras
Quem administra bancos de dados sabe bem: quando o uso de CPU chega a níveis críticos (como 90% ou mais), a lentidão é generalizada e os chamados começam a chegar. Em vez de reagir apenas quando os usuários reclamam, que tal implementar um monitoramento proativo para o seu SQL Server?
Nesta postagem, apresentamos uma solução automatizada via Stored Procedure (dbo.stp_alerta_cpu_critico) que atua como um "vigilante" do servidor, identificando gargalos e notificando a equipe por e-mail no momento exato em que a CPU ultrapassa o limite de segurança, detalhando exatamente quais consultas estão consumindo mais processamento.
O que este script faz?
A rotina funciona como um monitor de saúde contínuo da sua instância. A cada execução agendada no SQL Server Agent, ela realiza as seguintes etapas:
Mede o consumo total de CPU: Lê as informações internas da DMV sys.dm_os_ring_buffers e sys.dm_os_sys_info para extrair a porcentagem total de processamento da máquina, separando o consumo do SQL Server, do Sistema Operacional/Outros processos e o tempo ocioso (Idle);
Avalia o limite de segurança: Verifica se o uso total da CPU atingiu ou ultrapassou o limite configurado (por padrão, 90%);
Identifica as consultas "vilãs": Se o limite for atingido, a Procedure varre o cache do SQL Server (sys.dm_exec_query_stats e sys.dm_exec_sql_text) ordenando pelo maior tempo total de CPU (total_worker_time) para extrair as Top 4 consultas mais pesadas;
Dispara o alerta formatado em HTML: Monta e envia um e-mail estruturado contendo o resumo das métricas do servidor e uma tabela detalhada com as consultas causadoras do pico (incluindo tempo total/médio de CPU, quantidade de execuções e o código T-SQL correspondente).
Vantagens de utilizar Stored Procedure
Em vez de manter um script extenso diretamente no Step do Agent Job, encapsular a lógica em uma Stored Procedure traz diversos benefícios:
Parâmetros flexíveis: Permite alterar o limite de CPU (@CPU_Threshold), o perfil de e-mail (@ProfileName), os destinatários (@Recipients) ou a quantidade de queries no relatório (@TopQueries) sem precisar modificar o Job;
Fácil manutenção e testes: Facilita testes rápidos no SSMS (por exemplo, simulando o alerta ao alterar temporariamente o limite para 5%) e permite integrar o código ao controle de versão (Git/DevOps);
Estrutura do Script T-SQL (Stored Procedure)
Abaixo está o código para criação da procedure dbo.stp_alerta_cpu_critico na base master (ou no seu banco utilitário de DBA):
SQL
USE [master];
GO
IF OBJECT_ID('dbo.stp_alerta_cpu_critico', 'P') IS NULL
EXEC('CREATE PROCEDURE dbo.stp_alerta_cpu_critico AS SELECT 1;');
GO
ALTER PROCEDURE dbo.stp_alerta_cpu_critico
@CPU_Threshold INT = 90,
@ProfileName VARCHAR(50) = 'DBA',
@Recipients VARCHAR(200) = 'monitoramento@clouddbm.com',
@TopQueries INT = 4
AS
BEGIN
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET NOCOUNT ON;
DECLARE @Subject NVARCHAR(255);
DECLARE @BodyHTML NVARCHAR(MAX);
DECLARE @QueryTableHTML NVARCHAR(MAX) = '';
DECLARE @ts_now BIGINT;
DECLARE @SQLProcessUtilization INT;
DECLARE @SystemIdle INT;
DECLARE @OtherProcessUtilization INT;
DECLARE @TotalUtilization INT;
DECLARE @EventTime DATETIME;
-- Coleta do uso de CPU via ring buffers
SELECT @ts_now = cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info WITH (NOLOCK);
;WITH CPU_Data AS (
SELECT
record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
[timestamp]
FROM (
SELECT [timestamp], CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%'
) AS x
)
SELECT TOP (1)
@SQLProcessUtilization = SQLProcessUtilization,
@SystemIdle = SystemIdle,
@OtherProcessUtilization = (100 - SystemIdle - SQLProcessUtilization),
@TotalUtilization = (100 - SystemIdle),
@EventTime = DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE())
FROM CPU_Data
ORDER BY record_id DESC;
-- Se o limite for ultrapassado, gera a tabela de queries e dispara o e-mail
IF @TotalUtilization >= @CPU_Threshold
BEGIN
SELECT @QueryTableHTML = CAST(
(
SELECT
td = CAST(qs.total_worker_time / 1000.0 AS DECIMAL(18,2)), '',
td = qs.execution_count, '',
td = CAST(qs.total_worker_time / qs.execution_count / 1000.0 AS DECIMAL(18,2)), '',
td = CAST(qs.total_elapsed_time / qs.execution_count / 1000.0 AS DECIMAL(18,2)), '',
td = ISNULL(
SUBSTRING(t.text, (qs.statement_start_offset / 2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2) + 1),
t.text), ''
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY qs.total_worker_time DESC
OFFSET 0 ROWS FETCH NEXT @TopQueries ROWS ONLY
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)
);
SET @Subject = '[ALERTA] CPU Crítico: ' + CAST(@TotalUtilization AS VARCHAR(10)) + '% na instância ' + @@SERVERNAME;
SET @BodyHTML = '<html><head><style>' +
'body { font-family: Arial, sans-serif; font-size: 12px; color: #333; }' +
'h3 { color: #d9534f; margin-bottom: 5px; }' +
'h4 { color: #333; margin-top: 20px; margin-bottom: 8px; border-bottom: 1px solid #ddd; padding-bottom: 4px; }' +
'table { border-collapse: collapse; width: 100%; margin-bottom: 15px; }' +
'th, td { border: 1px solid #ddd; padding: 8px; text-align: left; vertical-align: top; }' +
'th { background-color: #f2f2f2; font-weight: bold; }' +
'.highlight { background-color: #ffcccc; font-weight: bold; }' +
'</style></head><body>' +
'<h3> Alerta de Utilização de CPU</h3>' +
'<p>O uso de CPU ultrapassou o limite de <b>' + CAST(@CPU_Threshold AS VARCHAR(10)) + '%</b>.</p>' +
'<h4>Resumo do Servidor</h4>' +
'<table style="width: 60%;">' +
'<tr><th>Métrica</th><th>Valor</th></tr>' +
'<tr class="highlight"><td>Uso Total CPU</td><td>' + CAST(@TotalUtilization AS VARCHAR(10)) + '%</td></tr>' +
'<tr><td>Uso pelo SQL Server</td><td>' + CAST(@SQLProcessUtilization AS VARCHAR(10)) + '%</td></tr>' +
'<tr><td>Outros Processos (OS/Antivírus)</td><td>' + CAST(@OtherProcessUtilization AS VARCHAR(10)) + '%</td></tr>' +
'<tr><td>Data/Hora do Evento</td><td>' + CONVERT(VARCHAR, @EventTime, 120) + '</td></tr>' +
'</table>' +
'<h4>Top ' + CAST(@TopQueries AS VARCHAR(5)) + ' Queries com Maior Consumo de CPU</h4>' +
'<table>' +
'<tr>' +
'<th style="width: 12%;">CPU Total (ms)</th>' +
'<th style="width: 10%;">Execuções</th>' +
'<th style="width: 12%;">CPU Média (ms)</th>' +
'<th style="width: 12%;">Duração Média (ms)</th>' +
'<th>Query Text</th>' +
'</tr>' +
ISNULL(@QueryTableHTML, '<tr><td colspan="5">Nenhuma query encontrada na dm_exec_query_stats.</td></tr>') +
'</table>' +
'<p><i>Verifique as sessões ativas e o plano de execução das queries acima.</i></p>' +
'</body></html>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients = @Recipients,
@subject = @Subject,
@body = @BodyHTML,
@body_format = 'HTML';
END
END;
GO
Como agendar no SQL Server Agent Job?
Para colocar essa rotina de monitoramento em produção, siga os passos para cadastrá-la no SQL Server Agent.
Configuração do Database Mail
Certifique-se de que a funcionalidade Database Mail está habilitada e com um Profile ativo (no script, o perfil padrão está preenchido como 'DBA', altere para o nome configurado na sua instância).
Criação do Job
1) Abra o SQL Server Management Studio (SSMS);
2) Vá em SQL Server Agent > Jobs > New Job;
3) Nomeie o job (ex: DBA - Alerta uso de CPU);
4) Na aba Steps, adicione um novo Step do tipo Transact-SQL (TSQL) apontando para o banco master;
5) Insira o comando de chamada executando a procedure com os parâmetros desejados:
SQL
EXEC master.dbo.stp_alerta_cpu_critico
@CPU_Threshold = 90, --percentual limite para alerta
@ProfileName = 'DBA', --Nome do profile configurado
@Recipients = 'dba@gmail.com.br', --Email ao qual irá receber alerta
@TopQueries = 4;
Frequência do Agendamento (Schedules)
Configure o Job para rodar periodicamente. A recomendação padrão é agendar a execução a cada 5 ou 15 minutos, garantindo que picos curtos e travamentos prolongados sejam detectados rapidamente sem onerar o servidor.
Assista ao vídeo demonstrativo e faça o download gratuito do script direto no site da CloudDB!
Comentários