May 25, 2012 at 7:03 am
Hi
One of our prod server is showing 100% CPU usage for continous long times, at times 5 minute.
I want to set up an alert if the usage stays 100% for 1 min.
If I got to Alerts-->Create new alert, I can see the option "Sql Server Performance Condition Alert"-->WorkLoadGroup Stats-->CPU Usage %
But this alert never gets fired even when I set the condition for say 10% CPU usage.The alert history says" Never Occured".
I understand that I can create a Proc and schedule it to fire an alert mail. But I think it's a pretty common requirement which can be done from Alerts GUI. Can somebody suggest what I may be missing?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 25, 2012 at 7:15 am
the below will give you a good starting point, you will need to modify it to change the order of the eventtime and get it to do some aggregates to check if its been at 100 for longer than X amount of minutes then send the email but a good starting point never the less
WITH CTE1 AS(
SELECT
EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
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,
EventTime
FROM (
SELECT
DATEADD (ms, r.[timestamp] - s.ms_ticks, GETDATE()) AS EventTime,
CAST(r.record AS XML) record
FROM
sys.dm_os_ring_buffers r
CROSS JOIN
sys.dm_os_sys_info s
WHERE
ring_buffer_type='RING_BUFFER_SCHEDULER_MONITOR'
AND
record LIKE '%<SystemHealth>%'
) AS x
) AS y
)select * from CTE1
May 25, 2012 at 7:27 am
Honestly I already had tried this query but the DMV doesn't seem to give satisfactory result .
e.g. I ran a query on test server which made the CPU 100% for continous 5 minutes but this query still retuned average CPU usage of around 35% for that duration. I doubt results of this query or may be I am not using it properly.
Have you ever used it for alerts?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 25, 2012 at 7:53 am
S_Kumar_S (5/25/2012)
Honestly I already had tried this query but the DMV doesn't seem to give satisfactory result .e.g. I ran a query on test server which made the CPU 100% for continous 5 minutes but this query still retuned average CPU usage of around 35% for that duration. I doubt results of this query or may be I am not using it properly.
Have you ever used it for alerts?
What do you get as the MAX utilization for that duration?
May 25, 2012 at 8:45 am
I actually used this proc, and it never gave me a CPU utilization of 100% even though it was 100% for 5 minutes.
ALTER proc [dbo].[MonitorCPU]
AS
BEGIN
SET NOCOUNT ON
DECLARE @TimeNow bigint
SELECT @TimeNow = cpu_ticks / (cpu_ticks/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,-1,getdate()))>=30
begin
print 'CPU Alert Condition True, 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='abc@abc.com', @profile_name = 'DatabaseEmail',
@subject = 'ServerName:MyServer Last 1 Minutes Avg CPU Utilization Over 2%',
@body = @tableHTML
, @body_format = 'HTML';
END
-- Drop the Temporary Table
DROP Table #tempCPURecords
END
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply