Post reply

Trace file 95 Percentile alongside min/avg/max

  • May 8, 2019 at 1:03 pm

    #3638290

    Hi

     

    Im trying to show the 95th percentile duration value, per proc , alongside side the min/avg/max duration.

    And Im hitting a brick wall. How on earth do I calculate the percentile per proc. Any words of advise gratefully received

    thank you

     

    simon

  • May 8, 2019 at 1:15 pm

    #3638294

    Simon

    Have you tried the NTILE function?

    John

  • May 8, 2019 at 2:26 pm

    #3638340

    I did try this but the results were garbage 🙁

     


    WITH
    percentiles AS
    (
    SELECT
    NTILE(100) OVER (ORDER BY duration) AS percentile,
    *
    FROM
    trace_table
    )
    SELECT a.textdata2 , a.duration as '95th' ,min(b.duration) as 'min',max(b.duration) as max
    FROM percentiles a 
    join trace_table b 
    on a.textdata2 = b.textdata2
    where percentile = 95
    group by a.textdata2 ,a.duration

  • May 8, 2019 at 2:41 pm

    #3638348

    Have you tried percentile_disc?

    https://docs.microsoft.com/en-us/sql/t-sql/functions/percentile-disc-transact-sql?view=sql-server-2017

     

  • May 8, 2019 at 2:42 pm

    #3638349

    Without any sample data, or knowing what is garbage about your results, it's difficult to help you.

    Presumably you're looking for the top 5% of durations for each proc?  In which case, I think you would be better doing NTILE(20) and WHERE percentile = 20.  Either that or leave it as NTILE(100) and have WHERE percentile > 95.

    John

    Edit: depending on what you are actually looking for, Steve's suggestion may be better

  • May 10, 2019 at 6:48 am

    #3639665

    Percentile_Cont or Percentile_Disc (depending upon which type you want) would probably be the way to go.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply