cpuUsageAlert

/***** threshold is currently set to 20% checks recent CPU activity in the last minute *****/



CREATE PROC cpuUsageAlert



AS
 SET NOCOUNT ON



/***** CPU Utilization history *****/

-- Get CPU Utilization History (SQL Server 2008 and above)
 DECLARE @ts BIGINT



--DROP Table #tempCPURecords
 SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks)

FROM sys.dm_os_sys_info);

SELECT TOP(1)SQLProcessUtilization AS [SQLServer_Process_CPU_Utilization],

SystemIdle AS [System_Idle_Process],

100 - SystemIdle - SQLProcessUtilization AS [Other_Process_CPU_Utilization],

DATEADD(ms,-1 *(@ts - [timestamp]),GETDATE())AS [Event_Time]

into #tempCPURecords

FROM (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

WHERE ring_buffer_type =N'RING_BUFFER_SCHEDULER_MONITOR'

AND record LIKE'%%')AS x

)AS y

ORDER BY record_id DESC;

select * from #tempCPURecords

if exists(select 1 from #tempCPURecords

where SQLServer_Process_CPU_Utilization > 20

and Event_Time>dateadd(mm,-1,getdate()))



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='shane.ryan@email.com,

@profile_name = 'ServiceDesk',

@subject = 'ServerName:Last 5 Minutes Avg CPU Utilization Over 80%',

@body = @tableHTML,

@body_format = 'HTML';



/** testing code

-- drop table temp_table

create table temp_table

(

cola varchar(max)

)

INSERT INTO [dba].[dbo].[temp_table] (cola)

select @tableHTML

select * from temp_table

**/
 END
 -- Drop the Temporary Table
 DROP Table #tempCPURecords