Monitor High CPU

Question

Monitor High CPU

nadersam

SSCertifiable

Points: 6542
More actions
February 20, 2012 at 4:40 am

#149078

Hi All,
I created an alert under sql server agent alerts.
I selected type as sql server performance condition alert, Object:Sql server :Resource Pool Stats,Counter :Cpu usage %, Instance:default,Alert if:Counter rises above 40.
I made response trigger running a job and notify database operator using email.
I ran some sqls to trigger high CPU usage but the alert is never triggered and nothing happens!!.
Looking forward for your feedback.
Nader

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Divine Flame SSCoach Points: 15943 More actions · Answer 1

nadersam (2/20/2012)
Hi All,
I created an alert under sql server agent alerts.
I selected type as sql server performance condition alert, Object:Sql server :Resource Pool Stats,Counter :Cpu usage %, Instance:default,Alert if:Counter rises above 40.
I made response trigger running a job and notify database operator using email.
I ran some sqls to trigger high CPU usage but the alert is never triggered and nothing happens!!.
Looking forward for your feedback.
Nader

Did the CPU touched 40% baseline during the tests??

Sujeet Singh

nadersam SSCertifiable Points: 6542 More actions · Answer 2

nadersam

SSCertifiable

Points: 6542

February 20, 2012 at 6:08 am

#1449208

Thanks for your reply.

It went above 70 %

Divine Flame SSCoach Points: 15943 More actions · Answer 3

nadersam (2/20/2012)
Thanks for your reply.
It went above 70 %

I think the alert considers "100" as "1.0" as CPU is calculated by its base, so you need to set the baseline to "0.4" if you want to get the alert mail on CPU usage > 40%.

Sujeet Singh

nadersam SSCertifiable Points: 6542 More actions · Answer 4

i did that and still nothing happening and i checked history for alert, i even change value to 0.1 to be sure and restarted sql agent and run slow sqls again

Thanks

nadersam SSCertifiable Points: 6542 More actions · Answer 5

As a test i changed the alert to datafile size above a certain number and the alert was fired and i got a mail for it, still can't get the CPU usage % under SQLServer:Resource Pool Stats to work 🙁

Thanks

Petros Sigalos SSC Enthusiast Points: 103 More actions · Answer 6

Same for me. The alert does not fire.

SQLServer: Resource Pool Stats

Counter: CPU usage%

Instance: default

Alert when couter rise above: 0.01

I do have alerts for different stuff that works fine.

motricio SSC Rookie Points: 41 More actions · Answer 7

Use object: Workload Group Stats

And Value in number percent format: 0.00 = 0% between 1.00 = 100%

Options: delay between responses: 2 minutes

Worked on SQL Server 2012 SP1 Cluster Instance

Cheers,

mdsanaullah.khan SSC Journeyman Points: 81 More actions · Answer 8

it's working for fall below condition not working for when rises above condition.

Please suggest

Mohammad

THANG HOANG SSChasing Mays Points: 642 More actions · Answer 9

The expected counter value is in percentage format.

If you want the alert to fire when CPU usage exceeding 90%, set it to 0.90

mceventphoto Ten Centuries Points: 1299 More actions · Answer 10

I found this WMI code here http://craftydba.com/?p=1114 and it works for me. I modified it for every 60 seconds and when CPU is higher than 25% for testing.

EXEC msdb.dbo.sp_add_alert @name=N'Alert High CPU % (WMI)',

@message_id=0,

@severity=0,

@enabled=1,

@delay_between_responses=60,

@include_event_description_in=1,

@category_name=N'[Alert By Performance]',

@wmi_namespace=N'\\.\ROOT\CIMV2',

@wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 60

WHERE TargetInstance ISA ''Win32_Processor'' AND TargetInstance.LoadPercentage > 25',

@job_id=N'00000000-0000-0000-0000-000000000000';

GO

I have this alert call the code below from http://sqlserver-help.com/2014/07/17/script-find-currently-executing-queries-blocking-waits-statement-procedure-cpu/ with some modification in a stored procedure to save the information of any running query to a table for future analysis.

--http://sqlserver-help.com/2014/07/17/script-find-currently-executing-queries-blocking-waits-statement-procedure-cpu/

SELECT

@@servername ServerName

,s.session_id spid

,DB_NAME(database_id) dbName

,s.login_time

,r.start_timestartTime

,s.last_request_end_time

,r.STATUS

,r.command

,r.cpu_time

,r.logical_reads

,r.reads

,r.writes

,r.total_elapsed_time / (1000.0) ElapsedTimeSec

,s.host_name

,s.program_name

,s.nt_user_name

,s.login_name

,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (

(

CASE r.statement_end_offset

WHEN - 1

THEN Datalength(st.TEXT)

ELSE r.statement_end_offset

END - r.statement_start_offset

) / 2

) + 1) AS statement_text

,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +

Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text

,r.wait_type

,r.wait_time / (1000.0) WaitTimeSec

,wait_resource

,r.blocking_session_id blockedby

,s.host_process_id

,r.open_transaction_count

,getdate() DateCollected

FROM sys.dm_exec_sessions AS s

INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st

WHERE r.session_id != @@SPID

ORDER BY r.cpu_time DESC

,r.STATUS

,r.blocking_session_id

,s.session_id