/**
* Scan Ingestion Processing Time Percentile Report
* A scan throughput report in precentile/end-2-end ingestion time reports
* Default: limits results to latest 1000 PendingScan that active flag is true/hasn't been user modified
* Parameters:
* Count limit: change the number after the TOP keyword, you can also remove it all together. (Default: TOP 1000)
* Order: switch DESC to ASC for earliest scans. (Default: ORDER BY createdDate DESC)
* Active
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
status AS Status,
COUNT(*) AS total,
AVG(twentyfifthPTime) AS '25th % Processing Time (Sec)',
AVG(medianPTime) AS 'Median Processing Time (Sec)',
AVG(eightiethPTime) AS '80th % Processing Time (Sec)',
AVG(ninetiethPTime) AS '90th % Processing Time (Sec)',
AVG(ninetyfifthPTime) AS '95th % Processing Time (Sec)'
FROM
(SELECT
status,
PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY processingTime ASC)
OVER (PARTITION BY status) AS medianPTime,
PERCENTILE_CONT(0.25)
WITHIN GROUP (ORDER BY processingTime ASC)
OVER (PARTITION BY status) AS twentyfifthPTime,
PERCENTILE_CONT(0.80)
WITHIN GROUP (ORDER BY processingTime ASC)
OVER (PARTITION BY status) AS eightiethPTime,
PERCENTILE_CONT(0.90)
WITHIN GROUP (ORDER BY processingTime ASC)
OVER (PARTITION BY status) AS ninetiethPTime,
PERCENTILE_CONT(0.95)
WITHIN GROUP (ORDER BY processingTime ASC)
OVER (PARTITION BY status) AS ninetyfifthPTime
FROM
(SELECT TOP 1000 -- this can be adjusted
pendingScanStatus AS status,
createdDate,
endTime,
(DATEDIFF(millisecond, CONVERT(DATETIME2, createdDate, 103), CONVERT(DATETIME2, modifiedDate, 103))/1000) AS processingTime
FROM NewPendingScan WHERE active = 1 ORDER BY createdDate DESC) ps) report
GROUP BY status;
GO |