[QUESTION] Lite - Perfmon graph spikes & multiple values at same timestamp

[dphugo]

Which component is your question about?

Lite

Performance Monitor Version

2.2.0

Is your question about how it works, or the results?

Results / data interpretation

What's your question?

Hi @erikdarlingdata,

Thanks again for fixes #483 and #491 that resolved most of the graph spikes as mentioned in #482.
I thought I’d raise this as a question rather than a bug, as I might be misinterpreting some of the graph results.

1. On the Perfmon tab the spikes after starting up the Lite edition remains

• In the screenshot below I started up Lite at around 9:00am with the Range set to Last 12 hours.

• If I set the Range to Last 24 hours it picks up yesterday’s data and then draws the graph as follows:

• If I set it to Last 4 hours the spikes have disappeared and this what I would expect the above 2 graphs to look like.
  But maybe the above graphs are by design?

Additionally, if the collection timed out, this would also create a tiny spike as the delta is now between the last collection point and the current collection point.
In the graph below it’s set to collect at 1-minute intervals, but it timed out for some of the intervals creating these small spikes.

If the first 2 graphs and the graph above are not what is expected, I suppose there are 2 options for these scenarios: on application startup OR missed collection:

1. Add a zero-value data point at the current collection timestamp and then add another delta collection value at the same timestamp.
2. [My preference] Add the last delta collected data point to the current timestamp and then either:
   i) [My preference] Add the delta collected data point at the same timestamp
   ii) Add the delta collected data point at the next timestamp.

Naturally the above are only suggestions, and would highly appreciate your views?

2) Multiple data points at the same timestamp for the same metric

Here again I might be misinterpreting the graph, but it seems like there are multiple datapoints at the same timestamp for some of the metrics.
If we zoom in a bit on the Perfmon graph I would say that the Batch Requests/sec, SQL Compilations/sec and SQL Re-Compilations/Sec lines are as expected. But the Query optimizations/sec and Network IO waits lines are unexpected.

These are rendering more than 1 data point value for the same timestamp which causes these jagged like shape or a single point rise.
My expectation is that these metrics should only have 1 value per timestamp and look similar to the Batch Requests/sec, SQL Compilations/sec and SQL Re-Compilations/Sec lines.

It’s not just on these specific counters only, as you can see in the graph below of I/O Pressure Counters’ Log Bytes Flushed/sec having multiple datapoints per timestamp. Again, my expectation is that there would only be 1 datapoint per timestamp, but I might be misunderstanding the graph.

3) Colour palate variety expansion

Another small thing I noticed in the graph above is that the colours of Page reads/sec and Log Bytes Flushed/sec are quite close to each other. Maybe the colour palate could be expanded a bit with a wider variety of distinct colours?
I initially thought it’s just the 2 different lines rendering causing the render above, but these are 2 different lines.

Looking at the monitor.duckdb with duckdb -ui shows that the grain of table includes instance_name which explains why there are multiple datapoints per timestamp.
Herewith for Query optimizations/sec having instance_name values of default and internal.

Herewith for Network IO waits having 4 different instance_name values.

4. I’m also not sure what the sample_interval_seconds column is used for but the values seem erratic?

Additional Context

SQL Server Version: SQL Server 2022 (RTM-CU16)

Windows Version: Windows 11 23H2

[erikdarlingdata]

Thanks for the incredibly detailed report — your investigation with duckdb -ui nailed it.

Here's what I found looking at the code:

1. Startup spikes on wider time ranges

The baseline fix from PR #483 handles the first collection correctly (returns delta=0), but when Lite has been off for hours and starts back up, the first delta spans the entire gap since the last session. I'll add gap detection to the delta calculator so it treats collection gaps the same as a fresh baseline.

2. Multiple data points per timestamp (confirmed bug)

You're exactly right. The perfmon chart query pulls all rows without aggregating by instance_name. Counters like "Query optimizations/sec" have instances "default" and "internal", and "Network IO waits" has 4 separate instances — all plotted as individual points, causing the jagged look.

The fix is to SUM(delta_cntr_value) grouped by (collection_time, counter_name) in the chart query so all instances roll up into a single data point per timestamp.

3. Color palette

Good catch on the similar colors — I'll look at widening the palette for better contrast.

4. sample_interval_seconds

That column is hardcoded to 600 (10 minutes) in the collector even though collection runs every 1 minute. It's not actually used in any queries currently, but I'll fix it to reflect the real interval.

Fixes incoming — will be in the next release. Thanks again for the thorough report!