How can we get emails whenever we have high CPU Usage in SQL Server
The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.
Certifications:
Certifications:
Latest posts by Stratos Matzouranis (see all)
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024
In this article we will see a script that creates one SQL Server Agent Job which will notify us by email whenever the CPU exceeds a threshold. It derives the information not from performance counters of the operating system but from the default Extended Event of system_health that exists in every SQL Server installation.
The footsteps
First we should have enabled Database Mail in SQL Server. We have seen how this is done in this article.
The Transact SQL Script
With the following script that I have prepared, a SQL Server Agent Job will be created which will run every 5 minutes and will check the CPU Usage through system_health Extended Event. If it exceeds the threshold we set in the code, it will send an email with the profile we set.
The only information we need to change per case is (line 59):
IF cast(@usage as int) > 95 <----- The limit / threshold of CPU Usage that we want to send us emails above
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = ''gmail'', <----- The profile name we made to send email in the first step
@recipients = ''info@dataplatform.gr'', <----- And the recipients of the email
@subject = @sub,
@body = @bod;
end;After making the above modifications to the following script, all that remains is to run in a new query window on the instance:
USE [msdb]
GO
/****** Object: Job [Alert for high CPU] Script Date: 29/10/2021 05:00:31 ìì ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 29/10/2021 05:00:31 ìì ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Alert for high CPU',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [step1] Script Date: 29/10/2021 05:00:31 ìì ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'set ansi_warnings on
go
set ansi_padding on
go
set quoted_identifier on
go
declare
@usage int,
@sub nvarchar(max),
@bod nvarchar(max)
set @usage =
( SELECT 100-avg(record.value(''(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]'', ''INT'')) as ''Cpu Usage %''
FROM (
SELECT top 5 CONVERT(xml, record) AS [record],dateadd (ms,timestamp - (select ms_ticks from sys.dm_os_sys_info),getdate()) as ''Time''
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N''RING_BUFFER_SCHEDULER_MONITOR''
AND record LIKE ''%<SystemHealth>%''
order by time desc) AS x) ;
set @sub = (select ''SQL Server Alert System: High CPU Usage at '' + @@servername);
set @bod = (select ''DESCRIPTION: At '' + cast(getdate() as nvarchar) +'' on SQL Server '' + @@servername +'', CPU usage was '' +cast(@usage as nvarchar)+ ''%!!!!'');
IF cast(@usage as int) > 95
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = ''gmail'',
@recipients = ''info@dataplatform.gr'',
@subject = @sub,
@body = @bod;
end;
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20211029,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'df24cb48-55e9-436a-a0de-56d4dd5139a3'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
When we run it we will see in the Object Explorer of SQL Server Management Studio that it has created a new Job in the SQL Server Agent:
After a while we will see that it sent itself without doing anything else an email about High CPU Usage (for example I changed the threshold from above 95% to 10% to be sent):
