Slow-tail response times can explain a service problem that an average conceals. SQL Server 2022 adds APPROX_PERCENTILE_CONT for approximate percentile summaries when the accepted contract permits estimation.
Define the Population and Percentile Meaning
A median describes the center of the ordered population. A ninety-fifth percentile describes a point toward its slower end. Neither is a maximum, and neither explains which individual request was slow. Define the measurement unit, service grouping, time window, and treatment of failures before calculating the summary.
A percentile over all services combined can hide a small slow service behind a large fast one. Group at the level that matches the question. Excluding failed requests can also change the result materially. I state the population explicitly so a good-looking summary cannot silently omit the operations users are complaining about.
The continuous percentile interpolates according to its definition and can return a value not present in the input. The discrete version returns an input value. Choose the meaning required by the report rather than treating the functions as spelling variants. NULL values are ignored, which makes missing measurements a separate coverage concern.
Create a Small Controlled Response Dataset
The following temporary table uses synthetic response times in milliseconds. They are inputs for inspecting function behavior, not measured service performance. The NULL row represents missing timing evidence and should remain visible in the coverage report.
CREATE TABLE #ResponseTimes
(
RequestID int PRIMARY KEY,
ServiceID int NOT NULL,
ResponseMs int NULL
);
INSERT #ResponseTimes VALUES(1,1,10),(2,1,20),(3,1,25),(4,1,30),
(5,1,40),(6,1,1000),(7,1,NULL),
(8,2,15),(9,2,25),(10,2,35);
SELECT ServiceID,COUNT_BIG(*) AS RecordedRequests,
COUNT(ResponseMs) AS TimedRequests
FROM #ResponseTimes GROUP BY ServiceID;Do not substitute zero for a missing response time merely to make the aggregate easier. That changes the population and shifts the distribution toward artificially fast values. Decide how the report signals incomplete timing and whether a coverage threshold is required before publishing a percentile.
The tiny sample makes the expressions easy to inspect. It is not a useful performance benchmark for an approximate streaming algorithm. Use a representative volume and distribution for the resource comparison, while keeping this small example for correctness and result-shape review.
Calculate APPROX_PERCENTILE_CONT and Its Discrete Partner
The approximate functions are aggregate expressions with WITHIN GROUP ordering. They can produce grouped results without returning one window-function value for every source row. This example uses integer response times, a supported input for both illustrated functions.
SELECT ServiceID,
APPROX_PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY ResponseMs) AS ApproxMedian,
APPROX_PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY ResponseMs) AS ApproxP95,
APPROX_PERCENTILE_DISC(0.95) WITHIN GROUP(ORDER BY ResponseMs) AS ApproxDiscreteP95
FROM #ResponseTimes
GROUP BY ServiceID;Check supported input types for the particular function. Do not assume the discrete approximate function accepts every type supported by an exact ordered-set expression. Continuous output uses its documented floating-point return type, while the discrete result follows the supported input type. Preserve that distinction in the receiving schema.
For APPROX_PERCENTILE_CONT, approximation reduces the need for the exact ordered population through a compact sketch. It still reads the relevant data and performs aggregation. A faster summary is not guaranteed for every tiny input, and an unsuitable scan or grouping can still dominate the query.
Compare APPROX_PERCENTILE_CONT With the Exact Window Function
PERCENTILE_CONT uses a window expression. The result repeats for rows in each partition, so the example selects distinct service summaries for comparison. Keep the same population and ordering expression as the approximate query.
SELECT DISTINCT ServiceID,
PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY ResponseMs)
OVER(PARTITION BY ServiceID) AS ExactMedian,
PERCENTILE_CONT(0.95) WITHIN GROUP(ORDER BY ResponseMs)
OVER(PARTITION BY ServiceID) AS ExactP95
FROM #ResponseTimes;Inspect the exact and approximate values, but do not invent a measured difference before running the queries. On this tiny sample, the two queries return the same medians and 95th percentiles. Exact percentile work needs the ordered-value information and can require substantial sorting and memory on large inputs. The actual plan and existing access paths determine the operation's resource cost.
A different result from a repeated approximate execution can be part of the randomized sketch behavior. Do not treat every small variation as data corruption. Keep the input snapshot stable when evaluating that variation, and label the summary as approximate in any interface that depends on the distinction.
Read the APPROX_PERCENTILE_CONT Error Bound as Rank Error
The documented guarantee is a rank-based error bound of up to 1.33 percent with 99 percent confidence. It is not a promise that the reported duration is within 1.33 percent of the exact millisecond value. Those are different error measures, especially where the distribution rises sharply near its tail.
A small rank shift can span a large value gap in a heavily skewed distribution. That matters when a decision is close to a duration threshold. Assess the accepted rank tolerance and the relevant value behavior on representative data rather than converting the guarantee into a universal timing percentage.
Which decision changes when the approximate result lies near the threshold? Define an exact follow-up or another accepted verification rule for that case. I keep estimation in the report's contract so downstream consumers know when they need a precise calculation. An approximate answer should not acquire extra authority by losing its label.
Measure Resource Use on a Larger Lab Population
The following generator supplies a larger synthetic distribution with a deliberate slow tail. It requires SQL Server 2022 or later and compatibility level 160 or higher. Compare the approximate and exact statements over the same retained input.
CREATE TABLE #LargeResponses(RequestID int PRIMARY KEY,ServiceID int,ResponseMs int);
INSERT #LargeResponses
SELECT CONVERT(int,value),1+CONVERT(int,value%3),
CASE WHEN value%1000=0 THEN 10000 ELSE 10+CONVERT(int,value%1000) END
FROM GENERATE_SERIES(1,1000000,1);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT ServiceID,APPROX_PERCENTILE_CONT(0.95)
WITHIN GROUP(ORDER BY ResponseMs) AS ApproxP95
FROM #LargeResponses GROUP BY ServiceID;
SELECT DISTINCT ServiceID,PERCENTILE_CONT(0.95)
WITHIN GROUP(ORDER BY ResponseMs) OVER(PARTITION BY ServiceID) AS ExactP95
FROM #LargeResponses;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;Capture actual plans, CPU, elapsed time, memory grants, and any spill evidence under controlled conditions. Include the grouping and filtering used by the real report. The generator's row count and tail values are chosen test inputs, not claimed observations about production.
Repeat the comparison with the same data snapshot. Run the approximate query more than once and its values move slightly from run to run while staying close to the exact result. Cache state and other workload activity can affect duration. Verify that each query returns the accepted service population and units before interpreting a resource difference. An efficient answer to a changed question is not a successful optimization.
Keep Exact Values Where the Contract Requires Them
Use exact calculations when the report or decision requires exact ordered-set semantics. Approximation fits exploratory monitoring and large summaries with an accepted tolerance. Do not silently replace an existing exact contractual output with an estimate because the faster query is convenient.
APPROX_PERCENTILE_CONT is useful when its precision and resource tradeoff match the workload. Retain coverage, population, unit, function type, and approximation label with the result. That makes the summary both faster to obtain and honest about what it establishes.
Related reading on this blog: APPROX_COUNT_DISTINCT: Not Always Efficient and Introduction to PERCENTILE_CONT(): Analytic Functions Introduced in SQL Server 2012.
An approximate percentile is not a fixed percentage error in milliseconds, it is an estimate with a documented rank-based precision contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
