SQL Server Best Practices Analyzer

Analyze SQL Server code and artifacts for performance issues, security risks, design problems, and standards gaps. The analyzer ships with 60+ rules across five categories and can run via CLI, REST API, MCP (Claude), GitHub Copilot, or LLM-powered auto-fix.

Contents

• Overview
• Highlights and rule coverage
• Supported inputs
• Install and build
• Usage modes (CLI, scripts, API, MCP, Copilot, auto-fix)
• Output formats
• Configuration (LLM, environment)
• Folder structure
• Example workflows
• Support and troubleshooting

Overview

• Purpose-built SQL Server best-practice analyzer using Microsoft.SqlServer.TransactSql.ScriptDom
• Multiple interfaces: CLI executable, PowerShell scripts, REST API, MCP server, and GitHub Copilot shortcuts
• LLM integration optional: auto-fix SQL and enrich execution plan results when configured
• Works on scripts, execution plans, Extended Events, DACPAC/BACPAC packages, and zipped batches

Highlights and rule coverage

• Performance: SELECT *, NOLOCK, functions on indexed columns, leading wildcard LIKE, high join counts, non-sargable predicates
• Security: SQL injection patterns, xp_cmdshell, missing error handling/transactions, unsafe dynamic SQL
• Code quality: missing WHERE, old-style joins, cursors, unused variables/parameters, CTE semicolons, SET NOCOUNT
• Design: missing clustered indexes/PKs/FK indexes, deprecated data types (TEXT/NTEXT/IMAGE), wide PKs, duplicate indexes
• Standards: naming conventions, table hints without WITH, ANSI settings, WAITFOR usage

Complete rule reference: SqlAnalyzer/README.md

Supported inputs

• SQL scripts: .sql, .tsql, .ddl
• Execution plans: .sqlplan, .xml
• Extended Events: .xel
• Database packages: .dacpac, .bacpac

Install and build

Prerequisites: .NET 8.0 SDK, PowerShell 7+ recommended on Windows.

cd Analyzer/SqlAnalyzer
dotnet build -c Release

Run tests (optional but recommended):

cd Analyzer
dotnet test

Usage modes

PowerShell scripts (fastest onboarding)

• Interactive demo: .\scripts\quick-start.ps1
• Default analysis (Analysis/input): .\scripts\run-analyzer.ps1
• With bundled examples: .\scripts\run-analyzer.ps1 --examples
• Analyze a file: .\scripts\run-analyzer.ps1 --file "C:\Path\query.sql"
• Analyze a directory (schema-aware): .\scripts\run-analyzer.ps1 --directory "C:\Database\Scripts" --schema
• Summary only: .\scripts\run-analyzer.ps1 --summary
• Auto-fix (requires .env): .\scripts\run-analyzer.ps1 --fix

CLI executable

• Single file: SqlAnalyzer.exe --file "C:\Scripts\query.sql"
• Directory recursive: SqlAnalyzer.exe --directory "C:\Scripts" --schema
• Execution plan: SqlAnalyzer.exe --file "slow_query.sqlplan"
• Extended Events: SqlAnalyzer.exe --file "trace.xel"
• DACPAC/BACPAC: SqlAnalyzer.exe --file "database.dacpac" --schema
• JSON output: SqlAnalyzer.exe --file query.sql --json --include-source

REST API

• Project: SqlAnalyzer.Api
• Start locally: cd SqlAnalyzer.Api && dotnet run
• Default URLs: http://localhost:5000, https://localhost:5001 (Swagger enabled)
• Key endpoints: /api/sqlanalysis/analyze-code, /analyze-file, /analyze-directory, /analyze-zip, /analyze-execution-plan, /health
• Deployment guidance: AZURE_DEPLOYMENT.md

MCP (Claude Desktop)

• Configure Claude with the MCP server command pointing to SqlAnalyzer.exe --mcp
• Setup guide: SqlAnalyzer/MCP_SETUP.md

GitHub Copilot shortcuts

• In Copilot Chat: /analyze-sql, /auto-fix-sql, /build-analyzer, /run-api, /help
• Copilot agent details: .github/copilot-instructions.md

LLM-powered auto-fix

• Requires .env with OpenAI/Azure OpenAI settings
• Run via script: .\scripts\run-analyzer.ps1 --file "query.sql" --fix --env ".env"
• Direct CLI: SqlAnalyzer.exe --file "query.sql" --fix --env ".env"
• Guide: LLM_FIX_GUIDE.md

Output formats

• Console: colorized summary with rule, severity, line/column, and remediation hint
• JSON: violations, counts, and (for .sqlplan) llmEligible + llmResult fields
• Reports: written to Analysis/output (gitignored) when using scripts with output enabled

Sample execution plan JSON fragment:

{
  "executionPlanResults": [
    {
      "queryNumber": 8,
      "llmEligible": true,
      "llmResult": {
        "attempted": true,
        "success": true,
        "fixedSql": "SELECT ...",
        "changes": ["Rewrote scalar UDF to inline logic"],
        "violationsAddressed": ["FunctionInWhereClause"],
        "notes": "Replaced UDF with sargable predicate."
      }
    }
  ]
}

Configuration

• .env (for LLM features only; do not commit)
  • Create at repo root when you want auto-fix or LLM plan enrichment
  • Azure OpenAI: AZURE_OPENAI_ENDPOINT, AZURE_OPENAI_KEY, AZURE_OPENAI_DEPLOYMENT, AZURE_OPENAI_API_VERSION
  • OpenAI: OPENAI_PROVIDER=openai, OPENAI_API_KEY, OPENAI_MODEL
  • Example:

    OPENAI_PROVIDER=openai
    OPENAI_API_KEY=your-key-here
    OPENAI_MODEL=gpt-4o

• Analysis input/output are already gitignored (Analysis/input, Analysis/output)
• Build artifacts ignored via .gitignore (bin/, obj/, publish/)

Folder structure

Analyzer/
├── Analysis/            # Input/output workspace (gitignored except README)
├── scripts/             # Helper scripts (run-analyzer.ps1, quick-start.ps1)
├── SqlAnalyzer/         # Core analyzer library + CLI
├── SqlAnalyzer.Api/     # REST API wrapper
├── Examples/            # Sample SQL files and docs
└── SqlAnalyzer.Tests/   # Unit and smoke tests

Detailed layout: FOLDER_STRUCTURE.md

Example workflows

• Validate a single proc: SqlAnalyzer.exe --file "C:\db\GetOrders.sql"
• Scan a repo folder with schema checks: SqlAnalyzer.exe --directory "C:\db\scripts" --schema
• Triage a slow plan: SqlAnalyzer.exe --file "slow_query.sqlplan" --json > plan.json
• Review production Extended Events: SqlAnalyzer.exe --file "trace.xel"
• Auto-fix candidate script: .\scripts\run-analyzer.ps1 --file "C:\db\legacy.sql" --fix --env ".env"

Support and troubleshooting

• Build issues: rerun dotnet restore/build with -c Release; ensure .NET 8.0 SDK installed
• File not found: use absolute paths and supported extensions listed above
• LLM errors: confirm .env variables, model name/deployment, and network access
• MCP tools not visible: restart Claude Desktop and verify MCP config path

Documentation links

• Getting started: GETTING_STARTED.md
• Copy-paste commands: COPILOT_COMMAND.md
• LLM auto-fix: LLM_FIX_GUIDE.md
• API deployment: AZURE_DEPLOYMENT.md
• ChatGPT integration: CHATGPT_SETUP.md
• Full analyzer details: SqlAnalyzer/README.md
• MCP setup: SqlAnalyzer/MCP_SETUP.md
• Examples: Examples/README.md

Get started now

• Add SQL files to Analysis/input/
• Run .\scripts\run-analyzer.ps1
• Review violations and iterate, or enable auto-fix with LLM config when ready

---

Built with: Microsoft.SqlServer.TransactSql.ScriptDom • .NET 8.0 • Model Context Protocol