TEXT   47

Alert if CPU utilization is more than 80%

Guest on 8th August 2021 10:52:04 AM

  1. Subject: Re: Alert if CPU utilization is more than 80%
  2. From: Pradeep Adiga
  3. Newsgroups: Msdn.en-US.sqldatabaseengine
  4.  
  5. You may use this query to get the CPU details. Insert the query output to a
  6. table and use sp_send_dbmail to send notifications.
  7.  
  8.     DECLARE @ts_now BIGINT
  9.     SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
  10.  
  11.     SELECT record_id,
  12.         DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
  13.         SQLProcessUtilization,
  14.         SystemIdle,
  15.         100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
  16.     FROM (
  17.         SELECT
  18.             record.value('(./Record/@id)[1]', 'int') AS record_id,
  19.             record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
  20.             record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
  21.             TIMESTAMP
  22.         FROM (
  23.             SELECT TIMESTAMP, CONVERT(XML, record) AS record
  24.             FROM sys.dm_os_ring_buffers
  25.             WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
  26.             AND record LIKE '% %') AS x
  27.         ) AS y
  28.     ORDER BY record_id DESC
  29.  
  30.   ------------------------------------------------------------------------
  31. Pradeep Adiga
  32. My blog: http://www.sqldbadiaries.com

Raw Paste


Login or Register to edit or fork this paste. It's free.