Here’s an easy way to grap minute by minute CPU Use in a query. This only goes back 4 hours, but can be useful for looking at load on your server.
CONVERT(VARCHAR(20), dateadd (ms, [timestamp] – sys.ms_ticks, getdate()), 100) as Time_Stamp,
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 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
cross join sys.dm_os_sys_info sys
ORDER BY record_id DESC
[/code]
