Query to use for CPU Utilization

  • I need a query that checks my overall CPU utilization on server and kick of one SQL Agent Job if overall CPU utilization is over 95% , can any body help me to get the correct query/dmv please?

    Thanks!!

  • got from one of blog

    DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

    SELECT TOP(1)

    --SQLProcessUtilization AS [SQL Server Process CPU Utilization],

    -- SystemIdle AS [System Idle Process],

    -- 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],

    SQLProcessUtilization+(100 - SystemIdle - SQLProcessUtilization) AS [OverallCPU Utilizaton]

    -- ,

    -- DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]

    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], [timestamp]

    FROM (

    SELECT [timestamp], CONVERT(xml, record) AS [record]

    FROM sys.dm_os_ring_buffers

    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

    AND record LIKE '%<SystemHealth>%') AS x

    ) AS y

    ORDER BY record_id DESC;

  • You can also create a performance alert: http://www.databasejournal.com/features/mssql/article.php/1498781/SQL-Server-Agent---Creating-Performance-Condition-Alerts-on-Database-Server-Objects.htm

    If you take something from a blog, please give credit to the source. Someone else wrote this, so ensure they get the credit for doing the work.

    One issue with this, and you want to be careful, is that simple queries, log backups, etc. can kick the CPU to 100%. You typically don't want alerts when this happens. What you want is an alert if this is sustained for some time, like more than 5 minutes, not each incident.

  • pretty cool ,thanks i was missing alert part....

Viewing 4 posts - 1 through 3 (of 3 total)

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