Documentation / Query Benchmark / Common Workflows

Common Workflows

Controlled experiments you can repeat, share, and gate in CI.

Single-query baseline

Query ready to run with iterations configured
  1. Add a server in the Connections sidebar (if needed), then pick it on the main page to auto-connect and select the database.
  2. Paste the statement into Query A; set iterations / users from the presets; leave warm-up as needed.
  3. If the SQL uses variables, Detect @params and set Fixed / Random / Range / Csv values.
  4. Add a short Change note (e.g. “baseline before index”).
  5. Leave Cache & Stats unchecked on shared servers.
  6. Run Benchmark; review avg + P95/P99; use File → Save Scenario (enter a name) or Export.

Compare two query versions

Compare Queries tab
  1. Open Compare Queries; paste Query A (baseline) and Query B (candidate).
  2. Use Same database or pick a second database on the same server.
  3. Set iterations, think time, and cache options for a fair A/B.
  4. Enable functional validation if results must match.
  5. Run; review avg / P50–P99, CPU, reads, waits, and plans. Optionally open Query Tuning Assistant for each side. Export PDF/JSON/CSV for the PR.

Load / concurrency test

  1. Set Users to 10, 25, or 50 from the preset list.
  2. Under Advanced, set Duration (e.g. 300 / 900 s) and Ramp-up.
  3. Optional think time (Fixed or Random) to avoid unrealistically aggressive hammering.
  4. Run; focus on throughput, P95/P99, waits, TempDB, and success rate — not only average duration.

CI / regression gate (CLI)

Full details, outputs, exit codes, and pipeline YAML: CI/CD Pipeline. Quick start:

# Capture a baseline report
DBLenseQueryBenchmark.exe --run -S . -d Sales --sql-file query.sql --iterations 20 --out baseline.json

# Fail the build if the new run is >10% slower on P95; also write JUnit for the test publisher
DBLenseQueryBenchmark.exe --run -S . -d Sales --sql-file query.sql --baseline baseline.json --fail-if-regression 10 --fail-on-p95 --out current.json --out-junit results.junit.xml

Exit codes: 0 ok · 1 error · 2 regression · 3 execution errors. In the GUI: Help → CI/CD Pipeline Integration… for copyable GitHub Actions / Azure DevOps snippets.

Query Tuning Assistant after a run

  1. Enable Collect actual plan, run the benchmark, then open Tuning Assistant (Results toolbar or Help menu).
  2. Review Critical / Warning / Info tips from SQL+plan heuristics and live catalog checks.
  3. Apply changes in a lab, re-benchmark, and compare P95 — see Query Tuning Assistant.

Cross-server / version upgrade test

Dual Environment connection cards
  1. Open Dual Environment; pick Env A (current) and Env B (target build or SKU) from saved connections.
  2. Use the same SQL and parameters on both; align database compatibility intentionally.
  3. Run equal iterations; compare plans and waits — not just average duration.

Config impact

Config Impact workspace
  1. Open Config Impact and choose proposed settings (MAXDOP, memory, cost threshold, etc.).
  2. Run the same query before/after (or with proposed options) on a lab instance.
  3. Keep production changes out of band — the tool measures impact; it does not push server config for you unless you explicitly apply lab scripts.

Index Analysis (lab only)

Index Analysis setup with query and CREATE INDEX DDL
  1. Open Index Analysis on an isolated / lab SQL Server (never production).
  2. Paste the query to measure and a candidate CREATE INDEX statement in the Index DDL pane.
  3. Confirm the lab warning, then run. The tool benchmarks without the index, creates it, benchmarks with it, captures plans for both sides, then drops the index (best-effort).
  4. Review Insights, KPI deltas, iteration grid, and Open Plan Comparison. Export or save a scenario for the PR.
Leftover indexes from a prior failed drop are pre-cleared automatically. Prefer schema-qualified names such as ON Sales.SalesOrderDetail.
Index Analysis results

Save & load scenarios

Save Scenario dialog
  1. Configure the workspace (Benchmark, Compare Queries, Index Analysis, …), connections, SQL, parameters, and options.
  2. File → Save Scenario — stores Query A, Query B (compare), Index DDL (index analysis), workspace tab, config, and parameters.
  3. File → Load Scenario restores the correct tab and both editor panes so you can re-run after a code change.
  4. Export the scenario JSON for CI with --scenario path.json (see CI/CD Pipeline).