Get CPU usage history

This script helps you to easily check the CPU usage of your SQL Server. You can get SQL CPU usage , Other processes CPU usage(Non-SQL) and the total usage for the previous 2 hours. Helpful to understand the usage patterns as well as to create a baseline.

-- CPU Usage History

SET NOCOUNT ON

declare @ts_now bigint

--SELECT @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info -- (Use this line if server is SQL 2005)

SELECT @ts_now = ms_ticks from sys.dm_os_sys_info -- (Use this line if server is SQL 2008 or above)

select /* top 1 */ record_id,

dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,

SQLProcessUtilization,

--SystemIdle,

100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization,

SQLProcessUtilization + (100 - SystemIdle - SQLProcessUtilization) as CPU_Usage

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

GO