SQL Refactor Analyzer

Native C#/WinForms desktop tool for T-SQL refactoring and query-plan analysis at scale. Batch- scans stored procedures/views/functions across one or more databases (or a folder of .sql files, offline) for anti-patterns, deprecated syntax and formatting issues, correlates execution-plan/index findings from the DMVs, and produces an HTML/Markdown report.

Replaces the earlier PowerShell module SQL-Tools/sqmRefactorTool (WinForms wizard on top of dbatools). That version's feedback was that the wizard GUI was "not comfortable enough" and that PowerShell might not be the right approach for this - this project is a from-scratch native rewrite in response.

Strictly read-only: only ever runs SELECT against system catalog views/DMVs, never ALTER/CREATE/DROP, and never writes back to a scanned .sql file.

Project Structure

SqlRefactorAnalyzer/
├── Models/      # Finding, RuleInfo, RuleHit, ScanObject, Severity/RuleCategory/ObjectTypeFilter enums
├── Parsing/     # Dependency-free T-SQL tokenizer (no ScriptDom/AST parser - see below) + snippet extraction
├── Rules/       # ISqlRule + the 14 token-stream-based rules + RuleCatalog (single source of truth)
├── Data/        # Microsoft.Data.SqlClient readers: object definitions (sys.sql_modules), plan-cache/index DMVs
├── Scan/        # ScanEngine (tokenize -> run rules) + FolderScanner (offline *.sql mode)
├── Export/      # HTML (dark-theme) + Markdown report builders
├── UI/          # Theme.cs (dark theme, copied from SsisAnalyzer), single-window MainForm, AboutForm
├── Program.cs   # Entry point + CLI verification switches (see below)
├── SqlRefactorAnalyzer.csproj   # net48, WinForms, Microsoft.Data.SqlClient
└── nuget.config                # explicit nuget.org source

Why no ScriptDom/AST parser: SsisAnalyzer (same repo, SQL-Tools/SSIS/SsisAnalyzer) already ships a dependency-free T-SQL tokenizer for its pretty-printer (Parsing/SqlPretty.cs) - established practice in this codebase. Parsing/SqlTokenizer.cs follows the same approach, with per-token line numbers added (needed for Finding.Line, which the pretty-printer never needed). Rules scan the flat token stream (keyword sequences, paren depth, clause boundaries) instead of walking a real parse tree - documented trade-off, not a claim of full T-SQL parsing correctness. One rule (DeprecatedJoinSyntaxRule, the *=/=* legacy outer-join syntax) is a plain text scan instead, since that syntax has no token-stream shape to key off.

Why net48 instead of net8/9 (unlike SsisAnalyzer): target machines are DBA workstations/jump hosts that don't reliably have a modern .NET desktop runtime installed; .NET Framework 4.8 ships with Windows, so there's nothing to install.

Building

dotnet build SqlRefactorAnalyzer.csproj

(A nuget.config with an explicit nuget.org source ships in this folder, in case the build machine has no NuGet source configured globally.)

Running

SqlRefactorAnalyzer.exe                      # GUI (single-window dashboard, no wizard)

CLI verification switches (no GUI, useful for scripting/CI)

SqlRefactorAnalyzer.exe --scanfolder <folder> [--savebaseline <file.json>] [--comparebaseline <file.json>]
                                                                       # scan *.sql files, print findings; optionally persist them as a
                                                                       # baseline or diff against an earlier one (neu/behoben/unveraendert)
SqlRefactorAnalyzer.exe --report <folder> html|md <outfile>           # scan + write report
SqlRefactorAnalyzer.exe --dbscan <server> <database> [--sqlauth <user> <pass>] [--plananalysis]
SqlRefactorAnalyzer.exe --shot <outfile.png> [<server> <database> [--sqlauth <user> <pass>]] [--problems]
                                                                       # render MainForm off-screen; with server+database, runs a real
                                                                       # scan first (RunHeadlessScan) so the grid/Problem-Queries tab
                                                                       # have real data instead of an empty layout; --problems switches
                                                                       # to the Problem-Queries tab before the screenshot
SqlRefactorAnalyzer.exe --planshot <server> <database> <object> <outfile.png> [--sqlauth <user> <pass>]
                                                                       # render the Plan Visualizer off-screen for one object's cached plan

Results grid: Plan-availability marker + Problem-Queries tab

Two small, data-only additions on top of the existing findings list (no new detection engine):

• "Plan" column in the Findings grid: a checkmark on any QP0xx row whose object currently has a plan available - in the plan cache OR in Query Store, matching exactly what the Plan Visualizer's double-click can open (QueryPlanReader.GetObjectsWithCachedPlan, computed once right after a database scan). Best-effort snapshot from scan time, not a guarantee (a cached plan can still get evicted between the scan and the click - though the Query Store fallback now catches most of those cases).
• "Problem-Queries" tab (UI/ProblemQueriesPanel.cs), next to "Findings": every finding (AntiPattern/Formatting/QueryPlan alike) grouped by object and ranked by a severity/impact- weighted score (Critical=30, Warning=10, Info=2, plus Finding.ImpactScore capped at 50 per finding so one outlier can't drown out an object with many smaller problems) - "badly written query" surfaced as "the object accumulating the most/worst findings," reusing the same Finding data the flat grid already has rather than a separate detector. Selecting a row lists every finding for that object plus any ready-to-use SuggestedSnippet (e.g. QP009's generated CREATE INDEX) as the concrete improvement suggestion.

Baseline compare

File -> "Save baseline..." persists the current findings as JSON; "Compare with baseline..." diffs a later scan against it and reports neu / behoben / unveraendert - turns the report from a snapshot into a progress record for recurring engagements on the same database. Also available headless for CI: --scanfolder <folder> --savebaseline/--comparebaseline <file.json>. The diff key is RuleId|ObjectName|Line WITHOUT the message text (QueryPlan messages embed volatile numbers that change between scans even when the problem is identical), with multiset semantics for several findings sharing one key - see Export/BaselineStore.cs.

Async scans

Database and folder scans run on a worker thread (Task.Run) with per-object progress in the status bar and a Cancel button - a large production database no longer freezes the window. RunHeadlessScan (used by --shot) deliberately calls the synchronous scan core instead, so the screenshot can't race a still-running background scan.

Releases

.github/workflows/release.yml builds the Release configuration on windows-latest and attaches the complete, ready-to-run net48 folder as a ZIP whenever a v* tag is pushed (git tag v1.0.0 && git push origin v1.0.0). The ZIP is the whole deployment unit - target machines only need the .NET Framework 4.8 runtime (in the box on Win10 2004+/Win11).

Plan Visualizer

UI/PlanViewerForm.cs + UI/PlanViewerControl.cs - a graphical operator tree for an object's execution plan, opened by double-clicking any QueryPlan-category (QP0xx) row in the results grid after a database scan, or via File -> "Open .sqlplan..." for any plan file on disk (e.g. mailed in by a customer - works fully offline). Plans come from the plan cache first; if the plan has been evicted, GetPlanXmls falls back to Query Store (SQL 2016+), which persists plans across restarts and eviction - the window title shows which source delivered ([Plan-Cache] / [Query Store] / [Datei]). "Als .sqlplan speichern..." writes the selected statement's source XML for full-detail viewing in SSMS/Azure Data Studio. Root operator at the top, children fan out below (a simplified top-down layout, not a literal clone of SSMS's right-to-left graphical plan - much simpler to lay out via a single recursive pass, still immediately readable as a tree):

• Edge thickness scales with EstimateRows (log scale) - the same "fat pipe" idea SSMS uses.
• Box fill colour scales with EstimatedTotalSubtreeCost relative to the plan's most expensive operator - a quick heat map of hot spots without reading numbers.
• Decisive problems get their own fixed highlight, independent of cost (PlanNode.ClassifyProblem, used by both PlanViewerControl and the detail panel): an operator with a critical showplan warning (SpillToTempDb, NoJoinPredicate) gets a solid red fill and thick red border regardless of how cheap it looks cost-wise; any other showplan warning (implicit converts, missing stats, ...) or a Hash/Merge join with a highly asymmetric input (same heuristic as QP008) gets an amber border. A cheap-but-broken operator must not get lost next to an expensive-but-fine one, which the cost heat map alone can't guarantee.
• Mouse wheel zooms centred on the cursor, left-drag pans, left-click selects an operator and fills the bottom detail panel (full cost/row/IO/CPU/parallel/problem-classification breakdown).
• A stored procedure/view can contain more than one statement; the top combo (built from Data/PlanXmlParser.ParseAll, one PlanTree per top-level StmtSimple) switches between them.

Data flow: QueryPlanReader.GetPlanXmls re-fetches the raw plan XML(s) for the double-clicked object on demand (a Finding only carries the summarized QP00x text, not the XML) - plan cache first, Query Store fallback second (QS stores one plan per STATEMENT/query_id, so the fallback can return several XML documents; both sources hold compile-time/estimated plans, QS runtime stats stay statement-level) -> PlanXmlParser.ParseAll walks each into PlanNode/PlanTree trees -> PlanViewerControl lays out and draws them. --planshot (above) exercises this whole chain headlessly, the same "no mouse/keyboard in this environment" need --shot already covers for MainForm.

Known trade-offs, consistent with the rest of this project's documented-not-hidden approach:

• Control-flow wrappers (IF/WHILE -> StmtCond/StmtCursor/...) aren't walked into, only plain StmtSimple statements - a procedure built entirely out of control flow shows 0 statements rather than a guessed tree.
• GetPlanXmls picks the first cached plan_handle found for an object; a procedure with several cached plans (e.g. parameter-sniffing recompiles) only shows one of them. The Query Store fallback analogously takes the LATEST plan per statement, not the plan history.
• A GDI-vs-GDI+ text-rendering gotcha bit this during development and is worth remembering if this control is extended: TextRenderer.DrawText ignores Graphics.Transform (it draws at literal device pixels even inside a pan/zoom TranslateTransform/ScaleTransform block), so all label drawing inside the transformed block must use Graphics.DrawString instead - PlanViewerControl.DrawNode does this; any new label drawn inside OnPaint's transformed section needs to follow the same rule.
• A second real bug bit this too: <Warnings> can sit directly under <QueryPlan> (statement- level, e.g. PlanAffectingConvert on an OPENJSON key) as well as nested under a specific <RelOp>. PlanXmlParser.ParseNode only ever walked the latter, so a statement-level warning had no operator to attach to and silently never reached PlanNode.Warnings - a plan with a real, confirmed warning rendered with no highlight anywhere. Fixed by attaching any QueryPlan-level <Warnings> to the root PlanNode (not perfectly precise about which operator caused it, but visible rather than dropped).

Rule Catalog (v1, Rules/RuleCatalog.cs)

Id	Category	Severity	Rule
AP001	AntiPattern	Warning	SELECT * instead of an explicit column list
AP002	AntiPattern	Warning	WITH (NOLOCK) table hint
AP003	AntiPattern	Warning	Cursor instead of set-based processing
AP004	AntiPattern	Warning	Likely scalar UDF call in a WHERE/JOIN predicate
AP005	AntiPattern	Critical	Dynamic SQL built via string concatenation in EXEC()
AP006	AntiPattern	Info	User procedure named with the reserved sp_ prefix
AP007	AntiPattern	Info	Table reference without an explicit schema prefix
AP008	AntiPattern	Critical	Deprecated *=/=* outer-join syntax
AP009	AntiPattern	Critical	UPDATE/DELETE without a WHERE clause (full-table accident; table variables and UPDATE STATISTICS excluded)
AP010	AntiPattern	Warning	Built-in function on a COLUMN in a WHERE/JOIN predicate (YEAR(col) = ...) - non-SARGable; function-on-parameter stays silent
AP011	AntiPattern	Warning	NOT IN with a subquery - one NULL in the subquery silently empties the result; suggests NOT EXISTS
AP012	AntiPattern	Warning	SELECT TOP n without ORDER BY - nondeterministic row choice (TOP 100 PERCENT and EXISTS(SELECT TOP 1 ...) excluded)
FMT001	Formatting	Info	Missing SET NOCOUNT ON
FMT002	Formatting	Info	Inconsistent keyword casing
QP001	QueryPlan	Warning	Plan-cache warnings (<Warnings> in the showplan XML), with tailored root-cause text for known types (SpillToTempDb, NoJoinPredicate, ColumnsWithNoStatistics, PlanAffectingConvert, UnmatchedIndexes)
QP002	QueryPlan	Info	Unused index (no seeks/scans/lookups since last restart)
QP003	QueryPlan	Warning	Duplicate/overlapping index (same key-column signature)
QP004	QueryPlan	Info	Disabled index
QP005	QueryPlan	Info/Warning	Plan overview: degree of parallelism vs. cost, memory grant efficiency
QP006	QueryPlan	Info/Warning	Top expensive operators per statement (by estimated subtree cost)
QP007	QueryPlan	Info/Warning/Critical	Cardinality mismatch: estimated vs. actual average row count (sys.dm_exec_query_stats)
QP008	QueryPlan	Warning	DBA heuristics: skewed Hash/Merge join inputs, per-thread parallelism skew
QP009	QueryPlan	Info/Warning/Critical	Missing index suggestion (sys.dm_db_missing_index_*) with a generated, ready-to-use CREATE INDEX statement

QP001-QP009 are all live-database only, via Data/QueryPlanReader.cs. HTML/Markdown reports also render a "Priorisierte Massnahmen" ("Action Items") section: the top 15 findings across every category and rule, ranked by severity and then by Finding.ImpactScore where a rule provides one.

Add a rule: implement ISqlRule under Rules/, add one line to RuleCatalog.All.

Known Limitations

• Token-stream scanning, not a real parser: nested subqueries can occasionally mis-bound a predicate zone for ScalarUdfInPredicateRule; old-style comma-joins (FROM A, B) are only partially covered by MissingSchemaPrefixRule (only the table right after FROM/JOIN is checked). Documented in the rule's own XML doc comments, not silently swept under the rug.
• QueryPlanReader's duplicate-index detection uses STRING_AGG (SQL Server 2017+); the cardinality-mismatch rule (QP007) uses sys.dm_exec_query_stats.total_rows, also a fairly recent column (SQL Server 2016 SP2 / 2017 CU3+).
• sys.dm_exec_query_plan returns the CACHED (compile-time) plan, which has no RunTimeInformation/actual-row-count data - that only exists in an actual plan (SET STATISTICS XML ON, Extended Events, or Query Store's "last actual plan" capture on SQL Server 2022+). Consequences for QP005/QP007/QP008: memory-grant efficiency (QP005) only fires the granted-vs-used comparison when MaxUsedMemory happens to be present, otherwise it reports the compile-time estimate only and says so explicitly; cardinality mismatch (QP007) is therefore computed at the statement level from dm_exec_query_stats instead of per-operator; per-thread parallelism skew (QP008) is a defensive no-op on typical cached plans and only activates if fed a plan that does carry runtime counters.
• Live-database scanning (--dbscan, "Scan Database" in the GUI) has been run end-to-end against a real SQL Server 2022 instance (--dbscan DEV01 CORO_DB --sqlauth ... --plananalysis): QP001/QP005/QP006/QP007 all fired correctly on real cached plans with no errors. QP002-QP004, QP008 and QP009 did not fire in that run simply because the test database had no unused/ duplicate/disabled indexes, no skewed Hash/Merge join inputs, and no missing-index DMV entries at scan time - not yet separately confirmed against a database that actually has one of those conditions.