Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Looking for a scripted way to track CPU utilization throughout the day Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 12:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 389, Visits: 790
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




Post #1360406
Posted Monday, September 17, 2012 1:01 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 389, Visits: 790
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?



Post #1360411
Posted Wednesday, September 19, 2012 10:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 389, Visits: 790
This link describes sys.dm_os_ring_buffers:

http://gallery.technet.microsoft.com/scriptcenter/Utilization-History-afe03282



Post #1361507
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse