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.
1 | SELECT |
2 | CONVERT(VARCHAR(20), dateadd (ms, [timestamp] - sys.ms_ticks, getdate()), 100) as Time_Stamp, |
3 | SQLProcessUtilization, |
4 | 100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization, |
5 | SystemIdle |
6 | FROM ( |
7 | SELECT |
8 | record.value('(./Record/@id)[1]', 'int') AS record_id, |
9 | record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle, |
10 | record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization, |
11 | TIMESTAMP |
12 | FROM ( |
13 | SELECT TIMESTAMP, CONVERT(XML, record) AS record |
14 | FROM sys.dm_os_ring_buffers |
15 | WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' |
16 | AND record LIKE '% %') AS x |
17 | ) AS y |
18 | cross join sys.dm_os_sys_info sys |
19 | ORDER BY record_id DESC |
