Need help aggregating rows into 15min intervals

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help aggregating rows into 15min intervals

Author

Topic

AdmSig
Starting Member

2 Posts

Posted - 2012-02-28 : 04:24:08

I am using perfmon to gather data about specific processes running on servers in our server farm. My goal is to monitor how a process behaves over time with regards to CPU, Memory and IO usage.
I have gathered all this data in a fairly large SQL database and now I need to present this data graphically.
The problem is that there can be 20-40 rows of measurement per minute for a single process+ counter, and this makes it difficult to plot.
I would therefore like to aggregate the counter value between say 2012-02-27 08:00:00.00 and 08:15:00.00 into a single row with columns for min, max and avg

CounterDateTime MachineName InstanceName CounterName CounterValue
2012-02-27 14:30:08.049 xxxxx01 Outlook.exe Private Bytes 126234624
2012-02-27 14:31:08.049 xxxxx01 Outlook.exe Private Bytes 125186048
2012-02-27 14:31:23.049 xxxxx01 Outlook.exe Private Bytes 126234624
2012-02-27 14:31:38.049 xxxxx01 Outlook.exe Private Bytes 126234624

I want to aggregate everything within a 15min interval into a single row

Min Max AVG
2012-02-27 14:30 125186048 126234624 125972480
2012-02-27 14:45
2012-02-27 15:00
How should I go about this?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 04:55:20

Something like this?


DECLARE @sample TABLE (
	CounterDateTime DATETIME
	, MachineName VARCHAR(25)
	, InstanceName VARCHAR(50)
	, CounterName VARCHAR(50)
	, CounterValue INT
	)
INSERT @sample
VALUES
	  ('2012-02-27 14:00:08.049', 'xxxxx01',	'Outlook.exe',	'Private Bytes',	125186048)
	, ('2012-02-27 14:15:08.049', 'xxxxx01',	'Outlook.exe',	'Private Bytes',	125186048)
	, ('2012-02-27 14:30:08.049', 'xxxxx01',	'Outlook.exe',	'Private BYTES',	126234624)
	, ('2012-02-27 14:31:08.049', 'xxxxx01',	'Outlook.exe',	'Private Bytes',	125186048)
	, ('2012-02-27 14:31:23.049', 'xxxxx01',	'Outlook.exe',	'Private Bytes',	126234624)
	, ('2012-02-27 14:31:38.049', 'xxxxx01',	'Outlook.exe',	'Private Bytes',	126234624)
	, ('2012-02-27 14:31:59.049', 'xxxxx01',	'Outlook.exe',	'Private Bytes',	126234624)
	, ('2012-02-27 14:54:10.049', 'xxxxx01',	'Outlook.exe',	'Private Bytes',	126234624)

SELECT
	[DateToHour]
	, [HourSegment]
	, [MachineName]
	, [CounterName]
	, MAX([CounterValue]) AS [MaxCounterValue]
	, MIN([CounterValue]) AS [MinCounterValue]
	, AVG([CounterValue]) AS [AVGCounterValue]
FROM
	(
	SELECT
		DATEADD(HOUR, DATEDIFF(HOUR, '20100101', [CounterDateTime]), '20100101') AS [DateToHour]
		, (DATEPART(MINUTE, [CounterDateTime]) / 15) + 1 AS [HourSegment]
		, [MachineName]
		, [CounterName]
		, [CounterValue]
	FROM
		@sample
	)
	AS s
GROUP BY
	[DateToHour]
	, [HourSegment]
	, [MachineName]
	, [CounterName]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 05:05:49

Results:



DateToHour              HourSegment MachineName  CounterName    MaxCounterValue MinCounterValue AVGCounterValue
----------------------- ----------- ------------ -------------- --------------- --------------- ---------------
2012-02-27 14:00:00.000 1           xxxxx01      Private Bytes  125186048       125186048       125186048
2012-02-27 14:00:00.000 2           xxxxx01      Private Bytes  125186048       125186048       125186048
2012-02-27 14:00:00.000 3           xxxxx01      Private BYTES  126234624       125186048       126024908
2012-02-27 14:00:00.000 4           xxxxx01      Private Bytes  126234624       126234624       126234624

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

AdmSig
Starting Member

2 Posts

Posted - 2012-02-28 : 06:15:35

Hmm that looks like a viable option. Going to do some testing.
Thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources