FinOps Roadmap: Cost Optimization Recommendations Engine

[erikdarlingdata]

Overview

The FinOps tab already collects solid foundational data — utilization scoring, idle databases, index cleanup, storage analysis, server properties, and resource consumption. The next phase is turning that raw data into actionable cost-saving recommendations that help DBAs and IT managers justify budget decisions.

This issue tracks the full roadmap from per-server optimizations through estate-wide consolidation analysis.

---

Phase 1: Per-Server Optimizations (SHIPPED)

• Enterprise feature usage audit — query sys.dm_db_persisted_sku_features per database to identify servers paying for Enterprise where no database uses Enterprise-only features
• CPU right-sizing score — compare licensed core count against sustained CPU utilization trends to flag servers where cores are significantly over-provisioned
• Memory right-sizing score — compare allocated max server memory against actual buffer pool + stolen memory usage to flag reclaimable RAM
• Unused index cost quantification — extend existing index cleanup to show wasted storage (GB), write I/O overhead, and estimated backup time impact for unused/duplicate indexes
• Compression savings estimator — flag large uncompressed heaps/indexes with estimated space savings (page vs. row compression candidates)
• Dormant database detection (enhanced) — extend existing idle database detection with estimated cost impact (backup storage, AG traffic, DBCC overhead, license implications)
• Dev/test workload detection — flag servers with production-edition licenses but development usage patterns (low connection counts, sporadic activity, database naming conventions)

Phase 2: Edition & Licensing (SHIPPED)

• Enterprise → Standard downgrade report — per-server report showing which Enterprise features are actually in use, which databases use them, and what would need to change to move to Standard
• License cost impact estimates — list pricing differential so recommendations show estimated dollar savings

Phase 3: Hardware & Maintenance Optimization (SHIPPED)

• Maintenance window efficiency — flag maintenance jobs with excessive duration relative to their historical average
• VM right-sizing recommendations — based on sustained CPU/memory trends, prescribe specific target reductions (e.g., "reduce from 32 to 16 cores based on P95 CPU of 22%")
• Storage tier optimization — flag databases with consistently low IO latency (<5ms reads) that don't need premium storage
• Reserved capacity candidates — servers with consistent, predictable utilization patterns (low coefficient of variation) that would benefit from reserved pricing over pay-as-you-go

Phase 4: High Impact Analysis (SHIPPED)

• High Impact Queries tab — 80/20 analysis across CPU, duration, reads, writes, memory, and executions. PERCENT_RANK scoring with composite impact score.

Phase 5: Test Infrastructure (SHIPPED)

• HighImpactScorer extraction — scoring logic in testable static methods
• FinOps test scenarios — 6 DuckDB-seeded scenarios for deterministic validation of recommendation engine output
• FinOps xunit tests — 16 scenario-based tests validating recommendations and scoring
• HealthCalculator + PercentRank tests — 57 unit tests with adversarial inputs, found and fixed PercentRank >1.0 bug

Phase 6: UI Polish (SHIPPED)

• Column filters on all FinOps grids — Dashboard (10 grids) and Lite Optimization (5 grids)
• Query preview with full-text tooltips — truncated in row, full text on hover
• Per-server monthly cost — budget field on server config, proportional cost attribution
• Health scores and license warnings — composite 0-100 score, Standard Edition boundary detection
• Storage Growth query fix — fallback to oldest snapshot when <7d history
• Empty grid scrollbar fix — disabled horizontal scroll on all FinOps DataGrids

---

Future (post-2.3)

These items need more design work, new collectors, or cross-server architecture changes.

• Consolidation candidate scoring — identify groups of underutilized servers whose combined workloads could fit on fewer instances. Must account for version compatibility, edition requirements, and platform (cloud vs on-prem). Cannot consolidate across major version downgrades or edition downgrades where features are in use.
• Workload complementarity analysis — find servers with complementary peak usage windows via hourly CPU time-series correlation. Natural pairs for consolidation. Same version/edition/platform constraints apply.
• Postgres migration compatibility score — feature dependency scan, T-SQL compatibility analysis. Requires: stored procedure/function text scanning.

---

Design Notes

• All recommendations are data-driven from DMVs and system catalog views we already collect
• Recommendations include confidence levels (high/medium/low) based on observation window length
• Where possible, savings are quantified as proportional share of the per-server monthly budget set by the user
• Both Lite (DuckDB) and Dashboard (SQL Server) surface recommendations
• Dollar estimates use the per-server monthly cost model — user tags each server with its budget, we show proportional attribution

Related

• Existing FinOps tab infrastructure (utilization scoring, idle DBs, index cleanup, storage analysis)
• Server properties collector (daily, 365-day retention)
• Database size stats collector (hourly, 90-day retention)
• Running jobs collector (per schedule, with avg/p95 duration tracking)
• High Impact Queries tab (query_stats-based 80/20 analysis)
• FinOps test scenarios in TestDataSeeder
• 73 xunit tests (FinOpsTests + HealthCalculatorTests)

[erikdarlingdata]

Additional FinOps Enhancements — Cost Visibility & Actionable Insights

After reviewing the current FinOps tab and thinking about what would make it genuinely useful for DBAs who need to justify spend to management, here are additional capabilities grouped by implementation effort. These build on data we already collect.

---

Tier 1: Dollar-Denominated Metrics (Low Effort, High Impact)

These are simple math on top of existing collectors — the data is already there, we just need to express it in money.

• Cost-per-query estimation — Calculate a dollar cost per query using CPU time and logical reads from dm_exec_query_stats. Use configurable rates ($/CPU-second, $/1M logical reads) with sensible defaults. Show a "Cost" column in Top Queries and Top Procedures views. Doesn't need to be precise — directionally useful is enough for budget conversations.
• Wait cost projection — Multiply cumulative wait time by a configurable hourly rate (default ~$150/hr as a reasonable loaded DBA cost). Show "Annual Wait Cost" broken down by wait category. Makes wait stats immediately meaningful to non-technical stakeholders.
• Annual compute spend estimate — Project yearly infrastructure cost per instance based on allocated CPU cores and memory. Use configurable per-instance rates, or default to published Azure/AWS retail pricing for comparable VM SKUs. Show at both instance and environment level.
• Annual storage spend estimate — Project yearly storage cost based on database sizes, backup volumes, and log file allocations. Aggregate across the estate for total storage spend visibility.
• Standard Edition license boundary detection — Flag instances running Standard Edition that exceed (or approach) Standard limits: 24 cores, 128GB RAM. These are compliance risks AND cost risks — they're paying for capacity SQL Server is silently capping.
• Composite health scores — Weighted 0-100 scores for CPU health, memory health, storage health, and I/O health per instance. Normalize existing metrics into a single number management can track over time. An "environment health score" rolls up all instances.

Tier 2: Recommendations & Projections (Medium Effort, Strong Value)

These turn raw metrics into specific actions with projected savings.

• Job schedule overlap detection — Query msdb.dbo.sysjobschedules and sysjobhistory to flag jobs with identical or near-identical start times. Overlapping jobs cause resource contention spikes that inflate infrastructure requirements. Show how staggering schedules could reduce peak resource demand.
• Savings rollup / ROI summary — Aggregate all recommendations (right-sizing, compression, unused indexes, edition downgrades, job staggering) into a single "Potential Annual Savings" number. Simple summary screen: "If you address the top 10 recommendations, estimated savings are $X/year." This is the screen that gets shown to VPs.
• Cost trend tracking — Track estimated costs over time so users can see whether their environment is getting more or less expensive. Weekly/monthly cost snapshots with trend lines. Answers "are we spending more than last quarter and why?"
• Query cost ranking with efficiency grades — Beyond raw cost, grade queries on efficiency: a query consuming $50/day that processes 10M rows efficiently is fine; a query consuming $50/day due to a missing index is waste. Combine cost with plan quality signals (scans, key lookups, implicit conversions) for a waste score.

Tier 3: Estate-Level Financial Views (Bigger Lift, Differentiating)

These provide the "CFO-ready" views that turn PerformanceMonitor from a DBA tool into something finance teams can use.

• Workload comparison — Side-by-side metrics between two time windows: before/after a deployment, week-over-week, month-over-month. The historical data already exists — this is primarily a UI exercise. Answers "did that release make things cheaper or more expensive?"
• Cost allocation by database/application — Group estimated costs by database, application tag, or team. Export-friendly (CSV/PDF) for chargeback and showback reporting. Lets organizations answer "which team's databases cost the most?"
• FinOps summary dashboard — Dedicated top-level view consolidating: total compute spend, total storage spend, total wait cost, top 5 most expensive queries, license alerts, right-sizing opportunities, and the savings rollup. One screen that tells the whole cost story.
• Capacity forecast — Project future resource needs and costs based on growth trends. "At current growth rates, this instance will need X more cores in Y months, costing $Z/year." Helps with budget planning cycles.

---

Implementation Notes

• Configurable cost rates are essential — every organization's cost structure is different. Provide reasonable defaults but let users set their own $/core, $/GB, $/hour rates. A simple settings page or config section in the Admin area.
• All dollar amounts should clearly label themselves as estimates — we're not reading invoices, we're projecting from resource consumption. Transparency about methodology builds trust.
• Tier 1 items could ship incrementally — each one is independently useful and doesn't depend on the others.
• Both Lite and Dashboard should surface these — Lite for single-server/small-shop users, Dashboard for estate-wide views. Some Tier 3 items (cost allocation, estate summary) only make sense in Dashboard.
• Export capability matters — FinOps data is useless if it can't leave the tool. CSV export at minimum for any cost-related grid or summary.

[erikdarlingdata]

Closing — all four phases shipped.