Monitor High CPU

  • nadersam

    SSCertifiable

    Points: 6541

    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

  • Divine Flame

    SSCoach

    Points: 15940

    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: 6541

    Thanks for your reply.

    It went above 70 %

  • Divine Flame

    SSCoach

    Points: 15940

    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: 6541

    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: 6541

    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

    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

    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

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

    Please suggest

    Mohammad

  • THANG HOANG

    SSChasing Mays

    Points: 632

    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: 1279

    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_time startTime

    ,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

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

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