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.

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('(.[email protected])[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

4 comments
sudhama 17 Nov 2014 at 4:21 pm

try by changing ‘VALUE’ to ‘value’

Tony Gallone 20 Feb 2017 at 4:11 pm

Change VALUE to value (lowercase), then it should run!

Sukhjinder 31 Oct 2014 at 5:30 am

Dieter, it gives error at record.VALUE function.
“VALUE” is not a valid function, property, or field.

Anitha 29 May 2014 at 10:37 am

Hi Derek,

Your scripts are simple and very handy.Keep Going.

Above script is not working in SQL 2008

getting below error

Msg 227, Level 15, State 1, Line 5
“VALUE” is not a valid function, property, or field.

I am a DBA and no idea on what should I do to get this off from my page. Please help

Featured Articles

 Site Author

  • Thanks for visiting!