sql-edm-performance-tuning-toolkit

An open-source SQL Server performance tuning toolkit for EDM-style batch data platforms. It helps developers and database engineers analyze technical debt, slow stored procedures, missing indexes, blocking, wait stats, batch SLA issues, and Azure migration readiness.

The toolkit is generic and uses synthetic examples only. It does not include proprietary Markit EDM internals, vendor code, or real financial data.

Who This Is For

• SQL Server developers responsible for batch data platforms.
• Data engineers tuning ingestion, validation, reconciliation, or distribution jobs.
• DBAs preparing SQL Server workloads for Azure SQL Managed Instance or SQL Server on Azure VM.
• Architects assessing modernization risk before cloud migration.

What You Can Do With It

• Build a synthetic EDM-style SQL Server demo database.
• Run diagnostic scripts for waits, indexes, blocking, Query Store, tempdb, and SLA breaches.
• Compare intentionally bad SQL with tuned versions.
• Generate markdown or HTML benchmark reports.
• Run static SQL technical-debt scans across a script folder.
• Review Azure migration readiness checks for MI and VM targets.

Architecture

flowchart LR
    DemoData[Synthetic EDM-style data] --> SQL[SQL Server demo database]
    SQL --> Diagnostics[Diagnostic SQL scripts]
    SQL --> Examples[Bad and tuned SQL examples]
    Examples --> Benchmark[Python benchmark runner]
    Benchmark --> Report[Markdown or HTML report]
    Diagnostics --> Migration[Azure migration readiness]
    Diagnostics --> Debt[Technical debt assessment]

Loading

Quick Start

git clone https://github.com/corzosoft/sql-edm-performance-tuning-toolkit.git
cd sql-edm-performance-tuning-toolkit
python -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install --upgrade pip
python -m pip install -e ".[dev]"
python -m pytest
python -m ruff check .

On macOS/Linux, activate the environment with source .venv/bin/activate.

Generate Synthetic Data

sql-edm-toolkit generate-test-data --output-dir generated-data --securities 1000 --price-days 10

This writes synthetic securities, prices, batch jobs, and validation exceptions as CSV files.

Run Reports Without SQL Server

Create an offline benchmark report:

sql-edm-toolkit run-benchmark --offline --report reports/benchmark-report.md

Create a static SQL technical-debt report:

sql-edm-toolkit assess-technical-debt --sql-root sql --report reports/technical-debt-assessment.md

The technical-debt scan flags patterns such as cursor/RBAR logic, scalar UDFs, non-sargable predicates, temp table usage, and SELECT *.

Run SQL Server Locally

docker compose up -d

SQL Server starts on localhost,1433 with user sa and password YourStrong!Passw0rd.

Apply these scripts in order:

sql/001_create_demo_database.sql
sql/002_create_batch_tables.sql
sql/003_insert_sample_data.sql

To execute benchmark scripts against SQL Server, install the optional dependency and Microsoft ODBC Driver for SQL Server:

python -m pip install -e ".[mssql]"
sql-edm-toolkit run-benchmark `
  --before sql/examples/bad_query_before.sql `
  --after sql/examples/tuned_query_after.sql `
  --report reports/sql-benchmark.html

Diagnostic Scripts

Script	Purpose
wait_stats.sql	Top waits for CPU, IO, locking, and memory-pressure investigation.
missing_indexes.sql	Optimizer missing-index candidates for review.
index_usage.sql	Seeks, scans, lookups, and updates by index.
blocking_sessions.sql	Active blocking chains.
deadlock_analysis_notes.sql	Deadlock investigation checklist.
long_running_procedures.sql	Slow cached stored procedures.
query_store_top_queries.sql	Query Store resource consumers.
tempdb_pressure.sql	tempdb allocation and session usage.
batch_sla_report.sql	Batch jobs that are over or near SLA.

Azure Migration Readiness

The sql/migration folder includes checks for:

• Azure SQL Managed Instance readiness.
• SQL Server on Azure VM readiness.
• SQL Agent jobs.
• Linked servers.
• Cross-database and cross-server dependencies.
• Potentially unsupported or risky features.

Production Boundaries

The diagnostic scripts are useful starting points, not automatic tuning instructions. Always review execution plans, workload impact, write overhead, and operational constraints before applying indexes or rewriting production procedures.

Before production use, add:

• Safe read-only execution roles.
• Change-control workflow for tuning recommendations.
• Query Store baselines.
• Representative load testing.
• Environment-specific migration compatibility checks.

Roadmap

• Add JSON report output for CI ingestion.
• Add plan-cache export helpers.
• Add SQL Agent job timeline visualization.
• Add compatibility checks for more Azure SQL target options.
• Add Docker bootstrap scripts for applying SQL files automatically.

Contributing

Contributions are welcome. See CONTRIBUTING.md.

Use synthetic data only. Do not submit production query text, real execution plans with confidential object names, credentials, or customer schemas.