Find CPU from within SSMS

This is a great DMV if you don’t want to leave SSMS to see the CPU usage or if you don’t have access. What’s good about this method is it also shows you the history of the cpu usage.

[cc lang=”sql”]

declare @ts_now bigint

select @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info;

select record_id,

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

SQLProcessUtilization,

SystemIdle,

100 – SystemIdle – SQLProcessUtilization as OtherProcessUtilization

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

order by record_id desc

[/cc]