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