Documentation / Query Benchmark / Interface & Features

Interface & Features — Query Benchmark

Walk through the main screens, menus, and controls you will use day to day.

Benchmark workspace overview

Main layout

  • Connections sidebar — Add/edit saved SQL Server profiles. Collapse with the chevron.
  • Mode tabs — Benchmark, Compare Queries, Dual Environment, Config Impact, Index Analysis. The selected tab uses a brand-cyan underline (matches Script Runner styling).
  • Setup cards — Workload, Cache & Stats, Parameters, and Advanced (always expanded) on the main page.
  • Results — KPIs, percentiles, waits, charts, plans, and Tuning Assistant entry points.
  • File — Save Scenario (named dialog), Load Scenario, Export (PDF / HTML / JSON / CSV / Markdown / Excel), History, Exit.
  • View — Dark Theme toggle.
  • Help — Documentation, CI/CD Pipeline Integration… (copyable CLI + GitHub Actions + Azure Pipelines snippets), Query Tuning Assistant…, Feedback, Version History, Check for Updates, Manage License, About.

Workspace tabs

  • Benchmark — Single-query runs on the primary connection.
  • Compare Queries — Query A vs Query B on one server (optional different databases).
  • Dual Environment — Env A / Env B pickers for cross-server.
  • Config Impact — Proposed setting changes (MAXDOP, memory, cost threshold, etc.).
  • Index Analysis — Before/after index candidate workflow (lab servers).
Config Impact tab
Config Impact
Index Analysis tab
Index Analysis

Cache & Stats

Shown as a single compact row of options you can combine:

  • DBCC FREEPROCCACHE — Clears cached plans (instance-wide).
  • DBCC DROPCLEANBUFFERS — Flushes the buffer pool (cold data pages).
  • DBCC FLUSHPROCINDB — Flushes plans for the current database only.
  • UPDATE STATISTICS — Refresh statistics before measured runs.

Leave them unchecked for production-safe / shared servers. Elevated rights are required for DBCC options.

Query Parameters

Use Detect @params to find names like @CustomerId / @OrderDate in Query A/B, then choose a value source:

  • Fixed — Constant value each iteration.
  • Random / Range — Pick between Min and Max (int, decimal, or datetime).
  • Csv — Random pick from a comma/newline list, or a path to a .csv / .txt file.
  • Dataset — Round-robin through the same list (deterministic cycling).

Parameters are included when you save a scenario and when the CLI loads --scenario.

Think Time

Optional delay between iterations for each virtual user: None, Fixed ms, or Random between min/max. Use this to avoid unrealistically aggressive hammering and to simulate client pacing.

Functional Validation

In compare mode, optionally verify Query A and Query B return equivalent results (not only speed):

  • Row count — Same number of rows.
  • Checksum / SHA256 — Hash of result values.
  • Detailed compare — Stronger fingerprinting to help locate differences.

Execution Engine

Each iteration opens its own SQL connection (so session wait stats stay isolated). Statement IO and CPU come from the same SET STATISTICS IO / SET STATISTICS TIME messages SSMS shows on the Messages tab — not from session counter deltas.

Multi-batch scripts (GO)

If your SQL contains GO batch separators, the tool splits the script automatically:

  • Leading batches (setup) run with statistics off so their IO is not counted.
  • The last batch is the measured statement: SET STATISTICS IO ON and SET STATISTICS TIME ON are enabled only for that batch, then turned off again.

Scripts without GO are a single batch — the entire text is measured, same as running it in SSMS with statistics on.

Where each metric comes from

  • Logical / physical reads — Parsed from SET STATISTICS IO InfoMessages (same table lines as SSMS).
  • CPU time — From SET STATISTICS TIME execution times when > 0 ms; for sub-millisecond queries (where TIME rounds to 0), falls back to sys.dm_exec_query_stats.last_worker_time (microseconds).
  • Duration — Client wall-clock stopwatch around the measured batch (includes network and result streaming).
  • Granted / used / ideal memory, DOP, spills — From sys.dm_exec_query_stats after the statement finishes: last_grant_kb, last_used_grant_kb, last_ideal_grant_kb, last_dop, last_spills. Not taken from the captured execution plan XML.
  • Throughput (exec/s) — Calculated locally: successful iterations ÷ total wall-clock seconds for the run. Not a SQL Server counter and not from the plan.
  • TempDB / waits — Session task space usage and sys.dm_exec_session_wait_stats before/after each iteration.
  • Execution plan — Optional separate capture (SET STATISTICS XML) for Show Plan / Tuning Assistant; plan capture does not drive the KPI cards.

Results

Benchmark Results with KPI cards, waits, resource breakdown, recommendations, and export buttons

The Results pane shows a verdict banner, Insights (compare modes), KPI hero cards, charts, iteration grid, wait stats, and recommendations.

  • Phase tab bar — Edit Setup and Run Again stay next to the Setup / Results tabs.
  • Results header actions — View Queries (compare), Tuning Assistant, and Show Plan / Open Plan Comparison (when plans were captured).
  • Single — Metrics for one query; Show Plan opens one plan tree.
  • Compare / Index / Dual / Config — Side-by-side KPIs, Insights bullets, and downloadable plan XML from Plan Comparison.

Query Tuning Assistant

Opens as a slide-over panel from Help → Query Tuning Assistant… or the Results toolbar after a run. It combines:

  • On-device heuristics — Static analysis of SQL text and plan XML (anti-patterns, scans, lookups, spills, missing-index hints, and more). No data leaves your machine.
  • Live catalog / DMV checks — Against the connected database: heaps, disabled/unused indexes, fragmentation, stale statistics, missing-index DMVs, Query Store state, blocking, and key instance/database configuration.

Tips are severity-tagged (Critical / Warning / Info) with problem + recommendation text. In compare mode, pick Query A or Query B from the panel dropdown. Full guide: Query Tuning Assistant.

History

Benchmark History workspace with retention controls

File → History lists past runs with retention (max runs / max age), purge, and open/export actions. Change notes and expected improvement (when filled on the setup panel) are stored with the run.

Metrics Explained

  • Avg Duration — Mean client wall time across successful iterations (stopwatch around the measured batch).
  • P50 / P90 / P95 / P99 — Percentile latency (tail latency). Prefer P95/P99 for concurrency/load tests — averages can hide outliers.
  • Min / Max Duration — Extremes among successful iterations.
  • Logical / Physical reads — From SET STATISTICS IO (same totals as SSMS Messages). Avg / min / max across iterations.
  • CPU — From SET STATISTICS TIME when reported; otherwise microsecond worker time from query stats for short queries.
  • DOP — Actual degree of parallelism from sys.dm_exec_query_stats.last_dop when available.
  • Grant / Used / Ideal (KB) — last_grant_kb, last_used_grant_kb, last_ideal_grant_kb from query stats (not plan XML). Trivial plans may show 0.
  • Spills — last_spills from query stats (TempDB spills during the last execution).
  • TempDB — Session TempDB page allocation deltas for the iteration.
  • Throughput — Successful executions ÷ wall-clock seconds for the whole run (client-side). Not collected from SQL Server or the plan.
  • Blocking / Deadlocks — Surfaced when live resource metrics / engine counters capture them during the run.

See Where each metric comes from for the full collection model, including automatic GO batch handling.

Wait Stats

Waits are collected per iteration session and aggregated per query. In compare mode the grid shows A and B columns for the same wait type so you can see which side waited more.

Plans

Optional actual plans via Show Plan / Plan Comparison — graphical operators, diffs, missing indexes. Pair with the Query Tuning Assistant for actionable recommendations.

Themes

Query Benchmark dark theme

Toggle light/dark from View → Dark Theme. Light theme uses stronger contrast on labels and muted text for readability; mode tabs highlight in cyan on both themes.

Capacity Estimate

Uses server CPU/memory/MAXDOP to suggest conservative / recommended / aggressive concurrent users for lab-style load tests. Apply recommended users to populate the concurrency field.

Export

Export CSV, PDF, JSON, HTML, Markdown, or Excel from Results or File → Export. History also supports open/export of past runs with retention (max runs / max age) and purge.

CI/CD from the app

Help → CI/CD Pipeline Integration… shows ready-to-copy CLI examples plus GitHub Actions and Azure Pipelines YAML (including --out-junit). Full reference: CI/CD Pipeline.