September 17, 2012 at 12:51 pm
I am looking for a scripted way (i.e., a TSQL query) to track CPU utilization throughout the day on a 3-node SQL Server 2008 R2 cluster that hosts 8 instances. Google shows lots of examples of the following query. However, I am at a loss for why it returns 256 rows for a 16 core server.
Is there a better way to track CPU utilization via a script? Whatever I end up using will be scheduled on all three cluster nodes.
SELECT
record_time,
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,
record_time
FROM (
select
dateadd (ms, r.[timestamp] - sys.ms_ticks, getdate()) as record_time,
cast(r.record as xml) record
from sys.dm_os_ring_buffers r
cross join sys.dm_os_sys_info sys
where
ring_buffer_type='RING_BUFFER_SCHEDULER_MONITOR'
AND
record LIKE '%<SystemHealth>%'
) AS x
) AS y
September 17, 2012 at 1:01 pm
Does this view just keep a sliding window of 256 entries? Meaning that the "top 1" row could be used to track CPU utilization?
Also, if the top 1 row is the current CPU utilization, is the reported utilization for the whole server, or just the current instance?
September 19, 2012 at 10:54 am
This link describes sys.dm_os_ring_buffers:
http://gallery.technet.microsoft.com/scriptcenter/Utilization-History-afe03282
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply