January 23, 2011 at 11:01 am
I have a table defined as below.
CREATE TABLE [dbo].[perfData](
[row] [int] IDENTITY(1,1) NOT NULL,
[EventTime] [datetime] NOT NULL,
[SQLProcessUtilization] [int] NOT NULL,
[SystemIdle] [int] NOT NULL,
[OtherProcessUtilization] [int] NOT NULL,
CONSTRAINT [PK_perfData] PRIMARY KEY CLUSTERED
(
[row] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I have a job that adds data to it every 4 hours. I need to determine if the SystemIdle value ever goes below a set threshold and if so for how long. From that I need to report the time and duration. My approach has been the following:
;with
cte1 as (
select row, EventTime, SystemIdle, row_number() over (order by row) as rownum from perfData
where SystemIdle < 15 and EventTime > dateadd(day, -14, getdate())
),
cte2 as (
select c1.row as row1, c2.row as row2 from cte1 c1
full join cte1 c2 on c2.rownum = c1.rownum + 1
where c2.row <> c1.row + 1 or c1.row is null or c2.row is null
),
cte3 as (
select c.row, c.EventTime, c.SystemIdle, row_number() over (order by c.row) as rownum from cte2 c1
join cte1 c on c.row in (c1.row1, c1.row2)
),
cte4 as (
select c1.row as row1, c2.row as row2, c1.EventTime as time1, c2.EventTime as time2, c1.SystemIdle as idle1, c2.SystemIdle as idle2, row_number() over (order by c1.row) as rownum from cte3 c1
join cte3 c2 on c2.rownum = c1.rownum + 1
)
select time1, datediff(minute, time1, time2) from cte4
where row1 <> row2 and rownum % 2 = 1
order by cte4.row1
I'm wondering if I'm going down the right path or if there is a better way that I'm overlooking. A preliminary audit seems to confirm that it does what I expected, but I could use another pair of eyes to point out if I'm doing something head-slappingly obvious.
14090 SW TENNESSEE LN
January 23, 2011 at 1:32 pm
Sample data and expected results based on that data will go a long way in helping us help you.
January 23, 2011 at 2:44 pm
Sorry, I wasn't sure how much detail to give with regard to data or how best to represent it.
The code below will populate the table with 240 entries.
-- cpu utilization snapshot (256 samples, 1/minute)
DECLARE @ts_now BIGINT
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms)
FROM sys.dm_os_sys_info
insert into perfData
select top 240 EventTime, SqlProcessUtilization, SystemIdle, OtherProcessUtilization from (
SELECT top 100 percent
row_number() over (order by record_id) as row,
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
order by row) cc
where cc.EventTime > coalesce((select max(EventTime) from perfData), dateadd(day, -1, getdate()))
The expected results look like:
2011-01-12 03:26:41.5531
2011-01-16 18:21:10.4778315
2011-01-22 16:44:15.7604
That's the start time of the event and the duration in minutes.
14090 SW TENNESSEE LN
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply