How to Monitor SQL Server CPU Usage and get auto alerts

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 28-12-2010

22

There are some condition & times when SQL Server CPU utilization exceeds threshold value (ex 80% or more) and stay constant even till 100% for a undefined duration. A DBA do not get notified automatically about performance degrade until its reported by application team or service desk . This usually happens when all in sudden unpredicted application workload goes to SQL Server for processing; but at good number of times, its because of some T-SQL Queries are taking longer than expected or because of bad execution plan or blocking. There may be more reason to add, but ultimately high CPU utilization on production systems is going to hurt performance.

image

Given below is a modified script that you can convert into stored procedure and call from SQL Server agent job, scheduled for every 5 minutes or more to check CPU utilization on a critical database server. This script uses sys.dm_os_ring_buffers to identify CPU utilization from last 5 minutes and if average CPU utilization goes beyond 80% then send an alert email along with top 50 CPU consuming SQL sessions and details.

 

SET NOCOUNT ON
DECLARE @TimeNow bigint 
SELECT @TimeNow = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info 
-- Collect Data from DMV
Select record_id, dateadd(ms, -1 * (@TimeNow - [timestamp]), 
GetDate())EventTime, SQLSvcUtilization, SystemIdle, 
(100 - SystemIdle - SQLSvcUtilization) AS OtherOSProcessUtilization into #tempCPURecords
from ( select record.value('(./Record/@id)[1]', 'int')record_id, 
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')SystemIdle, 
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')SQLSvcUtilization, 
timestamp 
from ( select timestamp, convert(xml, record)record 
from sys.dm_os_ring_buffers 
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
and record like '%<SystemHealth>%')x )y  order by record_id desc
-- To send detailed sql server session reports consuming high cpu
-- For a dedicated SQL Server you can monitor 'SQLProcessUtilization' 
-- if (select avg(SQLSvcUtilization) from #temp where EventTime>dateadd(mm,-5,getdate()))>=80
-- For a Shared SQL Server you can monitor 'SQLProcessUtilization'+'OtherOSProcessUtilization'
if (select avg(SQLSvcUtilization+OtherOSProcessUtilization) 
from #tempCPURecords where EventTime>dateadd(mm,-5,getdate()))>=80
begin
print 'CPU Alert Condition Ture, Sending Email..'
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
    N'<H1>High CPU Utilization Reported</H1>' +
    N'<H2>SQL Server Session Details</H2>' +
    N'<table border="1">' +
    N'<tr><th>SPID</th><th>Status</th><th>Login</th><th>Host</th><th>BlkBy</th>'+
N'<th>DatabaseID</th><th>CommandType</th><th>SQLStatement</th><th>ElapsedMS</th>'+
N'<th>CPUTime</th><th>IOReads</th><th>IOWrites</th><th>LastWaitType</th>'+
N'<th>StartTime</th><th>Protocol</th><th>ConnectionWrites</th>'+
N'<th>ConnectionReads</th><th>ClientAddress</th><th>Authentication</th></tr>'+
CAST ( ( SELECT  TOP 50 -- or all by using *
td= er.session_id,'',
td= ses.status,'',
td= ses.login_name,'',  
td= ses.host_name,'',   
td= er.blocking_session_id,'',  
td= er.database_id,'',  
td= er.command,'',  
td= st.text,'',  
td= er.total_elapsed_time,'',  
td= er.cpu_time,'',  
td= er.reads,'',  
td= er.writes,'',  
td= er.last_wait_type,'',  
td= er.start_time,'',  
td= con.net_transport,'',  
td= con.num_writes,'',  
td= con.num_reads,'',  
td= con.client_net_address,'',  
td= con.auth_scheme,''  
FROM sys.dm_exec_requests er  
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st  
LEFT JOIN sys.dm_exec_sessions ses  
ON ses.session_id = er.session_id  LEFT JOIN sys.dm_exec_connections con  
ON con.session_id = ses.session_id  
WHERE er.session_id > 50  
ORDER BY er.cpu_time DESC ,er.blocking_session_id
FOR XML PATH('tr'), TYPE 
)AS NVARCHAR(MAX))+
N'</table>' 
-- Change SQL Server Email notification code here
EXEC msdb.dbo.sp_send_dbmail 
@recipients='dk@sqlknowledge',
@profile_name = 'SQLProfileName',    
@subject = 'ServerName:Last 5 Minutes Avg CPU Utilization Over 80%',
@body = @tableHTML,
@body_format = 'HTML';
END
-- Drop the Temporary Table
DROP Table #tempCPURecords

Comments (22)

we can use process explorer tools to find which thread cost most CPU.
then runing
select sql.text
from sys.dm_exec_request qt
cross apply sys.dm_exec_sql_text(qt.sql_handle) sql
where session_id=
(select stasks.session_id
from sys.dm_os_tasks as stasks
inner join sys.dm_os_thread as sthreads
on stasks.worker_address=sthreads.worker_address
where stasks.session_id is not null and sthreds.os_thread_id=?(find in process explorer)

Hey Bro,
Thanks!! for a wonderful script.

but i am getting an arithmetic overflow error while executing the script on one of my server.

Thanks for the script. It is very helpfull to know how the CPU is working using SQL.

hI,
I am getting this error:–
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.

Great job.
This script works fine with SQL 2008 r2 but it runs into issues with SQL 2005 and SQL 2008.
Even I’m getting this error:–
Arithmetic overflow error converting expression to data type int.

It would be of great help to everyone if someone could fix this error.

You can replace code line 3 to following to have it working with R2

SELECT @TimeNow = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info

Thanks Deepak!

It now works absolutely fine with SQL Server 2008 and 2008R2.

Deepak

This script generates arithmetic overflow errors when i run it on SQL 2005.Can you please suggest any changes to be made in this script to make it run on SQL 2005.

You have really done a great job,appreciate it.

Hi All,

i couldn’t find creating table “#tempCPURecords” in this script please let me know any one how to create a temp table for this script.

Regards,
Malli

@MalliReddy:

temp table is automatically created by this script. If you see line 5 to 7 has select into statement. I hope you copied entire code.

Select record_id, dateadd(ms, -1 * (@TimeNow – [timestamp]),
GetDate())EventTime, SQLSvcUtilization, SystemIdle,
(100 – SystemIdle – SQLSvcUtilization) AS OtherOSProcessUtilization into #tempCPURecords

@Deepak Kumar:

Thanks a million Deepak

It’s a really Great script.

I get this error when trying to create stored procedure
Msg 102, Level 15, State 1, Procedure sp_lcrane_IT_SqlServerProcessor, Line 60
Incorrect syntax near ‘.’.

Here is line 60
OUTER APPLY master.sys.dm_exec_sql_text(er.sql_handle) st

Hi

Why this script is not sending mail alert (if current cpu utilization is 85% also) i have given

if (select avg(SQLSvcUtilization+OtherOSProcessUtilization)

from #tempCPURecords where EventTime>dateadd(mm,-5,getdate()))>=60

kindly help me any one

Thanks ..

Hi All,

Any please help me know the ” CPUTime” is Micro seconds or Milliseconds or seconds ?

Regards,
Malli

Hi All,
i would like to have an automated e-mail from the SQL server 2005 when ever the CPU utlisation is high..

and i want the email to contain the query which is causing the high cpu utilization.

can anyone help me on this please

thanks
Gowtham

[...] to Monitor SQL Server CPU Usage and get auto alerts http://sqlknowledge.com/2010/12/how-to-monitor-sql-server-cpu-usage-and-get-auto-alerts/ Be Sociable, Share! Tweet Posted in CPU, High CPU Utilization « How to cycle [...]

Great script. Saved me a lot of time.

Regards,
Paul

is this code will also work on MSSQL server 2012

Its such as you read my mind! You appear to understand a lot
approximately this, such as you wrote the e book in it or something.

I think that you could do with a few p.c. to drive the message house a little bit, but instead of that, that is
great blog. An excellent read. I’ll certainly be back.

try to use montarget system you get cpu and more .

i have created stored as you said. then how sheduel
this email alert

can you please tell me

thank you so much

Write a comment

*

© 2010 SQLKnowledge.com Increase your website traffic with Attracta.com