SQL Server and PostgreSQL can indicate report `0` as the queue length value

[tmasternak]

Symptoms

When using PostgreSQL or SQL Server transport, the 1-minute queue length diagram (shown in ServicePulse) can report 0 as the queue length value for some of the points on the graph. This happens even if the queue is non-empty and results in a characteristic saw-tooth shape of the graph.

Who's affected

All PostgreSQL and SQL Server transport users.

Root cause

The problem is caused by the frequency (every 1 second both for PostgreSQL and SQL Server) at which the queue length measurements are made. The points on the Queue Length Diagram represent an average value for a given time interval that starts at a given time. The length of this interval depends on the visualization period chosen in the UI and by default is set to 1 minute. Every such period is divided into 60 intervals which means that for the 1 minute history period each point represents a 1 second bucket.

ServiceControl/src/ServiceControl.Monitoring/Infrastructure/HistoryPeriod.cs

Lines 59 to 66 in 038bc17

	public static IReadOnlyCollection<HistoryPeriod> All =
	[
	new(TimeSpan.FromMinutes(1), numberOfIntervals: 60, delayedIntervals: 2),
	new(TimeSpan.FromMinutes(5), numberOfIntervals: 60, delayedIntervals: 1),
	new(TimeSpan.FromMinutes(10), numberOfIntervals: 60, delayedIntervals: 1),
	new(TimeSpan.FromMinutes(15), numberOfIntervals: 60, delayedIntervals: 1),
	new(TimeSpan.FromMinutes(30), numberOfIntervals: 60, delayedIntervals: 1),
	new(TimeSpan.FromMinutes(LargestHistoryPeriod), 60, 1)

With the query executing every 1 second, it is very likely that, due to jitter in scheduling and query execution times in some intervals, there won't be any measurements made. This in turn causes points on the diagram to indicate the queue length value of 0.

Patched version

• https://github.com/Particular/ServiceControl/releases/tag/6.0.1
• https://github.com/Particular/ServiceControl/releases/tag/5.11.1