Instance CPU Utilization SQL2005

Is there any reason why the 'Instance CPU Utilization' metric is unavailable for SQL2005 instances under the Resources->CPU tab? It appears to be configured for SQL2008 and later instances only and we've had to create a custom metric to get this for SQL2005.

Find more posts tagged with

instance

CPU

dpa

Accepted answers

brianflynn

The reason it requires 2008 is this metric is being gathered from the ring buffer which is part of extended events and that didn't come out until SQL 2008.

All comments

brianflynn

The reason it requires 2008 is this metric is being gathered from the ring buffer which is part of extended events and that didn't come out until SQL 2008.

spaceman

Thanks Brian, makes sense. If anyone reading this anyone wants it as a custom metric for SQL2005 we use the same query as the performance dashboard;

DECLARE
@ts_now BigInt

SELECT @ts_now = cpu_ticks / cpu_ticks / ms_ticks
FROM sys.dm_os_sys_info WITH (nolock)

SELECT TOP 1 SQLProcessUtilization
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

brianflynn

Thanks for sharing! I will post this back to our content exchange too. :-)

https://thwack.solarwinds.com/community/database-management/database-performance-analyzer/content?filterID=contentstatus[published]~objecttype~objecttype[document]