Explore native query plan rendering

[erikdarlingdata]

Background

Currently, query plans can only be downloaded as .sqlplan files and opened in SSMS or another external tool. It would be valuable to render execution plans natively inside the Lite app so users can inspect plans without leaving the tool.

Showplan XML Schema

Microsoft's execution plan XML schema is well-documented:
https://schemas.microsoft.com/sqlserver/2004/07/showplan/

The schema defines the full structure of graphical execution plans including:

• RelOp elements (operators) with physical/logical operation types
• Cost estimates (EstimatedTotalSubtreeCost, EstimateRows, EstimateIO, EstimateCPU)
• Operator-specific details (seeks, scans, joins, sorts, spools, etc.)
• Parallelism info (actual/estimated DOP, thread distribution)
• Memory grant info, warnings, missing indexes
• Runtime stats when available (actual rows, executions, memory fractions)

Exploration Areas

1. Tree view — simplest starting point. Parse the XML into a hierarchical tree showing operator names, costs, and row estimates. Similar to the text plan output but navigable.

2. Graphical plan — WPF canvas rendering operators as nodes with connecting arrows (right-to-left flow). Arrow thickness proportional to row counts. This is what SSMS does.

3. Warnings and missing indexes — highlight plan warnings (implicit conversions, spills, no join predicate) and surface missing index recommendations prominently.

4. Plan comparison — side-by-side rendering of two plans for the same query hash to spot regressions.

Prior Art

• SQL Sentry Plan Explorer (commercial, WPF-based)
• Brent Ozar's PasteThePlan (web-based)
• The showplan XSD itself is the canonical reference for parsing

Notes

This is exploratory — the scope ranges from a simple tree view (days of work) to a full graphical renderer (weeks). Filing to track interest and gather requirements. Relates to #219 (DuckDB-first plan download) since having plans readily available in DuckDB makes in-app rendering more natural.

[erikdarlingdata]

Refined Scope

Updated: applies to both Dashboard and Lite, not Lite-only.

Two Parts

Part A: Native Plan Rendering
Parse showplan XML and render it in-app instead of requiring SSMS. Starting point is a tree view — operator hierarchy with costs, row estimates, and warnings. Graphical node layout (SSMS-style canvas) is a stretch goal.

Key things to surface prominently:

• Operator tree with physical/logical operation names
• Cost percentages (relative to batch)
• Estimated vs actual row counts (when actual plan available)
• Warnings: implicit conversions, spills, no join predicate, residual predicates
• Missing index recommendations
• Memory grant info (requested vs used vs ideal)
• Parallelism info (DOP, thread distribution)

Both apps already have plan XML available:

• Dashboard: estimated plans from report.query_snapshots (sp_WhoIsActive), live plans from dm_exec_query_statistics_xml (Current Active Queries tab)
• Lite: estimated + live plans stored in DuckDB query_snapshots table, also available from Live Snapshot

Part B: "Get Actual Plan" Execution (tie-in)
Add a button on query rows that re-executes the captured query against the server using the established connection to get an actual execution plan with runtime stats. The query repro/parameterization logic will be prepared separately.

Flow:

1. User clicks "Get Actual Plan" on a query row
2. Confirmation dialog: "You are about to execute this query against [ServerName]. Make sure you understand what the query does before proceeding." with OK/Cancel
3. On OK: open connection to the correct database, SET STATISTICS XML ON, execute the query, capture the plan XML from the result sets, discard all data result sets
4. Feed the captured XML into the plan renderer (Part A)

Estimated plans are already available — this specifically adds actual plans with real row counts, spills, memory grants, etc.

Implementation Approach (Part A)

Phase 1 — Tree View

• Parse <ShowPlanXML> → <RelOp> hierarchy
• WPF TreeView or custom ItemsControl
• Each node shows: operator icon/name, cost %, estimated rows, actual rows (if present)
• Expandable detail panel per node: predicates, seek keys, output columns
• Warnings highlighted in orange/red
• Missing indexes shown in a summary banner at the top

Phase 2 — Graphical View (stretch)

• WPF Canvas with operator nodes as styled borders
• Right-to-left flow layout (like SSMS)
• Connecting arrows with thickness proportional to row counts
• Zoom/pan support
• Significantly more complex — evaluate after Phase 1 ships

Open Questions

1. Should the plan viewer be a modal window, a new tab, or an inline expander on the query row?
2. For "Get Actual Plan" — should there be a configurable query timeout, or rely entirely on the user's judgment after the warning?

[erikdarlingdata]

Updated Design: Graphical Plan Viewer

Based on discussion — the plan viewer must be graphical from the start. Tree views don't match how users read execution plans. Reference implementation: html-query-plan (TypeScript/XSLT, well-structured for porting).

Viewer Placement: Dedicated "Plan Viewer" Tab

A new top-level tab in both Dashboard and Lite, acting as a scratch space for plan analysis.

Why not inline or modal:

• Graphical plans need horizontal scroll + zoom/pan — inline expanders are too cramped
• Modal windows block interaction with query grids, preventing cross-reference
• A persistent tab lets users keep a plan open while navigating other tabs

How it works:

1. Right-click a query row → "View Estimated Plan" / "View Live Plan" / "Get Actual Plan"
2. Plan Viewer tab auto-selects, plan renders graphically
3. Multiple plans can accumulate as sub-tabs or a history list within the viewer
4. Natural path to side-by-side plan comparison later

Graphical Rendering Architecture

Layout algorithm (porting from html-query-plan):

• Right-to-left operator flow (parent on right, children on left) — matches SSMS convention
• WPF Canvas with operator nodes as styled Border/StackPanel elements
• Connecting arrows as Polyline/Path shapes
• Arrow thickness: max(2, min(floor(ln(rows)), 12)) pixels — logarithmic scaling
• Multi-child lines stack with symmetric offsets to avoid overlap
• Bend points in arrow routing to prevent node crossing

Per-node display:

• Operator icon (graphical asset — TBD, will source these separately)
• Physical operation name (bold)
• Logical operation name (if different from physical)
• Object name (table/index, truncated)
• Cost percentage (color-coded — orange/red for expensive)
• Icon overlays for: parallelism, batch mode, warnings

Tooltip on hover (full detail):

• Estimated vs actual row counts
• Estimated vs actual executions
• Cost breakdown: I/O, CPU, subtree cost
• Memory grant info (requested/used/ideal)
• Parallelism: DOP, thread distribution
• Seek predicates, output columns
• Spill counts, rebinds/rewinds
• Node ID, statement ID

Top-level features:

• Missing index recommendations banner (above the plan)
• Plan warnings summary (spills, implicit conversions, no join predicate)
• Statement text display
• Zoom/pan controls (scroll wheel + drag, or slider)
• "Save as .sqlplan" button (export the raw XML)

"Get Actual Plan" Execution Flow

1. User right-clicks a query row → "Get Actual Plan"
2. Confirmation dialog: "You are about to execute this query against [ServerName]. Make sure you understand what the query does before proceeding." — OK / Cancel
3. On OK: open connection to correct database, SET STATISTICS XML ON, execute query
4. Cancel button available during execution (via CancellationTokenSource on the SqlCommand)
5. Capture plan XML from result sets, discard all data rows
6. Render in Plan Viewer tab

Query repro/parameterization logic will be handled separately.

Operator Icon Mapping

Priority chain for determining which icon to display (from html-query-plan):

1. Parallelism → use logical operation subtype
2. Cursor → use actual cursor type
3. IndexScan with Lookup attribute → "KeyLookup"
4. ColumnStore variants (Scan, Insert, Update, etc.)
5. Physical operation name (spaces removed)
6. Statement type (SELECT, INSERT, etc.)
7. Fallback: generic operator icon

Icon assets: Will be sourced separately — need the standard SQL Server operator icon set.

Implementation Phases

Phase 1: Core renderer + existing plans

• XML parser: ShowPlanXML → operator tree model (C# classes)
• Layout engine: measure nodes → position right-to-left → route arrows
• WPF Canvas rendering with zoom/pan
• Tooltips with full operator detail
• Wire up "View Plan" context menu on existing plan columns (estimated + live plans already captured)
• Plan Viewer tab in both apps

Phase 2: Actual plan execution

• "Get Actual Plan" button with confirmation dialog + cancel
• SET STATISTICS XML ON execution, result discard, XML capture
• Query repro logic (separate prep)

Phase 3: Polish

• Missing index banner, warnings summary
• Plan comparison (side-by-side)
• Plan history within the viewer tab

[erikdarlingdata]

Icon Assets: Solved

Microsoft's vscode-mssql extension (MIT-licensed) contains 113 PNG operator icons at:

/extensions/mssql/src/reactviews/pages/ExecutionPlan/icons/

Complete coverage: index seeks/scans/inserts/deletes/updates, all join types, aggregates, sorts, spools, filters, unions, remote ops, columnstore variants, PDW/parallel ops, and catch-all fallbacks. Plus ~45 SVG UI controls (zoom, expand/collapse, etc.).

Icon mapping is in queryPlanSetup.ts — kebab-case filenames to camelCase properties, 147 mapped entries including aliases.

Rendering Reference: azdataGraph

The extension's graph rendering uses azdataGraph (Apache 2.0), Microsoft's custom graph library built on mxGraph. Key file: src/js/azdata/azdataQueryPlan.js (~800 lines).

Layout constants:

• NODE_HEIGHT: 105px
• STANDARD_NODE_DISTANCE: 173px
• CELL_WIDTH/HEIGHT: 80x80px

Two-phase custom positioning:

• X-axis: setNodeXPositionRecursive() — distributes by tree depth
• Y-axis: setNodeYPositionRecursive() — prevents node overlap
• Edges: ElbowConnector with weighted hierarchical layout

WPF Implementation Plan

1. Copy 113 PNG icons into Resources/PlanIcons/ (both Dashboard and Lite)
2. Port layout algorithm from azdataQueryPlan.js to C# — the math is straightforward tree positioning
3. Render on WPF Canvas: Border/Image elements for operator nodes, Path shapes for connecting arrows
4. Tooltips with full operator detail (costs, rows, predicates, warnings)
5. ScrollViewer with zoom/pan for large plans
6. Missing index banner + warnings summary above the plan
7. Add MIT attribution (Microsoft, vscode-mssql) to About dialog and LICENSE file

License Compliance

Both repos are permissive open source:

• vscode-mssql: MIT (Microsoft, 2016)
• azdataGraph: Apache 2.0

Requirements: include original copyright notice + license text, document the source. Already have a THIRD-PARTY-NOTICES pattern we can follow.

[erikdarlingdata]

Phase 1 Complete — Native Graphical Plan Viewer

Both Dashboard and Lite now have a Plan Viewer tab with graphical execution plan rendering:

• ShowPlanXML parser — LINQ to XML, handles estimated + actual plans, extracts costs/warnings/missing indexes
• SSMS-style layout — left-to-right operator graph with first-child alignment (horizontal spine)
• 113 operator icons — from vscode-mssql (MIT), covers all standard + legacy operators
• Context menu integration — "View Estimated Plan" on all query grids, fetches plan on-demand when needed
• Rich tooltips — operator costs, estimated/actual rows, I/O, predicates, warnings
• Missing index banner — amber banner with CREATE INDEX suggestions
• Warnings banner — summarizes plan warnings (spills, implicit conversions, no join predicate, etc.)
• Zoom/pan — Ctrl+scroll zoom, fit-to-view, save .sqlplan export
• Synthetic statement node — SELECT/INSERT/UPDATE/DELETE root node matches SSMS display

Phase 2 — Future Enhancements

• "View Actual Plan" execution — execute query with SET STATISTICS XML ON, confirmation dialog, cancel button, discard results
• Right-click operator properties — SSMS-style properties pane showing full operator details (not just tooltip)
• Full object names — display complete schema.table.index without truncation
• Operator timing for actual plans — show both duration and CPU time per operator
• SQL Sentry-style enhancements — rows modified count, read/write breakdowns, operator-level I/O summary
• Tooltip improvements — richer formatting, expand/collapse sections
• Plan comparison — side-by-side rendering of two plans
• Plan history — track plans for the same query hash over time

[erikdarlingdata]

Implemented in PR #243. Full native graphical query plan viewer with XSD v1.599 parser coverage, 11 analysis rules, tabbed plans, actual execution plan support, operator icons, and SSMS parity.