CPU history (256 minutes)

Show the CPU load in last 256 minutes

/* 
Description 	: Show the CPU load in last 256 minutes 
Note		: For SQL 2012 replace "cpu_ticks / 1000" by "cpu_ticks_in_ms / 1000"
Author			: Theo Ekelmans 
Version/Date	: 1.0 2013-03-23
*/


SELECT Dateadd(ms, -1 * ( cpu_ticks / ( cpu_ticks / ms_ticks ) - [timestamp] ), Getdate()) AS DT
       ,CAST(( 1.0 * ms_ticks / ( 24 * 60 * 60 * 1000 ) ) AS DECIMAL(10, 2))               AS system_uptime_days
       ,cpu_count
       ,cpu_ticks / 1000                                                                   AS cpu_MHz
       ,hyperthread_ratio
       ,SQLProcessUtilization
       ,100 - SystemIdle - SQLProcessUtilization                                           AS OtherProcessUtilization
       ,SystemIdle
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
               --top 1 
               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>%'
               --order by timestamp desc 
               ) AS x) AS y,
       sys.dm_os_sys_info
ORDER  BY timestamp DESC 


Leave a Reply

Your email address will not be published. Required fields are marked *