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
