Query to monitor the CPU on SQL Server 2008

Question

Hi , 

I use this query on SQL Server 2005 to monitor the CPU , on Nagios.  It does not works on SQL Server 2008 R2. 

Somebody have a similiar query to monitor SQL Server 2008 R2?

DECLARE @ts_now BIGINT
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info

SELECT top 1 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

Answer 1

Try this:

declare @ms bigint
declare @total_ms bigint

select	@ms = ms_ticks,
		@total_ms = process_kernel_time_ms + process_user_time_ms 
from sys.dm_os_sys_info 

waitfor delay '00:00:01'

select	ms_ticks - @ms total_cpu_time,
		process_kernel_time_ms + process_user_time_ms - @total_ms total_used_time,
		(process_kernel_time_ms + process_user_time_ms - @total_ms) / cpu_count avg_used_time_per_cpu,
		round(((process_kernel_time_ms + process_user_time_ms - @total_ms) / cast(cpu_count as decimal(10, 2))) / (ms_ticks - @ms) * 100, 2)  cpu_utilization
from sys.dm_os_sys_info 

You have to wait 1 sec for the result.

---

m@te

Answer 2

Timing in SQL 2008 is more accurate than SQL 2005.  So you can just us ms_ticks.  That is, in SQL 2008 and later, the first two lines in your code should be

DECLARE @ts_now BIGINT
SELECT @ts_now = ms_ticks FROM sys.dm_os_sys_info

Tom