SQL   27

Alert if CPU utilization is more than 80%

Guest on 24th August 2022 04:35:46 AM

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

Raw Paste


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