Commands / Get-sqmPlanInsights
sqmSQLTool › Commands › Get-sqmPlanInsights

Get-sqmPlanInsights Performance

Parses SQL Server ShowPlan XML (.sqlplan / .xml) and acts as an automated query-tuning consultant: cardinality-estimate mismatches, tempdb spills, SARGability violations, implicit conversions, parameter-sniffing risk, and missing-index suggestions — with a runtime-evidence severity model instead of the optimizer's own (page-weighted) cost estimates. Optionally connects live to cross-reference indexes/statistics and Query Store, and can emit a JSON bundle for the offline Plan Visualizer.

Execution Flow

START -Path supplied? throw ERROR Resolve path, load XmlDocument, detect ShowPlan namespace Batch-aware statement keys: "<batchOrdinal>.<StatementId>" for multi-batch files Statement chooser: sniffing evidence → actual elapsed time → est. subtree cost -Statement override (e.g. "2.1") picks a specific statement explicitly Runtime-evidence heuristics (no optimizer cost bias) CE severity = f(log CE ratio, row-scale CPU volume, RowsRead/ActRows waste) SARGability, implicit conversions, tempdb spills, key lookups, parallelism skew Missing-index harvest from plan XML + parameter sniffing (compiled vs runtime) -InspectDatabase & dbatools available? throw ERROR (-InspectDatabase) Connect-DbaInstance → index/stats coverage + Query Store regression detection Build $result object (CardinalityIssues, SargabilityIssues, SpillSignals, MissingIndexes, TopOperators, …) (-OutFile) ConvertTo-Json -Depth 12 → plan_yyyyMMdd_HHmmss.json bundle for the HTML Plan Visualizer Console report + return $result DONE

Synopsis

Two operating modes: offline file parsing of a saved .sqlplan/.xml plan, or telemetry & regression mode that additionally connects to the source database (via dbatools) to cross-reference existing indexes and statistics staleness, append Query Store metrics for the matching query/plan hash, and auto-generate plan-forcing/OPTIMIZE FOR mitigation scripts when a regression is found. Cardinality-estimate severity is computed entirely from runtime evidence (log-scaled CE mismatch ratio, actual rows touched, and rows-read-vs-rows-returned waste) rather than the optimizer's own page-weighted cost model — the same evidence a DBA would look for manually, just automated.

Adapted with permission from rafinnerty/dbaStuff (MIT License, © Richard Armstrong-Finnerty) — see THIRD-PARTY-NOTICES.md. Pair it with the offline, self-contained Plan Visualizer (Tools\PlanVisualizer\plan_visualizer_V4.html) — feed it a -OutFile JSON bundle for an interactive diagram with the same findings. No network calls, nothing leaves your machine. dbatools is only required for -InspectDatabase / -ServerInstance telemetry — plain offline file analysis has no dependency.

Syntax

Get-sqmPlanInsights
    [-Path] <String>                    # .sqlplan or .xml file
    [-TopOperators <Int>]                # default 15
    [-CEMismatchRatio <Double>]         # default 10
    [-CEMinRows <Double>]               # default 10
    [-LookupCallsThreshold <Double>]   # default 10000
    [-ServerInstance <String>]
    [-Database <String>]                # required with -ServerInstance / -InspectDatabase
    [-InspectDatabase]
    [-SqlCredential <PSCredential>]
    [-Statement <String>]               # e.g. "2.1" — batch-qualified override
    [-OutFile <String>]                 # writes a plan-visualizer JSON bundle
    [-ShowAllHeuristicMatches]
    [-IncludeOperatorRows]
    [-SanityCheck]
    [-DebugSargability]

Parameters

ParameterTypeDefaultDescription
-PathString(required)Path to the .sqlplan or .xml execution plan file to analyze. Positional (0).
-TopOperatorsInt15How many operators to list in the cost/CPU-volume grids.
-CEMismatchRatioDouble10Cardinality estimate mismatch ratio threshold (estimated vs. actual rows) before flagging.
-CEMinRowsDouble10Minimum row count before a CE mismatch is considered significant.
-LookupCallsThresholdDouble10000Call-volume heuristic threshold for flagging expensive key/RID lookups.
-ServerInstanceString—Target SQL Server instance for optional Query Store telemetry / database inspection.
-DatabaseString—Target database. Required together with -ServerInstance / -InspectDatabase.
-InspectDatabaseSwitchfalseConnect to the database to inspect existing indexes, statistics staleness, and missing-index coverage. Requires -ServerInstance and -Database, and dbatools.
-SqlCredentialPSCredential—SQL Server authentication credential, passed through to dbatools.
-StatementString(auto-chosen)Analyze a specific statement in a multi-statement plan instead of the automatically chosen one — use the batch-qualified key printed in the report (e.g. "1.1"). Falls back to auto-selection if not found.
-OutFileString—Emit a self-contained sqlplan-insights JSON bundle (plan XML + analysis + live-inspection data) for the HTML Plan Visualizer. Stamped with the run date/time.
-ShowAllHeuristicMatchesSwitchfalseList every matching operator for heuristic sections instead of deduplicated/grouped findings.
-IncludeOperatorRowsSwitchfalseDiagnostics: include raw per-operator row data in the result.
-SanityCheckSwitchfalseDiagnostics: run internal consistency checks against the parsed plan.
-DebugSargabilitySwitchfalseVerbose tracing of the SARGability regex detection for troubleshooting false positives/negatives.

Return Value

PropertyDescription
File / DegreeOfParallelismSource file analyzed and the chosen statement's observed DOP.
TopOperatorsMost expensive operators by estimated subtree cost and true CPU volume.
CardinalityIssuesCE mismatches with the runtime-evidence severity score and likely contributors.
SargabilityIssuesNon-SARGable predicates: implicit conversions, functions on columns, leading-wildcard LIKE.
SpillSignals / ParallelSkewSignalsTempDB spill markers and parallelism skew detected in the plan XML.
MissingIndexesMissing-index suggestions harvested from the plan XML, merged to avoid duplicates.
KeyLookups / SortOps / JoinChecks / HintSignalsExpensive key/RID lookups, large sorts, join-strategy sanity checks, and query/table hint signals.
SuggestionsActionable rewrite/design recommendations distilled from all of the above.
DbInspection / QueryStoreRegressionsLive index/statistics coverage and Query Store regression data (only with -InspectDatabase).
*Count propertiesConvenience counts (CEIssuesCount, SargIssuesCount, SpillOpsCount, MissingIndexesCount, …) added for the compact default display view.

Examples

Example 1 — Offline analysis of a saved plan

Get-sqmPlanInsights -Path ".\Execution plan.xml"

Example 2 — With live index coverage and Query Store telemetry

Get-sqmPlanInsights -Path ".\Execution plan.xml" -ServerInstance "PROD-SQL-01" -Database "StackOverflow2013" -InspectDatabase

Example 3 — Write a JSON bundle for the Plan Visualizer

Get-sqmPlanInsights -Path ".\Execution plan.xml" -ServerInstance "PROD-SQL-01" -Database "StackOverflow2013" -InspectDatabase -OutFile ".\plan.json"

Example 4 — Tighten CE mismatch sensitivity

Get-sqmPlanInsights -Path ".\Execution plan.xml" -CEMismatchRatio 5 -CEMinRows 50

Example 5 — SQL authentication via dbatools

$cred = Get-Credential
Get-sqmPlanInsights -ServerInstance "192.168.1.195" -Database "StackOverflow" -SqlCredential $cred
⬆ Index
All Commands