This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

95th Percentile calculation

I noticed that Orion is taking the datasets and ordering them descending, taking the top 95 percent and returning the highest number. I'm not sure this is an appropriate way of calculating percentile. The way that I was taught was by Ranked Percentile wherein you order a dataset, separating the set into 100 equal ranks and finding the average of each rank. The 95th rank's average being the 95th percentile, of course. SQL server actually has a built in function for ranking nth'tile results and I've written a quasi-replacement structure for the functions in Orion (with small changes and excluding the max95th)

declare @Percentile as int = 95;

declare @StartDate as datetime = DATEADD(day, -30,getdate());

declare @EndDate as datetime = getdate();

with

I as (Select InterfaceID  from Interfaces where FullName like '%Verizon%'),

IT as (select I.InterfaceID, In_Averagebps, Out_Averagebps from InterfaceTraffic inner join I on I.InterfaceID = InterfaceTraffic.InterfaceID where DateTime >= @StartDate AND DateTime <= @EndDate),

IAP as (select NTILE(100) OVER(Partition by interfaceid order by In_Averagebps) AS InPercentile,InterfaceID,In_Averagebps from it),

InAVGpercentile as (select InterfaceID, AVG(In_Averagebps) as InBPS, InPercentile from IAP group by InterfaceID, InPercentile),

OAP as (select NTILE(100) OVER(Partition by interfaceid order by Out_Averagebps) AS OutPercentile,InterfaceID,Out_Averagebps from it),

OutAVGpercentile as (select InterfaceID, AVG(Out_Averagebps) as OutBPS, OutPercentile from OAP group by InterfaceID, OutPercentile)

select * from InAVGpercentile IAP inner join OutAVGpercentile OAP on iap.InterfaceID = oap.InterfaceID and iap.InPercentile = oap.OutPercentile where InPercentile = @Percentile

This code gets all interfaces that has a fullname containing 'Verizon' and returns a table of whichever percentile defined in the @Percentile variable. This code is written to run on 2008 SQL.

  • Interesting thought.  When you compare the results of your method versus the method SolarWinds is currently using in Orion how different are the results?

  • It (of course) depends on the data distribution...

    declare @Percentile as int = 95; 

    declare @StartDate as datetime = DATEADD(day, -30,getdate()); 

    declare @EndDate as datetime = getdate();

    Declare @ReferenceTemp TABLE

    (

    InterfaceID INT,

    InBPS DECIMAL(38,2),

    GetInBps95th DECIMAL(38,2),

    InDiff DECIMAL(38,2),

    InChange VarChar(max),

    OutBPS DECIMAL(38,2),

    GetOutBps95th DECIMAL(38,2),

    OutDiff DECIMAL(38,2),

    OutChange VarChar(max)

    );

    with  

    I as (Select InterfaceID  from Interfaces where FullName like '%Verizon%'), 

    IT as (select I.InterfaceID, In_Averagebps, Out_Averagebps from InterfaceTraffic inner join I on I.InterfaceID = InterfaceTraffic.InterfaceID where DateTime >= @StartDate AND DateTime <= @EndDate), 

    IAP as (select NTILE(100) OVER(Partition by interfaceid order by In_Averagebps) AS InPercentile,InterfaceID,In_Averagebps from it), 

    InAVGpercentile as (select InterfaceID, AVG(In_Averagebps) as InBPS, InPercentile from IAP group by InterfaceID, InPercentile), 

    OAP as (select NTILE(100) OVER(Partition by interfaceid order by Out_Averagebps) AS OutPercentile,InterfaceID,Out_Averagebps from it), 

    OutAVGpercentile as (select InterfaceID, AVG(Out_Averagebps) as OutBPS, OutPercentile from OAP group by InterfaceID, OutPercentile)

    Insert into @ReferenceTemp

    select IAP.InterfaceID

    ,CONVERT(decimal(38,2),IAP.InBPS) AS InBPS

    ,CONVERT(decimal(38,2),dbo.GetInBps95th(IAP.InterfaceID,@StartDate,@EndDate)) AS GetInBps95th

    ,CONVERT(decimal(38,2),(dbo.GetInBps95th(IAP.InterfaceID,@StartDate,@EndDate)-IAP.InBPS)) AS InDiff

    ,CONVERT(VarChar(max),CONVERT(decimal(38,2),((dbo.GetInBps95th(IAP.InterfaceID,@StartDate,@EndDate)/IAP.InBPS*100)-100)))+'%' AS InChange

    ,CONVERT(decimal(38,2),OAP.OutBPS) AS OutBPS

    ,CONVERT(decimal(38,2),dbo.GetOutBps95th(IAP.InterfaceID,@StartDate,@EndDate)) AS GetOutBps95th

    ,CONVERT(decimal(38,2),(dbo.GetOutBps95th(IAP.InterfaceID,@StartDate,@EndDate)-OAP.OutBPS)) AS OutDiff

    ,CONVERT(VarChar(max),CONVERT(decimal(38,2),((dbo.GetOutBps95th(IAP.InterfaceID,@StartDate,@EndDate)/OAP.OutBPS*100)-100)))+'%' AS OutChange

    from InAVGpercentile IAP

    inner join

    OutAVGpercentile OAP

    on iap.InterfaceID = oap.InterfaceID

    and iap.InPercentile = oap.OutPercentile

    where InPercentile = @Percentile

    Select RT.*, IT.In_Averagebps, IT.Out_Averagebps

    from

    InterfaceTraffic IT

    inner join

    @ReferenceTemp RT

    on IT.InterfaceID = RT.InterfaceID

    For instance if you run this through SSRS and shape the data on our system... you'll see...

    percentileDifference.PNG

  • Like ByronA, I'm interested to know how your results compare to those of Orion.  I agree with the concept that the 95th percentile doesn't appear to be working accurately in some cases; but as you stated above, its all about the data.

    Additionally, since I'm not a SQL DBA, would it be possible for you to build this in Report Writer for us?  I need a report showing monthly WAN/Internet stats on this very thing.

    Many thanks.

    D