Script to get CPU usage

  • Hi All

    We use the below script to calculate the CPU usage across our servers

    DECLARE @ts_now BIGINT

    SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, ms_ticks) FROM sys.dm_os_sys_info

    SELECT record_id,

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

    SQLProcessUtilization,

    SystemIdle,

    100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization

    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 '% %') AS x

    ) AS y

    But from 8pm last night it starting failing with cannot convert expression to data type int. I know where this is coming from, its the DATEADD at the top due to @ts_now - timestamp resulting in a BIGINT which you cannot pass into DATEADD as it only wants an INT.

    Just wondering if anyone else has seen this problem and if they have another script or a workaround.

  • Nice script, although I prefer Perfmon logging CPU usage over periods of time to get averages, highs, lows.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • the sys.dm_os_ring_buffers table stores the past ~4 hours worth of CPU data so its just a case of running this once every 2 hours and loading it into a table where the date conversion isnt in the table already then it aggreagates in the warehouse for each server overnight.

    just a pain that its stopped working

  • from this url sys.dm_os_ring_buffers is only there for backwards compatability and is not guaranteed, so what is the new DMV replacement for this DMV?

  • I tested the script on my 2008 R2, and 2012 instances, only changed the @ts_now to float, and it ran on both versions without issues.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • What I can think off:

    try to dissect @ts_now to (hours), minutes, seconds and milliseconds

    Then dateadd one by one

  • Just an idea, if you can live with the loss of precision, you could try dividing both @ts_now and [timestamp] by 1000 inside the [number] argument of the DATEADD function, and then use seconds instead of milliseconds for the [datepart]:-

    DATEADD(second, -1 * ((@ts_now / 1000) - ([timestamp] / 1000)), GETDATE()) AS EventTime

  • i think something a bit more sinister is at play here as I did just that Rob and got a Eventtime of 2012-04-24 16:33:383.443 so I am thinking something is squiffy on ticks or timestamp part as a getdate returns the correct value for today

  • changing the code to this works without dividing cpu_ticks with ms_ticks

    SELECT

    record_time,

    SQLProcessUtilization,

    SystemIdle,

    100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization

    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,

    record_time

    FROM (

    select

    dateadd (ms, r.[timestamp] - sys.ms_ticks, getdate()) as record_time,

    cast(r.record as xml) record

    from sys.dm_os_ring_buffers r

    cross join sys.dm_os_sys_info sys

    where

    ring_buffer_type='RING_BUFFER_SCHEDULER_MONITOR'

    AND

    record LIKE '%<SystemHealth>%'

    ) AS x

    ) AS y

Viewing 9 posts - 1 through 8 (of 8 total)

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