Uncategorized

Get CPU usage from Ring Buffer

DECLARE @Now BIGINT
SELECT  @Now = ms_ticks FROM sys.dm_os_sys_info 

SELECT  DATEADD(ms, -1 * ( @Now - [timestamp] ), GETDATE()) AS EventTime,
        SQLService, 
        Idle,
        100 - Idle - SQLService AS NonSqlProcesses
FROM    ( SELECT    record.value('(./Record/@id)[1]', 'INT') AS record_id,
                    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'INT') AS Idle,
                    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'INT') AS SQLService, 
                    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
ORDER BY 1 DESC

Listing Ring Buffer record types:

SELECT RecordType, COUNT(*)
FROM 
    (
        SELECT CAST(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS RecordType
        FROM sys.dm_os_ring_buffers
    ) rb
GROUP BY RecordType