[BUG] Memory usage on client

[000al000]

Component

Lite

Performance Monitor Version

2.10

SQL Server Version

Microsoft SQL Server 2016 (SP3-CU1-GDR)

Windows Version

Windows 11 23H2

Describe the Bug

High memory usage on client.
4 servers in monitoring
Memory usage about 2700-2900 Mb after 10 minutes of starting app

Steps to Reproduce

1. Start Perfomance monitor light
2. Waiting 10 minutes

Expected Behavior

Less memory usage

Actual Behavior

Memory usage higher that expected

Error Messages / Log Output

2026-05-05 09:38:26.978 [INFO ] [Collector] Running 24 collectors for 'plvs-cube-sql03.roshen.trade' (serverId=999618653)
2026-05-05 09:38:26.978 [INFO ] [RemoteCollectorService] Running 24 collectors for server 'plvs-cube-sql03.roshen.trade' (initial load)
2026-05-05 09:38:27.235 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] wait_stats => 121 rows in 256ms (sql:16ms, duck:239ms)
2026-05-05 09:38:29.592 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] query_stats => 200 rows in 2266ms (sql:2266ms, duck:2266ms)
2026-05-05 09:38:29.855 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] procedure_stats => 0 rows in 163ms (sql:117ms, duck:45ms)
2026-05-05 09:38:30.061 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] query_store => 0 rows in 120ms (sql:61ms, duck:0ms)
2026-05-05 09:38:30.256 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] query_snapshots => 0 rows in 99ms (sql:49ms, duck:50ms)
2026-05-05 09:38:30.567 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] cpu_utilization => 1 rows in 215ms (sql:101ms, duck:69ms)
2026-05-05 09:38:30.811 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] file_io_stats => 25 rows in 161ms (sql:64ms, duck:96ms)
2026-05-05 09:38:31.035 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] memory_stats => 1 rows in 141ms (sql:50ms, duck:90ms)
2026-05-05 09:38:31.225 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] memory_clerks => 25 rows in 110ms (sql:17ms, duck:92ms)
2026-05-05 09:38:31.405 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] tempdb_stats => 1 rows in 101ms (sql:19ms, duck:82ms)
2026-05-05 09:38:31.607 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] perfmon_stats => 218 rows in 118ms (sql:33ms, duck:85ms)
2026-05-05 09:38:31.973 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] deadlocks => 0 rows in 287ms (sql:76ms, duck:40ms)
2026-05-05 09:38:32.147 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] server_config => 101 rows in 98ms (sql:19ms, duck:41ms)
2026-05-05 09:38:32.379 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] database_config => 7 rows in 150ms (sql:48ms, duck:53ms)
2026-05-05 09:38:32.553 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] memory_grant_stats => 2 rows in 91ms (sql:16ms, duck:74ms)
2026-05-05 09:38:32.783 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] waiting_tasks => 2 rows in 129ms (sql:42ms, duck:87ms)
2026-05-05 09:38:33.205 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] blocked_process_report => 0 rows in 316ms (sql:214ms, duck:50ms)
2026-05-05 09:38:33.596 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] database_scoped_config => 286 rows in 278ms (sql:159ms, duck:44ms)
2026-05-05 09:38:33.775 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] trace_flags => 0 rows in 70ms (sql:18ms, duck:44ms)
2026-05-05 09:38:33.959 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] running_jobs => 0 rows in 84ms (sql:31ms, duck:53ms)
2026-05-05 09:38:34.282 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] database_size_stats => 31 rows in 229ms (sql:113ms, duck:115ms)
2026-05-05 09:38:34.518 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] server_properties => 1 rows in 136ms (sql:18ms, duck:115ms)
2026-05-05 09:38:34.754 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] session_stats => 7 rows in 117ms (sql:19ms, duck:98ms)
2026-05-05 09:38:35.001 [INFO ] [Collector]   [plvs-cube-sql03.roshen.trade] memory_pressure_events => 1 rows in 153ms (sql:25ms, duck:83ms)
2026-05-05 09:46:55.204 [ERROR] [ArchiveService] Failed to compact 202604/query_snapshots (15 files) | DuckDBException: Out of Memory Error: failed to pin block of size 256.0 KiB (3.7 GiB/3.7 GiB used)

Possible solutions:
* Reducing the number of threads (SET threads=X)
* Disabling insertion-order preservation (SET preserve_insertion_order=false)
* Increasing the memory limit (SET memory_limit='...GB')

See also https://duckdb.org/docs/stable/guides/performance/how_to_tune_workloads
2026-05-05 09:46:55.529 [ERROR] [ArchiveService] Stack:    at DuckDB.NET.Data.PreparedStatement.PreparedStatement.PrepareMultiple(DuckDBNativeConnection connection, String query, DuckDBParameterCollection parameters, Boolean useStreamingMode)+MoveNext()
   at DuckDB.NET.Data.DuckDBCommand.ExecuteNonQuery()
   at PerformanceMonitorLite.Services.ArchiveService.CompactParquetFiles()

Screenshots

No response

Additional Context

No response

[erikdarlingdata]

Thanks for the detailed report and the log output — that compaction stack trace was the key piece.

Root cause: the in-memory DuckDB connection used for parquet compaction had a 4 GB memory_limit pragma but no temp_directory set, so when the cap was hit DuckDB had nowhere to spill and OOM'd immediately. Setting temp_directory (to a duckdb_tmp/ folder co-located with the archive) lets DuckDB spill to disk under memory pressure, which is what the cap was meant to enable in the first place.

Verified end-to-end here on a 4-server setup under HammerDB load:

• First 512 MB reset → 26 single-file groups compacted, no errors
• Second 512 MB reset → 21 groups merged with 2 source files each (the multi-file pair-merge path that was OOM'ing for you), completed in ~3.5s, no OOM, spill dir cleaned up automatically

Fix is in #935 (merged to dev). Could you grab the next nightly build once it's published and confirm the compaction errors clear up on your 4-server environment? Want to make sure the fix holds on real-world data shapes, not just synthetic load.

Appreciate you sticking with this through both #758 and #933 — your reports drove two real fixes.

[000al000]

Tested with Nightly Build (2.10.0-nightly.20260506)
As i undestand it included #935

In process RAM consummption rised up to more that 4GB , disk usage rised up to more that 4GB

Problem exists

2026-05-07 10:01:00.555 [INFO ] [CollectionBackgroundService] Database size (591 MB) exceeds 512 MB — archiving all data and resetting database
2026-05-07 10:01:00.560 [INFO ] [ArchiveService] Archiving ALL data to Parquet (prefix: 20260507_0701) and resetting database
2026-05-07 10:01:00.736 [INFO ] [ArchiveService] Archived 26264 rows from wait_stats
2026-05-07 10:01:01.231 [INFO ] [ArchiveService] Archived 29800 rows from query_stats
2026-05-07 10:01:01.257 [INFO ] [ArchiveService] Archived 5125 rows from procedure_stats
2026-05-07 10:01:01.315 [INFO ] [ArchiveService] Archived 21087 rows from query_store_stats
2026-05-07 10:01:02.231 [INFO ] [ArchiveService] Archived 604 rows from query_snapshots
2026-05-07 10:01:02.237 [INFO ] [ArchiveService] Archived 1565 rows from cpu_utilization_stats
2026-05-07 10:01:02.249 [INFO ] [ArchiveService] Archived 4187 rows from file_io_stats
2026-05-07 10:01:02.253 [INFO ] [ArchiveService] Archived 151 rows from memory_stats
2026-05-07 10:01:02.257 [INFO ] [ArchiveService] Archived 1400 rows from memory_clerks
2026-05-07 10:01:02.261 [INFO ] [ArchiveService] Archived 2588 rows from memory_pressure_events
2026-05-07 10:01:02.265 [INFO ] [ArchiveService] Archived 188 rows from tempdb_stats
2026-05-07 10:01:02.291 [INFO ] [ArchiveService] Archived 36856 rows from perfmon_stats
2026-05-07 10:01:02.308 [INFO ] [ArchiveService] Archived 709 rows from blocked_process_reports
2026-05-07 10:01:02.312 [INFO ] [ArchiveService] Archived 376 rows from memory_grant_stats
2026-05-07 10:01:02.320 [INFO ] [ArchiveService] Archived 3137 rows from waiting_tasks
2026-05-07 10:01:02.324 [INFO ] [ArchiveService] Archived 15 rows from running_jobs
2026-05-07 10:01:02.331 [INFO ] [ArchiveService] Archived 1079 rows from database_size_stats
2026-05-07 10:01:02.336 [INFO ] [ArchiveService] Archived 5 rows from server_properties
2026-05-07 10:01:02.340 [INFO ] [ArchiveService] Archived 659 rows from session_stats
2026-05-07 10:01:02.344 [INFO ] [ArchiveService] Archived 430 rows from server_config
2026-05-07 10:01:02.349 [INFO ] [ArchiveService] Archived 29 rows from database_config
2026-05-07 10:01:02.353 [INFO ] [ArchiveService] Archived 632 rows from database_scoped_config
2026-05-07 10:01:02.356 [INFO ] [ArchiveService] Archived 3 rows from trace_flags
2026-05-07 10:01:02.360 [INFO ] [ArchiveService] Archived 73 rows from config_alert_log
2026-05-07 10:01:02.365 [INFO ] [ArchiveService] Archived 2932 rows from collection_log
2026-05-07 10:01:02.405 [INFO ] [ArchiveService] Compacting parquet files into monthly archives
2026-05-07 10:05:51.489 [ERROR] [ArchiveService] Failed to compact 202604/query_snapshots (15 files) | DuckDBException: Out of Memory Error: failed to pin block of size 256.0 KiB (3.7 GiB/3.7 GiB used)

Possible solutions:

• Reducing the number of threads (SET threads=X)
• Disabling insertion-order preservation (SET preserve_insertion_order=false)
• Increasing the memory limit (SET memory_limit='...GB')

See also https://duckdb.org/docs/stable/guides/performance/how_to_tune_workloads
2026-05-07 10:05:51.555 [ERROR] [ArchiveService] Stack: at DuckDB.NET.Data.PreparedStatement.PreparedStatement.PrepareMultiple(DuckDBNativeConnection connection, String query, DuckDBParameterCollection parameters, Boolean useStreamingMode)+MoveNext()
at DuckDB.NET.Data.DuckDBCommand.ExecuteNonQuery()
at PerformanceMonitorLite.Services.ArchiveService.CompactParquetFiles() in D:\a\PerformanceMonitor\PerformanceMonitor\Lite\Services\ArchiveService.cs:line 426

[erikdarlingdata]

Followup fix in #942 (merged to dev, will be in the next nightly).

Tuning the compaction connection itself — the prior fix gave DuckDB somewhere to spill, but the working set was still climbing fast enough to blow past the 4 GB cap before spill caught up. Three knobs were doing it together: memory_limit too high (DuckDB held off spilling), threads at default-N-cores (per-thread row-group buffers multiplied), and ROW_GROUP_SIZE 122880 buffering up to 122k wide-VARCHAR rows per group.

The new tuning is memory_limit = 1GB, threads = 2, ROW_GROUP_SIZE = 8192 (compaction connections only — your collectors are unaffected).

Validated on a real local archive of similar shape (202604_query_stats.parquet, 1.7M rows): peak working set drops 1236 MB → 166 MB (87% reduction) at a 31% wall-time cost. The key behavior is that memory now plateaus during the merge instead of growing with row count — that's what makes it safe at your data volumes.

I also added a standalone reproducer at tools/CompactionRepro so we can stress-test compaction outside the app. If you want to run it against your own archive files before the nightly drops:

dotnet run --project tools/CompactionRepro -c Release -- \
  --source-file "%LOCALAPPDATA%\PerformanceMonitorLite\archive\202604_query_snapshots.parquet" \
  --memory-limit 1GB --threads 2 --row-group-size 8192

Once the nightly drops, please confirm the OOM clears on your 4-server setup. Look for Parquet compaction complete: merged ... without any Failed to compact errors.