Below you will find the code to get the CPU Utilization for the past 7 days. Simply select the text and copy to the Clipboard.
— Code is From Glenn Berry – https://social.msdn.microsoft.com/Forums/sqlserver/en-US/38f34421-60b8-4bf4-b779-56e2f1d70cc0/how-to-check-cpu-usage-by-sql?forum=sqldatabaseengine
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(1080) SQLProcessUtilization AS ‘SQL Server Process CPU Utilization’,
SystemIdle AS ‘System Idle Process’,
100 – SystemIdle – SQLProcessUtilization AS ‘Other Process CPU Utilization’,
DATEADD(ms, -7 * (@ts_now – [timestamp]), GETDATE()) AS ‘Event Time’,
‘Glenn Berry – https://social.msdn.microsoft.com/Forums/sqlserver/en-US/38f34421-60b8-4bf4-b779-56e2f1d70cc0/how-to-check-cpu-usage-by-sql?forum=sqldatabaseengine’ AS ‘Source’
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 ‘%<SystemHealth>%’) AS x
) AS y
ORDER BY record_id DESC;