Performance Monitor v2.2.0 — Release Notes

Release date: 2026-03-11
Previous release: v2.1.0 (2026-03-04)

LOB Compression — 19× Storage Reduction

The three largest collection tables in Dashboard — query_stats, query_store_data, and procedure_stats — store query text and execution plan XML as nvarchar(max). On a busy instance, these tables dominate the database footprint. A production server collecting from 4 instances for two weeks had 597 MB of raw text across these tables.

v2.2.0 migrates these columns to varbinary(max) with SQL Server's built-in COMPRESS() function (gzip). The upgrade performs an atomic table swap per table: create new schema → migrate data with compression → rename. A row_hash column (binary(32), computed via HASHBYTES('SHA2_256', ...)) is added to each table for deduplication, preventing the same query from being stored repeatedly across collection cycles.

Measured Results

Query text and plan XML compress extremely well because they're highly repetitive — the same SELECT statement structure appears thousands of times with slight parameter variations. The COMPRESS() approach works on Standard Edition (unlike page/row compression which was Enterprise-only before 2016 SP1), requires no application changes beyond wrapping reads in DECOMPRESS(), and is completely transparent to the Dashboard UI.

Upgrade Experience

The migration runs automatically via the CLI or GUI installer. Each table swap takes a few minutes depending on table size. The upgrade scripts are fully idempotent — if the columns are already varbinary, the script prints a skip message and exits. No downtime required; collectors continue running during migration since the swap is atomic.

Deduplication Payoff

The row_hash column eliminates a long-standing data growth problem. Previously, every collection cycle could re-insert the same query text if it was still in the plan cache. With hash-based deduplication, a query that hasn't changed between cycles is skipped. Combined with compression, the steady-state database size for a typical 4-server deployment dropped from multiple gigabytes to under 200 MB.

FinOps Monitoring Tab

v2.2.0 adds a dedicated FinOps tab to both Dashboard and Lite with eight panels for cost optimization and resource utilization analysis. This isn't a single metric — it's a full operational efficiency toolkit built on data the collectors already gather.

Utilization & Provisioning

The Utilization panel aggregates CPU and memory metrics into a provisioning classification:

Memory ratio is target memory used / target memory allocated — a server using 12 GB of a 64 GB target is at 0.19, clearly over-provisioned on memory.

A 7-day provisioning trend shows daily classification history, so you can distinguish "consistently over-provisioned" from "spiked once on Tuesday."

Database Resources

Per-database workload breakdown: CPU time, logical/physical reads and writes, execution count, I/O stall time. Each database gets a CPU share and I/O share percentage. This answers "which database is actually consuming this server's resources" — essential for chargeback conversations and consolidation planning.

Storage Growth

7-day and 30-day growth analysis per database. Shows absolute growth in MB, daily growth rate, and growth percentage. A database growing 500 MB/day for 30 days is a different conversation than one that spiked once. Both show up in file size views, but only the trend tells the story.

Idle Database Detection

Databases with zero executions in the configured lookback window. Includes database size so you can prioritize — a 50 GB idle database is worth investigating, a 10 MB one isn't. Shows last known execution time when available.

Index Analysis with sp_IndexCleanup

Integration with the DarlingData sp_IndexCleanup stored procedure. Checks if the proc exists on the target server, then executes it to produce:

• Summary view: Per-database rollup of total indexes, removable count, mergeable count, compressible count, space savings in GB, and percentage reduction.
• Detail view: Per-index recommendations with generated DROP/MERGE scripts, consolidation rules, size before/after, and usage statistics (reads, writes, lock waits, latch waits).

The summary answers "how much can I save?" The detail provides the runnable scripts to actually do it.

Application Connections

Connected application breakdown with average and max connection counts. Useful for identifying connection pool leaks or applications holding excessive connections.

Optimization Panel

Four sub-sections:

• Wait Categories: Categorized wait stats with percentages and top wait type per category
• Expensive Queries: Top queries by CPU with full query text
• TempDB Pressure: User object reserved/peak, internal object usage, and warnings
• Memory Grant Efficiency: Daily breakdown of granted vs used memory, efficiency percentage, peak grants, and timeout/forced grant counts

DuckDB 1.5.0 + Parquet Compaction (Lite)

Lite's local DuckDB database had two compounding performance problems. The archive system produced one parquet file per table per archive cycle — with 25 tables and the archive-and-reset cycle firing every ~75 minutes, this generated 525 new files daily. After two weeks, the archive directory contained 2,611 parquet files. Every view query paid per-file metadata overhead on a glob scan.

DuckDB 1.4.4's write path made this worse. INSERT performance degraded linearly with table size (33× slower at 667 MB vs empty), and deleted blocks couldn't be reused, forcing the frequent archive-and-reset cycles that produced all those files.

DuckDB 1.5.0

Updated from 1.4.4 to 1.5.0 (released 2026-03-10):

• Non-blocking checkpointing — WAL-to-disk checkpoints no longer stall concurrent readers. Eliminated intermittent "Reached the end of the file" errors and read latency spikes during checkpoint operations.
• Free block reuse — deleted blocks are marked free and reused for new INSERTs. The database file stabilizes at a steady-state size instead of growing monotonically.
• Storage format v67 → v68 — upgrades transparently on first open. Parquet archives unaffected.

Under identical workload (5 SQL Servers, periodic TPC-C load):

• 1.4.4: Database grew to 512 MB every ~75 minutes, triggering 21 archive-and-reset cycles per day
• 1.5.0: Database stabilized at ~424 MB with zero resets in 6+ hours of monitoring

Automatic Parquet Compaction

New CompactParquetFiles() method runs after every archive cycle. Groups all parquet files by (month, table), merges each group into a single YYYYMM_tablename.parquet file using an in-memory DuckDB connection (no contention with the main database). Handles schema evolution across archive generations via union_by_name=true. Strips dead columns during compaction — the query_plan_text column in query_store_stats was NULL in new data but old archives still contained ~20 KB of plan XML per row.

Steady-state file count: ~75 files (25 tables × 3 months). Down from 2,611.

Retention switched from 90-day file-age deletion to 3-month calendar-month deletion, aligned with the monthly compacted filename format.

Performance Results

The method profiler has a 500ms threshold. After 75 minutes of runtime, Lite stopped logging RefreshAllDataAsync entirely — every refresh cycle completing in under half a second.

Compound Effect

No single change achieved sub-500ms:

1. Visible-tab-only refresh (#510): 6-13s → 1.7-4.3s
2. Parquet compaction: 1.7-4.3s → 1.0-1.5s
3. DuckDB 1.5.0 non-blocking checkpoint: eliminated latency spikes
4. DuckDB 1.5.0 free block reuse: eliminated the archive churn that generated files
5. Together: 6-13s → < 500ms (13-26× improvement)

Upgrade Path

No user action required. DuckDB storage format upgrades on first launch. First archive cycle compacts existing files (may take 30-60 seconds for large archive directories). Subsequent cycles are near-instant.

SignPath Code Signing

Starting with v2.2.0, all release binaries (Dashboard, Lite, and Installers) are digitally signed via SignPath, an OSS-friendly code signing service. This means:

• Windows SmartScreen no longer flags the executables as unrecognized
• Corporate environments with signature-required policies can deploy without exception requests
• The installer MSI chain of trust is verifiable end-to-end

SignPath was approved under their Free Open Source Software program. Signing is integrated into the GitHub Actions release workflow — when a version tag is pushed, the build artifacts are submitted to SignPath for signing before the GitHub release is published.

Other Notable Changes

ReadOnlyIntent Connection Option (Lite)

Lite connections can now set ApplicationIntent=ReadOnly, which enables read routing on Always On Availability Group secondaries. For AG deployments, this means Lite's monitoring queries are automatically routed to a readable secondary, offloading the primary.

UI Responsiveness Overhaul (#510)

The foundation for the performance story above. Both Dashboard and Lite were refreshing all data grids on every timer tick regardless of which tab was visible. v2.2.0 introduced:

• Visible-tab-only refresh — only the active tab's data is refreshed each cycle
• Sub-tab awareness — nested tabs within a server tab track visibility independently
• Query Store collector optimization — NULL plan XML writes (was writing full XML to DuckDB unnecessarily) and LOOP JOIN hint on the SQL Server query
• Tab switch freeze fix — _isRefreshing guard prevents timer ticks from competing with manual tab switches for the DuckDB connection

First-Collection Spike Fix (#482)

Delta-based collectors (PerfMon counters, wait stats, file I/O, memory grants, query stats, procedure stats) previously showed a massive spike on their first data point because the first cumulative value was treated as a delta instead of a baseline. Charts no longer show a misleading spike at startup — the first value is consumed silently as the baseline.

Execution Plan Analyzer Sync

All 30 PlanAnalyzer rules synced across Dashboard, Lite, and Performance Studio. Notable fixes:

• Rule 5 (missing index): improved message format and seek predicate parsing
• Rule 8 (key lookup): now matches RID Lookup PhysicalOp label
• Rule 22 (table variable): no longer warns on modification operators (INSERT/UPDATE/DELETE on table variables is expected)
• SSMS-parity edge tooltips on plan viewer operator connections
• ManyToMany indicator always shown for merge join operators

Cloud Support Fixes

Multiple collector fixes for Azure SQL DB, Azure MI, and AWS RDS:

• Query Store and database-scoped config collectors no longer fail on Azure SQL DB
• Int16 cast error resolved in database_size_stats for Azure compatibility levels
• FinOps tab server dropdown properly updates when server list changes

DuckDB 1.5.0 Upgrade + Parquet Compaction — Way More Details

PR: #516 (merged to dev 2026-03-11)
Files changed: PerformanceMonitorLite.csproj, ArchiveService.cs, CollectionBackgroundService.cs, RetentionService.cs

The Problem

Lite's local DuckDB database had two compounding performance issues:

1. Archive file proliferation. Each archive cycle (triggered every ~75 minutes at 512MB) produced one parquet file per table — 25 tables × 21 resets/day = 525 new files daily. After two weeks of continuous monitoring, the archive directory contained 2,611 parquet files. Every DuckDB view that reads from archive uses read_parquet('*_tablename.parquet'), which must open and read metadata from every matching file on each query. This glob overhead dominated read performance.

2. DuckDB 1.4.4 write path degradation. INSERT performance degraded linearly with table size — benchmarked at 33× slower at 667MB vs empty tables. The database file grew monotonically because 1.4.4 cannot reuse free blocks from deleted rows, forcing the archive-and-reset cycle to fire every ~75 minutes. Each reset produced another batch of parquet files, feeding problem #1.

The combined effect: Lite's UI refresh (RefreshAllDataAsync) took 6-13 seconds before the visible-tab-only optimization (#510), and even after that fix, parquet scan overhead kept certain views (especially v_query_store_stats and v_wait_stats) at 1.7-4.3 seconds per query.

What Changed

DuckDB 1.5.0 Upgrade

Updated DuckDB.NET.Data and DuckDB.NET.Bindings.Full from 1.4.4 to 1.5.0.

Key improvements relevant to Lite:

• Non-blocking checkpointing. WAL-to-disk checkpointing no longer blocks concurrent readers. Previously, checkpoint operations could stall UI queries mid-flight, causing "Reached the end of the file" errors and inconsistent read latencies.
• Free block reuse. When rows are deleted (during archive cycles), DuckDB 1.5.0 marks those blocks as free and reuses them for new INSERTs. The database file no longer grows monotonically — it stabilizes at a steady-state size determined by the amount of hot data, not the cumulative write history.
• Storage format v67 → v68. The upgrade happens transparently on first read-write open. Existing parquet archive files are unaffected (parquet is a standalone format, not tied to DuckDB's internal storage version).

Measured impact: Under identical workload (5 servers, periodic TPC-C on SQL2022):

• DuckDB 1.4.4: Database grew to 512MB every ~75 minutes, triggering archive-and-reset 21 times/day. INSERT latency degraded as the file grew. Timer ticks showed 2.3-3.8s with a steady upward trend.
• DuckDB 1.5.0: Database stabilized at ~424MB (444MB file with 353MB of reusable free blocks from pre-upgrade data). Zero archive-and-reset cycles in 6+ hours of monitoring. Timer ticks stable at 1.0-1.5s with no degradation trend.

Automatic Parquet Compaction

New CompactParquetFiles() method in ArchiveService runs after every archive cycle (both hourly ArchiveOldDataAsync and size-triggered ArchiveAllAndResetAsync). It:

1. Groups all parquet files in the archive directory by (month, table) — recognizing five naming formats:

   • YYYYMMDD_HHMM_tablename.parquet (per-cycle, the most common)
   • YYYYMMDD_tablename.parquet (consolidated daily, from earlier manual work)
   • YYYY-MM_tablename.parquet (legacy monthly format)
   • all_tablename.parquet (one-time manual consolidation)
   • YYYYMM_tablename.parquet (monthly — the target format)

2. Merges each group into a single monthly file (YYYYMM_tablename.parquet) using read_parquet([...], union_by_name=true) to handle schema evolution across archive generations.

3. Strips dead columns during compaction. Specifically, query_plan_text is excluded from query_store_stats files — the collector stopped populating this column (writes NULL) but old archives still contained ~20KB of plan XML per row. Stripping it reduced compaction time from 8.9s to 0.46s and file size from 22.6MB to 7.9MB for that table alone.

4. Uses an in-memory DuckDB connection (DataSource=:memory:) for the merge, avoiding any contention with Lite's main database connection. Only touches filesystem files.

5. Atomic writes: Writes to a .tmp file, deletes originals, then renames .tmp to final — no partial files if the process crashes mid-compaction.

Steady-state file count: ~75 files (25 archivable tables × 3 months of retention). Down from 2,611.

Column exclusion safety: The compaction inspects the actual schema of source files before applying EXCLUDE clauses. If a column was already stripped in a previous compaction cycle, the EXCLUDE is skipped — preventing the "column not found" error that caused data loss during development.

Monthly Retention

RetentionService changed from 90-day file-age deletion to 3-month calendar deletion:

• Old: CleanupOldArchives(retentionDays: 90) — parsed YYYYMMDD timestamps from filenames, deleted files older than 90 days. Did not understand monthly compacted filenames.
• New: CleanupOldArchives(retentionMonths: 3) — recognizes all three active filename formats (YYYYMM_, YYYYMMDD_, YYYY-MM_), uses DateTime.AddMonths(-3) for the cutoff. Clean calendar-month boundaries.

CollectionBackgroundService updated to call with the new parameter signature.

Performance Results

Parquet Read Performance (EXPLAIN ANALYZE)

Query: SELECT * FROM v_wait_stats WHERE server_id = 1 AND collection_time >= NOW() - INTERVAL '4 hours' ORDER BY collection_time DESC LIMIT 1000

63× faster on the glob-heavy path.

Lite UI Refresh (RefreshAllDataAsync)

Monitored continuously over 60+ minutes post-change:

The method profiler has a 500ms threshold. After 75 minutes, Lite stopped logging RefreshAllDataAsync at all — meaning every refresh cycle completed in under 500ms. This is a 13-26× improvement from the pre-optimization baseline.

Slow Query Log

Zero new slow query entries for 6+ hours after compaction. The only slow queries logged were from the pre-compaction state (a 4.3s v_query_store_stats scan when the archive was still fragmented).

Database Size Stability

Upgrade Path

• No user action required. The DuckDB storage format upgrade (v67 → v68) happens automatically on first launch. Existing parquet archive files are fully compatible — parquet is a standalone format.
• First archive cycle after upgrade will compact all existing per-cycle files into monthly files. Users with large archive directories (thousands of files) will see a one-time compaction pass that may take 30-60 seconds. Subsequent cycles are near-instant.
• No schema changes to the DuckDB tables or views. Archive views continue using the same read_parquet('*_tablename.parquet') glob pattern, which matches both old and new filename formats.

Lessons Learned

1. Small-file problem is real. DuckDB's read_parquet with a glob pattern pays per-file metadata overhead that scales linearly. At 2,600 files, this overhead dominated query time — the actual data scan with predicate pushdown was nearly instant, but opening 233 files to check metadata took 1.7 seconds.

2. Free block reuse changes the operating model. On 1.4.4, the archive-and-reset cycle was a necessary pressure valve — without it, INSERT performance degraded to unusable levels. On 1.5.0, the same cycle becomes a rare event because the file self-compacts. This dramatically reduces parquet file generation rate.

3. Dead columns in parquet are expensive. The query_plan_text column in query_store_stats was NULL in new data but still contained ~20KB XML blobs in old archives. DuckDB's columnar reader still had to decompress and skip these columns. Stripping them during compaction gave a 19× speedup on that specific table's compaction.

4. Compound optimizations. No single change here would have achieved sub-500ms refresh:

   • Visible-tab-only refresh (#510): 6-13s → 1.7-4.3s
   • Parquet compaction: 1.7-4.3s → 1.0-1.5s
   • DuckDB 1.5.0 non-blocking checkpoint: eliminated latency spikes from checkpoint stalls
   • DuckDB 1.5.0 free block reuse: eliminated the archive-and-reset churn that generated files
   • Together: 6-13s → <500ms (13-26× improvement)

See CHANGELOG.md for full release notes.

What's Changed

• Fix Regression - The tooltip for data points doesn't appear by @ClaudioESSilva in #411
• Plan Viewer "Key Lookup" not showing right icon/text by @ClaudioESSilva in #413
• GUI installer: log installation history after install by @erikdarlingdata in #414
• Feature/long running queries config settings by @HannahVernon in #415
• Sync plan viewer: spool labels + unmatched index detail by @erikdarlingdata in #416
• update some nuget packages and fix many warnings by @MisterZeus in #346
• Complete GeneratedRegex conversion and remove Compiled flags by @erikdarlingdata in #420
• Add permissions section to README by @erikdarlingdata in #421
• Fix system tray tooltip crash in both editions by @erikdarlingdata in #424
• Add resilience to DuckDB read lock acquisition by @erikdarlingdata in #425
• Fix RID Lookup analyzer rule after Key Lookup parser change by @erikdarlingdata in #429
• Fix incorrect table name in Data Retention README section by @erikdarlingdata in #428
• LOB compression + deduplication for query stats tables (#419) by @erikdarlingdata in #433
• Add uninstall option to CLI and GUI installers by @erikdarlingdata in #432
• Add RESTORING database filter to waiting_tasks collector (#430) by @erikdarlingdata in #434
• Restore commercial support tiers to README by @erikdarlingdata in #437
• Add wait stats query drill-down (#372) by @erikdarlingdata in #442
• Add version bump check for PRs to main by @erikdarlingdata in #435
• Fix poison wait false positives and alert log parsing (#445) by @erikdarlingdata in #447
• Fix poison wait false positives and alert log parsing (#445) by @erikdarlingdata in #448
• Revert accidental squash merge of dev content to main by @erikdarlingdata in #449
• Lite - fixed sort on columns that are formatted for seconds or milliseconds - branch fixed by @dphugo in #452
• Feature/add cooldown period setting by @HannahVernon in #444
• Feature: Add alert filter by database (from PR #427) by @erikdarlingdata in #456
• Feature/add alert filter by database by @HannahVernon in #427
• Fix remaining formatted duration columns sorting alphabetically by @erikdarlingdata in #457
• Fix nullable dereference in Lite anomalous job alert by @erikdarlingdata in #458
• Purge processed XE staging rows in retention proc by @erikdarlingdata in #461
• Fix procedure_stats plan query after compression migration by @erikdarlingdata in #462
• Add named collection presets (closes #454) by @erikdarlingdata in #463
• Add FinOps monitoring collectors and reporting views by @erikdarlingdata in #464
• Fix FinOps CI: sql_variant CONCAT and test table count by @erikdarlingdata in #465
• Fix finops_utilization_efficiency: separate window function from aggregates by @erikdarlingdata in #466
• Fix CI validation: update object checklist for presets and FinOps by @erikdarlingdata in #467
• Widen SQL Validation path filter to include .github/sql changes by @erikdarlingdata in #468
• Strip XML processing instructions from sql_command/sql_text by @erikdarlingdata in #469
• Use user locale for date/time formatting (closes #459) by @erikdarlingdata in #470
• Fix database_size_stats tinyint cast error by @erikdarlingdata in #471
• Enrich database_size_stats with drive-level volume space by @erikdarlingdata in #472
• Sync PlanAnalyzer rules from PerformanceStudio by @erikdarlingdata in #473
• FinOps estate-level tabs, session_stats collector, archive reconciliation by @erikdarlingdata in #474
• Sync Rule 5 message format and seek predicate parsing from plan-b by @erikdarlingdata in #475
• Fix schema test: update table count for session_stats by @erikdarlingdata in #476
• Port Utilization tab redesign to Dashboard, align metrics between apps by @erikdarlingdata in #478
• FinOps parity follow-up: CPU count, DB sizes filtering, sort order by @erikdarlingdata in #479
• Sync PlanAnalyzer improvements from PerformanceStudio by @erikdarlingdata in #480
• Fix Dashboard UI hang when opening tab for offline server by @erikdarlingdata in #485
• Fix PerfMon first-value spike that skews graphs by @erikdarlingdata in #483
• Add Entra ID interactive MFA authentication to installers by @erikdarlingdata in #484
• Add Storage Growth and Index Analysis to FinOps tab by @erikdarlingdata in #487
• Feature/mcp port validation by @HannahVernon in #453
• Fix/minor UI cuts by @ClaudioESSilva in #490
• Fix first-collection spike for query and procedure stats charts by @erikdarlingdata in #491
• Fix query_store and database_scoped_config collectors on Azure SQL DB by @erikdarlingdata in #497
• Fix database_size_stats Int16 cast error on Azure SQL DB by @erikdarlingdata in #498
• Fix FinOps server dropdown not updating on server add by @erikdarlingdata in #499
• v2.2.0 release prep: RDS fixes, USE removal, README sync by @erikdarlingdata in #500
• Sync PlanAnalyzer rules from Performance Studio v1.0.0 by @erikdarlingdata in #501
• Sync analyzer improvements from Performance Studio by @erikdarlingdata in #502
• Fix sp_IndexCleanup column mapping and show all columns by @erikdarlingdata in #503
• Add SSMS-parity edge tooltips and merge join ManyToMany by @erikdarlingdata in #504
• FinOps tab improvements: 6 features for v2.2.0 by @erikdarlingdata in #505
• Add compression ratio stats to 2.2.0 changelog by @erikdarlingdata in #506
• Fix ComboBox focus steal in plan viewer + DOP 2 skew threshold by @erikdarlingdata in #508
• Add performance instrumentation, caching, and parallelization by @erikdarlingdata in #509
• Add SignPath code signing to release workflow by @erikdarlingdata in #511
• Fix table variable warnings on modification operators by @erikdarlingdata in #513
• Lite UI responsiveness overhaul + Query Store collector optimization (#510) by @erikdarlingdata in #514
• Add ReadOnlyIntent connection option to Lite by @erikdarlingdata in #515
• Upgrade DuckDB to 1.5.0 + automatic parquet compaction by @erikdarlingdata in #516
• Show used vs file size in Lite status bar by @erikdarlingdata in #517
• Add Query Store collector spike diagnostic timing by @erikdarlingdata in #518
• Update changelog and README for v2.2.0 by @erikdarlingdata in #519
• Fix ReadOnlyIntent connections sharing server_id in DuckDB by @erikdarlingdata in #521
• Feature/alert muting by @HannahVernon in #512
• Add alert muting to changelog by @erikdarlingdata in #524
• Release v2.2.0 by @erikdarlingdata in #525

New Contributors

• @dphugo made their first contribution in #452

Full Changelog: v2.1.0...v2.2.0