Post reply

Query to use for CPU Utilization

  • July 10, 2010 at 7:37 pm

    #223753

    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!!

  • July 10, 2010 at 8:18 pm

    #1191186

    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;

  • July 11, 2010 at 8:58 am

    #1191217

    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.

  • July 11, 2010 at 10:39 am

    #1191246

    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