Peak Concurrency: The Busiest Moment From Start and End Times

Peak concurrency is the most things happening at the same moment, not the total number of things that happened. You can find it from nothing but a start time and an end time, with one running sum.

Total sessions and peak are different questions

A manager asks how many connections you need at the busiest moment. A junior DBA answers with a count of sessions for the day. That number is true, and it is useless. Four sessions that never overlap need one connection. Four that all overlap need four.

Here are four sessions. The last one has no end time because it is still open.

DROP TABLE IF EXISTS #Sessions;

CREATE TABLE #Sessions (Id int PRIMARY KEY, Started datetime2(0), Ended datetime2(0));

INSERT #Sessions VALUES
    (1, '2025-01-01T09:00:00', '2025-01-01T10:00:00'),
    (2, '2025-01-01T09:30:00', '2025-01-01T10:30:00'),
    (3, '2025-01-01T10:00:00', '2025-01-01T11:00:00'),
    (4, '2025-01-01T10:15:00', NULL);

Turn each session into two events

The trick is to stop thinking in intervals. Every session becomes two events: a start worth +1 and an end worth -1. Add the events up in time order and you get the number of sessions open at each moment.

Two rules go with it. A session counts from its start up to, but not including, its end. And an open session needs an end, so I pick one cutoff and use it. Here the cutoff is noon.

DECLARE @Cutoff datetime2(0) = '2025-01-01T12:00:00';

SELECT Started AS EventAt, 1 AS Delta
INTO #Events
FROM #Sessions
UNION ALL
SELECT CASE WHEN Ended IS NULL OR Ended > @Cutoff THEN @Cutoff ELSE Ended END, -1
FROM #Sessions;

SELECT EventAt, Delta
FROM #Events
ORDER BY EventAt, Delta;

You get eight events. Look at 10:00. Session 1 ends at the very moment session 3 starts, so there is a -1 and a +1 with the same timestamp.

Why shared timestamps need care

That shared timestamp is where people get burned. If the running sum processes the +1 first, the count briefly says 3 at 10:00. The truth is 2. The order of tied rows is arbitrary unless you remove the choice.

SELECT EventAt, Delta,
       SUM(Delta) OVER (ORDER BY EventAt, Delta DESC ROWS UNBOUNDED PRECEDING) AS RunningCount
FROM #Events
ORDER BY EventAt, Delta DESC;

With starts sorted first, the running count shows 3 at 10:00, then 2. That 3 never happened. The fix is simple: add up the deltas for each timestamp first, so a tie becomes one net change.

Build the busy periods

Group the events by time, take the running sum, and use LEAD to find where each period ends. The last event has no next event, so it is not a period, and the query drops it.

CREATE TABLE #Spans (PeriodStart datetime2(0), PeriodEnd datetime2(0), ActiveCount int);

WITH Grouped AS
(
    SELECT EventAt, SUM(Delta) AS Delta
    FROM #Events
    GROUP BY EventAt
),
Running AS
(
    SELECT EventAt,
           SUM(Delta) OVER (ORDER BY EventAt ROWS UNBOUNDED PRECEDING) AS ActiveCount
    FROM Grouped
),
Spans AS
(
    SELECT EventAt AS PeriodStart,
           LEAD(EventAt) OVER (ORDER BY EventAt) AS PeriodEnd,
           ActiveCount
    FROM Running
)
INSERT #Spans (PeriodStart, PeriodEnd, ActiveCount)
SELECT PeriodStart, PeriodEnd, ActiveCount
FROM Spans
WHERE PeriodEnd IS NOT NULL;

SELECT PeriodStart, PeriodEnd, ActiveCount
FROM #Spans
ORDER BY PeriodStart;

Six periods come back. At 10:00 the count stays at 2, because one session ended as another began. The busiest period runs from 10:15 to 10:30, with 3 sessions open.

Report the peak with its rules

Now answer the manager. TOP (1) WITH TIES returns every period that shares the highest count, which matters if you also need to know how long the peak lasted.

SELECT COUNT(*) AS TotalSessions
FROM #Sessions;

SELECT TOP (1) WITH TIES PeriodStart, PeriodEnd, ActiveCount AS PeakConcurrency
FROM #Spans
ORDER BY ActiveCount DESC;

The day had 4 sessions, but never more than 3 at once. Those are two different answers to two different questions.

Write the rules next to the number. Say how open sessions were handled, which cutoff you used, and that touching endpoints do not overlap. Watch the input too. A duplicated row counts twice, and one person with several sessions is not several people.

DROP TABLE IF EXISTS #Spans;
DROP TABLE IF EXISTS #Events;
DROP TABLE IF EXISTS #Sessions;

Next time someone asks for the busiest moment, count the overlap, not the day.

A daily count is not peak concurrency, it is activity without an overlap rule.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.