Combining Two SQL queries for Network Utilization

Question

looking for some assistance in combining two SQL queries (or provide an opinion on feasibility.) Individually these are used by our Health Authority, Network Services group to plan WAN bandwidth allocations. Our Network Monitoring System uses MS SQL Server 2012 as the database.

The first query pulls out a metric when average utilization for a WAN link is over 90% in the last 30 days. This was not created by me but appears to be a good proxy for highly used networks. Not sure why there is two datetime statements in the 'where' clause:

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate =CAST((ROUND(CAST(GetDate() - 30 AS FLOAT), 0, 1)) as datetime) 
SET @EndDate = GetDate()

SELECT     
    Nodes.HA, 
    Nodes.Site, 
    Nodes.Address,
    Nodes.City,
    Nodes.SiteType,
    Interfaces.WANFeed, 
    (Interfaces.InBandwidth / 1000000) As Subscribed_Mbps,
    Count(*) as SaturationEvents

FROM
    Interfaces 
    INNER JOIN InterfaceTraffic_Detail ON Interfaces.InterfaceID = InterfaceTraffic_Detail.InterfaceID 
    INNER JOIN Nodes ON Interfaces.NodeID = Nodes.NodeID
WHERE      
    (NOT (Interfaces.WANFeed IS NULL)) 
    AND (InterfaceTraffic_Detail.DateTime > DATEADD(day, -30, GETDATE())) 
    AND (InterfaceTraffic_Detail.In_Averagebps / Interfaces.InBandwidth > .9)
    AND (Nodes.SiteType = 'Acute')
    AND (DateTime between @StartDate and @EndDate)

GROUP BY 
    Nodes.HA,
    Nodes.SiteType,
    Nodes.Site,
    Nodes.Address,
    Nodes.City, 
    Interfaces.WANFeed,
    Interfaces.InBandwidth

Order By SaturationEvents Desc

Sample output (cleaned)

HA  SITE    Address City    SiteType    WANFeed Subscribed_Mbps Saturation Events
--- --- --- --- Acute   Primary 10  252
--- --- --- --- Acute   Primary 10  152
--- --- --- --- Acute   Primary 100 104
--- --- --- --- Acute   Primary 10  57

I created the second query based on an example provided by our NMS vendor. It reports on 95th percentile utilization:

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate =CAST((ROUND(CAST(GetDate() - 30 AS FLOAT), 0, 1)) as datetime) 
SET @EndDate = GetDate()

SELECT  

Nodes.HA,
Nodes.Site,
Nodes.Address,
Nodes.City,
Interfaces.WANFeed,
Nodes.SiteType,
(Interfaces.InBandwidth / 1000000) As Subscribed_Mbps,
Interfaces.InterfaceId,
Nodes.NodeID,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
Maxbps_In95,
Maxbps_Out95,
Maxbps_95

FROM Nodes
INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN (
    SELECT  InterfaceID,
            dbo.GetInBps95th(InterfaceID,@StartDate ,@EndDate ) AS Maxbps_In95,
            dbo.GetOutBps95th(InterfaceID,@StartDate ,@EndDate ) AS Maxbps_Out95,
            dbo.GetMaxBps95th(InterfaceID,@StartDate ,@EndDate ) AS Maxbps_95
    FROM InterfaceTraffic
    WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate 
    GROUP BY InterfaceID
) TrafficStat
ON Interfaces.InterfaceID = TrafficStat.InterfaceID

WHERE
(NOT (Interfaces.WANFeed IS NULL)) AND
(Nodes.SiteType = 'Acute') AND
(Interfaces.WANFeed <> 'Secondary') 

ORDER BY HA,NodeName, Interface_Caption

Sample Output (cleaned)

SITE    Address City    SiteType    WANFeed Subscribed_Mbps InterfaceID NodeID  Hostname    Interface_Caption   Maxbps_In95 Maxbps_Out95    Maxbps_95
--- --- --- Primary Acute   10000   25899   3516    --- --- 2.84E+08    3.92E+08    4.01E+08
--- --- --- Primary Acute   20000   23428   3508    --- --- 7.44E+08    3.50E+08    7.52E+08
--- --- --- Primary Acute   10000   23354   3508    --- --- 5.74E+08    4.61E+08    6.46E+08
--- --- --- Primary Acute   10000   23368   3508    --- --- 2.28E+08    4.59E+07    2.28E+08
--- --- --- Primary Acute   1000    11389   1232    --- --- 8.42E+07    8.55E+07    1.06E+08
--- --- --- Primary Acute   1000    29360   1234    --- --- 6.65E+07    3.95E+07    7.46E+07

My hope is that they could be combined into one report (only some sites would have saturation but all would have 95th percentile.)

Thanks for any time spent looking at this.

Have you tried left joining the 2 queries? – Rabbit Dec 03 '15 at 23:22 — Rabbit, Dec 03 '15 at 23:22

score 0 · Answer 1 · answered Dec 04 '15 at 02:48

First, I think that it cause more confusion than it is worth to combine directly into one query. However, you could declare two temp tables, fill each table from a different query, then select the desired result set from a join between the tables.

In addition, it seems like the queries are a little more complicated than necessary and can be made to run a little faster and be clearer with a smidgen of tweaking. I suggest the following:

There is no need for an @EndDate parameter because the queries are running up to the current time. Therefore, change each date comparison to look for DateTime >= @StartDate.
(NOT (Interfaces.WANFeed IS NULL)) adds unnecessary complexity. Use (Interfaces.WANFeed IS NOT NULL) instead.
Remove or clarify the AND (DateTime between @StartDate and @EndDate) at the end of the WHERE clause in the first query. It looks redundant and is not fully qualified with its proper alias. If this is a different field than the first date comparison, just add the table alias for clarification.

I hope all of this helps.

Thanks for the feedback -- I will cleanup the date related parameters and research temp tables. Will repost if I make any progress. — effluvium, Dec 04 '15 at 18:43

Combining Two SQL queries for Network Utilization

1 Answers1