SQL Profiler Trace ~ 95th percentile value for duration

• simon_s

  Ten Centuries

  Points: 1354

  More actions

  September 20, 2017 at 3:09 am

  #323579

  Hi All

  Ive been tasked with adding the  95th percentile value for duration to the results I supply from profiler traces. Whilst I can get an overall value Im failing to get it to work per stored proc 

  Any pointers ?

  WITH
percentiles AS
(
SELECT
  NTILE(100) OVER (ORDER BY duration) AS percentile,
  *
FROM
  trace_table
)
SELECT *
FROM percentiles
WHERE percentile = 90

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  September 20, 2017 at 7:52 am

  #1960165

  Depends on what you mean by "adding the 95th percentile".   If you're only looking at 90 right now, and want to look at both 90 AND 95, then try this:
  WITH percentiles AS (

      SELECT
        NTILE(100) OVER (ORDER BY duration) AS percentile,
        *
    FROM trace_table
)
SELECT *
FROM percentiles
WHERE percentile IN (90, 95);

  However, as you're breaking down your data by the individual percentage, I wonder why you wouldn't be using a comparison that says greater than or equal to 90 or 95, depending on what, exactly, you're looking to see.   Given the query you initially supplied, you only see the 90 values, but not 91 through 100.  Similarly, my replacement query will only show 90 and 95, and not 91 through 94 or 96 through 100.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)