Summary
Average duration and p95/p99 lines hide distribution shapes. A query with avg duration 200ms might actually be bimodal — 50ms 90% of the time and 2000ms 10% of the time — which is the signature of parameter sniffing. No other SQL Server monitoring tool surfaces this visually. A heatmap would make bimodal distributions, long-tail outliers, and gradual regressions immediately obvious.
What Is a Heatmap
Instead of plotting avg/max duration as lines over time, plot every observation as a colored cell in a time × duration grid:
- X-axis: time (bucketed by collection interval)
- Y-axis: duration buckets (log scale — 1ms, 10ms, 100ms, 1s, 10s, etc.)
- Color intensity: execution count in that bucket
What Patterns Look Like
- Single bright band = stable query, consistent duration
- Two distinct bright bands = bimodal execution = parameter sniffing
- Band drifting upward over time = gradual regression
- Bright band with scattered hot cells above = occasional long-tail outliers
- Sudden band shift = plan change (recompile, new index, stats update)
Where It Fits
Dashboard
The Query Performance tab has a "Performance Trends" sub-tab showing 2×2 line charts (Query Duration, Procedure Duration, Query Store Duration, Execution Counts). The heatmap could:
- Replace the Query Duration trend line chart with a heatmap
- Or be an additional sub-tab "Duration Distribution" alongside Performance Trends
Lite
The Queries tab has the same "Performance Trends" sub-tab with the same 2×2 layout. Same options apply.
In both apps, the heatmap answers a question the line charts cannot: "is this one query behaving two different ways?"
Data Source
- Plan cache queries (get_top_queries_by_cpu): have avg/min/max duration per collection snapshot — can bucket into duration ranges
- Query Store (get_query_store_top): has avg duration + execution count per interval — richer data for heatmap bucketing
- Active query snapshots: have individual elapsed times per snapshot — most granular source
Query Store is the best fit because it already stores per-interval statistics with execution counts, making the bucketing natural.
Design Notes
- Log scale on the Y-axis is important — query durations span microseconds to minutes
- Color palette: dark background with intensity ramp (dark blue → bright yellow or similar)
- ScottPlot (used in both apps) supports heatmap rendering via
ScottPlot.Plottables.Heatmap
- Clicking a hot cell could show the queries that fell in that duration bucket at that time
- This is a differentiator — parameter sniffing is uniquely painful in SQL Server and no competing tool visualizes it this way
- Applies to both Dashboard and Lite
Summary
Average duration and p95/p99 lines hide distribution shapes. A query with avg duration 200ms might actually be bimodal — 50ms 90% of the time and 2000ms 10% of the time — which is the signature of parameter sniffing. No other SQL Server monitoring tool surfaces this visually. A heatmap would make bimodal distributions, long-tail outliers, and gradual regressions immediately obvious.
What Is a Heatmap
Instead of plotting avg/max duration as lines over time, plot every observation as a colored cell in a time × duration grid:
What Patterns Look Like
Where It Fits
Dashboard
The Query Performance tab has a "Performance Trends" sub-tab showing 2×2 line charts (Query Duration, Procedure Duration, Query Store Duration, Execution Counts). The heatmap could:
Lite
The Queries tab has the same "Performance Trends" sub-tab with the same 2×2 layout. Same options apply.
In both apps, the heatmap answers a question the line charts cannot: "is this one query behaving two different ways?"
Data Source
Query Store is the best fit because it already stores per-interval statistics with execution counts, making the bucketing natural.
Design Notes
ScottPlot.Plottables.Heatmap