For a quick and dirty way to get the CPU utilization from SQL Server you can use the sys.dm_os_ring_buffers DMV.

The DMV sys.dm_os_ring_buffers maintains approximately 1000 records, before wrapping around and replacing the oldest entries first. It exposes four columns but we’re primarily only interested in one of them, record, which contains XML data.

Here is a quick query to get CPU utilization from the SQL Server.

-------------------------------------------

select top 1
(convert(xml, record)).value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as [cpuIdle],
(convert(xml, record)).value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as [cpuSQLProcessUtilization],
100 - (convert(xml, record)).value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') - (convert(xml, record)).value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as [CPUOtherProcessUtilization]

from sys.dm_os_ring_buffers a
where a.ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and a.record like '%<SystemHealth>%'
order by (convert(xml, record)).value('(./Record/@id)[1]', 'int') desc;

--------------------------------------------

Executing the above query will give you a result similar to the following:

One caveat about using this method though is that the ring buffer reports CPU usage once per minute.  This means that this is not necessarily “real time”, but will always be within 1 minute or less of the moment when the query was executed.

If you wanted to get the CPU usage for the last ten minutes you could take the above query and change the ‘top’ clause from a 1 to a 10.  Like so:

-------------------------------------------

select top 10
(convert(xml, record)).value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as [cpuIdle],
(convert(xml, record)).value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as [cpuSQLProcessUtilization],
100 - (convert(xml, record)).value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') - (convert(xml, record)).value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as [CPUOtherProcessUtilization]

from sys.dm_os_ring_buffers a
where a.ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and a.record like '%<SystemHealth>%'
order by (convert(xml, record)).value('(./Record/@id)[1]', 'int') desc;

--------------------------------------------

As you can see from the screen shot below we now receive the CPU usage statistics for the last 10 minutes.