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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy