Calculating Bandwidth - SQL Server Forums

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

Calculating Bandwidth

Author

Topic

bwiechman
Starting Member

1 Post

Posted - 2010-11-01 : 17:59:48

I am trying to come up with a query to generate a report. We are monitoring bandwidth consumption on a number of interfaces in the network. We have customers who have redundant connections from us and I need to calculate the 95th percentile based on a five minute sample using the sum of the total bandwidth inbound and outbound across all interfaces in use by a single customer.

Problems:
- each interface has a record within a five minute window but they are not polled at the same time.
- There may not be a record within a five minute window
- There may be more than one record per five minute window

The data is stored in a table.
Table Interface_Details
int InterfaceID (id of particular interface)
real In_Maxbps real (max rate during that period)
real Out_Maxbps (max outbound rate during that period)
DATETIME DateTime(datetime interface was polled - I didn't name the columns...)

This is the query I've worked out that seems to be what I would need for one direction, either inbound or outbound, however the results diverge somewhat from what I would have

@StartDate/@EndDate set the start/end time to the previous month.
[id1] and [id2] would be replaced with the actual interface IDs.

SELECT
SUM(In_Maxbps) AS Combined_In_Maxbps,
dateadd(mi, datediff(mi,0,DateTime)/5*5, 0) AS DateTime
FROM InterfaceTraffic_Detail
WHERE ( InterfaceID = [id1] OR InterfaceID = [id2] )
AND DateTime BETWEEN @StartDate AND @EndDate
GROUP BY DateTime
ORDER BY Combined_In_Maxbps ASC;

Roughly what I am trying to do
- round each poll time down to the previous five minute interval
- Calculate the sum of the throughput for all interfaces at that time. Typically two interfaces, but it may be more than that.

This however does not work. It simply pulls the bandwidth for both interfaces individually instead of the sum of bandwidth.

My JOIN FU is not strong...

Considering the three points above:
- If a particular interface does not have an entry for a particular five minute period I would like to retrieve the value(s) that do exist.
- If there are duplicate entries for an interface in a five minute period I don't care if one of them is not considered.
- If none of the interfaces are polled during a five minute period I am OK with no value being returned. (Seems much easier than generating a tmp table that contains an entry for every five minute timeslot for the period in question, although technically that would be the most accurate method.)

Is there a way I can get this sum? Even if it takes multiple steps?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-11-02 : 07:15:25

I am not quite sure what you are doing, but the obvious problem with your query is that [DateTime] is both a calculated column and a table column.
(ie I suspect that you want to group by the calculated column which will not be in scope by the time the GROUP BY is applied.)
Maybe you want something like:


SELECT SUM(In_Maxbps) AS Combined_In_Maxbps
	,GroupTime
FROM
(
	SELECT In_Maxbps
		,DATEADD(mi, DATEDIFF(mi, 0, [DateTime]) / 5 * 5, 0) AS GroupTime
	FROM InterfaceTraffic_Detail
	WHERE InterfaceID IN (<id1>, <id2>)
		AND [DateTime] BETWEEN @StartDate AND @EndDate
) D
GROUP BY GroupTime
ORDER BY Combined_In_Maxbps

ps. The logical order of evaluation of the main SQL clauses is something like:
FROM (including JOINs)
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources