March 30, 2012 at 2:12 am
Hi All
We use the below script to calculate the CPU usage across our servers
DECLARE @ts_now BIGINT
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, 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('(./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
But from 8pm last night it starting failing with cannot convert expression to data type int. I know where this is coming from, its the DATEADD at the top due to @ts_now - timestamp resulting in a BIGINT which you cannot pass into DATEADD as it only wants an INT.
Just wondering if anyone else has seen this problem and if they have another script or a workaround.
March 30, 2012 at 2:18 am
Nice script, although I prefer Perfmon logging CPU usage over periods of time to get averages, highs, lows.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 30, 2012 at 2:22 am
the sys.dm_os_ring_buffers table stores the past ~4 hours worth of CPU data so its just a case of running this once every 2 hours and loading it into a table where the date conversion isnt in the table already then it aggreagates in the warehouse for each server overnight.
just a pain that its stopped working
March 30, 2012 at 2:38 am
I tested the script on my 2008 R2, and 2012 instances, only changed the @ts_now to float, and it ran on both versions without issues.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 30, 2012 at 2:46 am
What I can think off:
try to dissect @ts_now to (hours), minutes, seconds and milliseconds
Then dateadd one by one
March 30, 2012 at 2:50 am
Just an idea, if you can live with the loss of precision, you could try dividing both @ts_now and [timestamp] by 1000 inside the [number] argument of the DATEADD function, and then use seconds instead of milliseconds for the [datepart]:-
DATEADD(second, -1 * ((@ts_now / 1000) - ([timestamp] / 1000)), GETDATE()) AS EventTime
March 30, 2012 at 2:56 am
i think something a bit more sinister is at play here as I did just that Rob and got a Eventtime of 2012-04-24 16:33:383.443 so I am thinking something is squiffy on ticks or timestamp part as a getdate returns the correct value for today
March 30, 2012 at 3:49 am
changing the code to this works without dividing cpu_ticks with ms_ticks
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply