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
