{"id":3997,"date":"2022-12-14T07:00:00","date_gmt":"2022-12-14T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3997"},"modified":"2023-11-26T18:04:28","modified_gmt":"2023-11-26T15:04:28","slug":"pos-mporoyme-na-lamvanoyme-email-opote-echo","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-lamvanoyme-email-opote-echo\/","title":{"rendered":"How can we get emails whenever we have high CPU Usage in SQL Server"},"content":{"rendered":"

In this article we will see a script that creates one SQL Server Agent Job<\/strong> 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<\/a> of system_health<\/a> that exists in every SQL Server installation. <\/p>\n\n\n\n

The footsteps<\/h2>\n\n\n\n

First we should have enabled Database Mail in SQL Server. We have seen how this is done in this <\/a>article<\/a>.<\/p>\n\n\n\n

The Transact SQL Script<\/h4>\n\n\n\n

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 <\/em><\/strong>Extended Event. If it exceeds the threshold we set in the code, it will send an email with the profile we set.<\/p>\n\n\n\n

The only information we need to change per case is (line 59):<\/p>\n\n\n\n

IF  cast(@usage as int) > 95    <----- The limit \/ threshold of CPU Usage that we want to send us emails above<\/strong>\nbegin\n\texec msdb.dbo.sp_send_dbmail\n\t@profile_name = ''gmail'',       <----- The profile name we made to send email in the first step<\/strong>\n\t@recipients = ''info@dataplatform.gr'',    <----- And the recipients of the email<\/strong>\n\t@subject = @sub,\n\t@body = @bod;\nend;<\/code><\/pre>\n\n\n\n
After making the above modifications to the following script, all that remains is to run in a new query window on the instance:<\/p>\n\n\n\n
USE [msdb]\nGO\n\n\/****** Object:  Job [Alert for high CPU]    Script Date: 29\/10\/2021 05:00:31 \u00ec\u00ec ******\/\nBEGIN TRANSACTION\nDECLARE @ReturnCode INT\nSELECT @ReturnCode = 0\n\/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 29\/10\/2021 05:00:31 \u00ec\u00ec ******\/\nIF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)\nBEGIN\nEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'\nIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback\n\nEND\n\nDECLARE @jobId BINARY(16)\nEXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Alert for high CPU', \n\t\t@enabled=1, \n\t\t@notify_level_eventlog=0, \n\t\t@notify_level_email=0, \n\t\t@notify_level_netsend=0, \n\t\t@notify_level_page=0, \n\t\t@delete_level=0, \n\t\t@description=N'No description available.', \n\t\t@category_name=N'[Uncategorized (Local)]', \n\t\t@owner_login_name=N'sa', @job_id = @jobId OUTPUT\nIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback\n\/****** Object:  Step [step1]    Script Date: 29\/10\/2021 05:00:31 \u00ec\u00ec ******\/\nEXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step1', \n\t\t@step_id=1, \n\t\t@cmdexec_success_code=0, \n\t\t@on_success_action=1, \n\t\t@on_success_step_id=0, \n\t\t@on_fail_action=2, \n\t\t@on_fail_step_id=0, \n\t\t@retry_attempts=0, \n\t\t@retry_interval=0, \n\t\t@os_run_priority=0, @subsystem=N'TSQL', \n\t\t@command=N'set ansi_warnings on\ngo\nset ansi_padding on\ngo\nset quoted_identifier on\ngo\ndeclare \n@usage  int,\n@sub nvarchar(max),\n@bod nvarchar(max)\nset @usage =\n(\t\tSELECT 100-avg(record.value(''(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/SystemIdle)[1]'', ''INT'')) as ''Cpu Usage %''\n\t\t\t\t\tFROM ( \n                        SELECT top 5 CONVERT(xml, record) AS [record],dateadd (ms,timestamp - (select ms_ticks from sys.dm_os_sys_info),getdate()) as ''Time''\n\t\t\t\t\t\tFROM sys.dm_os_ring_buffers\n                        WHERE ring_buffer_type = N''RING_BUFFER_SCHEDULER_MONITOR''\n                        AND record LIKE ''%<SystemHealth>%''\n\t\t\t\t\t\torder by time desc) AS x) ;\nset @sub = (select ''SQL Server Alert System: High CPU Usage at '' + @@servername);\nset @bod = (select ''DESCRIPTION: At '' + cast(getdate() as nvarchar) +'' on SQL Server '' + @@servername +'', CPU usage was '' +cast(@usage as nvarchar)+ ''%!!!!'');\nIF  cast(@usage as int) > 95\nbegin\n\texec msdb.dbo.sp_send_dbmail\n\t@profile_name = ''gmail'',\n\t@recipients = ''info@dataplatform.gr'',\n\t@subject = @sub,\n\t@body = @bod;\nend;\n', \n\t\t@database_name=N'master', \n\t\t@flags=0\nIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback\nEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1\nIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback\nEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily', \n\t\t@enabled=1, \n\t\t@freq_type=4, \n\t\t@freq_interval=1, \n\t\t@freq_subday_type=4, \n\t\t@freq_subday_interval=5, \n\t\t@freq_relative_interval=0, \n\t\t@freq_recurrence_factor=0, \n\t\t@active_start_date=20211029, \n\t\t@active_end_date=99991231, \n\t\t@active_start_time=0, \n\t\t@active_end_time=235959, \n\t\t@schedule_uid=N'df24cb48-55e9-436a-a0de-56d4dd5139a3'\nIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback\nEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'\nIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback\nCOMMIT TRANSACTION\nGOTO EndSave\nQuitWithRollback:\n    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION\nEndSave:\nGO\n\n\n<\/pre>\n\n\n\n
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:<\/p>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
After a while we will see that it sent by 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):<\/p>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n