Last updated: 2026-09-05

SQL Server Query Store

Open a database's Query Store history from a SQL Server connection, compare two adjacent periods with execution-weighted averages, and open the compiled plans each period used — on macOS, Windows or Linux. The workspace is read-only: it runs SELECTs over the sys.query_store_* catalog views and never changes the Query Store configuration.

What the Query Store workspace does

SQL Server's Query Store keeps per-interval runtime statistics for the queries a database ran, together with the plans that were compiled for them. It survives restarts and plan-cache eviction, so it can answer questions about last week rather than only about what is in the plan cache right now.

The workspace turns that catalog into one screen per database:

• a state header that says whether Query Store is on, what it is capturing, how much of its storage is used and how long an interval is;
• a comparison of a recent period against the period immediately before it, with duration, CPU time and logical reads weighted by execution count;
• a candidate list ranked by how far the chosen metric moved, with explicit labels for rows that cannot be compared;
• a detail pane with the query text, its identity, the per-interval rows behind the averages and the plans used in each period;
• Compare plans, which opens the two estimated compiled plans in the existing execution-plan compare view.

It is a diagnostic surface. A metric that moved is an observed change between two periods, not proof of a cause — the workspace says so next to the comparison and leaves the conclusion to you.

How to open it

• Object Explorer: right-click a database on a SQL Server connection and choose Query Store…
• Command palette: press Ctrl+Shift+P and run Query Store… The entry appears while a SQL Server connection is connected, and asks for the connection and database through the standard context picker when your tree selection does not already pin one.
• Main toolbar: More → DBA → Query Store…, next to Filegroup Manager and gated the same way.

The tab is titled Query Store: <database>. Opening the same connection and database again focuses the tab you already have rather than opening a second one.

Supported services and versions

Support is decided from SERVERPROPERTY('EngineEdition') before the product version, because Azure SQL Database reports engine version 12.x while fully supporting Query Store — a plain "major version 13 or higher" test would misclassify it.

Query Store state and permissions

The header line reports the facts the server gave, in this order: actual <state> · desired <state> · capture <mode> · <used> / <max> MB · interval <n> min, then · <k> intervals when the interval count could be read, · stale after <d> d only when stale_query_threshold_days is greater than zero, and · SQL Server <version> only when the server returned its product version. Its own refresh button re-checks the state without re-running the comparison.

Permissions

• SQL Server 2019 and earlier: VIEW DATABASE STATE on the database.
• SQL Server 2022 and later: VIEW DATABASE PERFORMANCE STATE is enough; the broader VIEW DATABASE STATE also works.
• Azure SQL Database: the database owner grants VIEW DATABASE STATE.

What the workspace never does

Every statement it sends is a SELECT over the sys.query_store_* and sys.database_query_store_options catalog views, SERVERPROPERTY() and the metadata functions OBJECT_SCHEMA_NAME / OBJECT_NAME (to name the module a query belongs to), run on the selected database's own connection pool rather than by switching context with USE. It does not enable Query Store, change the capture mode or storage settings, force or unforce a plan, remove a plan, clear history or call sp_query_store_flush_db. Prerequisites are explained in the UI as text you can hand to a DBA.

Choosing the two periods

The toolbar defines one recent period and one baseline period of the same length immediately before it. They are adjacent and never overlap.

• Period — 1 hour, 6 hours, 24 hours or 7 days. The default is 24 hours.
• ending at — an anchor entered in your local time and stored as UTC. It shows (now) until you set one; the Now button clears it again.
• Include in-progress interval — off by default. The interval that has not closed yet holds partial numbers that keep changing, so it is opt-in; when it is on, the recent period is labelled (last one in progress).

Effective boundaries are whole intervals

Query Store aggregates per collection interval, so it cannot answer for an arbitrary sub-interval. Rather than prorating an aggregate into invented precision, both periods are snapped to whole intervals: the recent period ends at the end of the latest eligible interval, and the baseline covers the same length immediately before it. The line under the toolbar shows the resulting UTC ranges and how many intervals each period contains, with the reminder that "Boundaries are snapped to whole Query Store intervals." Timestamps in the interval table are shown in UTC too; only the anchor input is local.

Two cases of sparse history are called out instead of silently returning nothing:

• "No interval has completed at or before the anchor, or one interval is longer than the period. Lengthen the period, move the anchor, or include the in-progress interval."
• "History does not reach back far enough for a baseline of the same length — queries are listed without a comparison (not comparable)."
• "History covers only n% of the baseline period — a partial baseline is not compared, so queries are listed without a comparison (not comparable)."

The baseline must cover the same length as the recent period. With 25 hours of history and a 24-hour period, the one hour before the recent period is not used as a baseline: the two periods would not be comparable, so the table says what share of the baseline the history covers and lists the queries without deltas.

A store that stopped collecting — READ_ONLY because its storage filled up, switched off for a while, or restored from a backup — has no interval that covers the anchor. The periods are then placed at the end of the retained history rather than at the anchor, and a note above the table says so: "Query Store history ends at date UTC — no interval covers the anchor, so both periods were placed at the end of the retained history." A store whose history ended a day and a half ago still gets a full 24-hour baseline, and one that ended three days ago still compares its last two periods; only history that is genuinely too short is reported as not comparable.

How the averages are calculated

sys.query_store_runtime_stats stores, per interval and plan, an average such as avg_duration together with count_executions. Averaging those averages is wrong whenever the execution counts differ, which they almost always do. The workspace sums avg × count_executions across every row in a period and divides by the summed count.

Why an average of averages misleads

Take one query and two intervals of the same period:

The average of the two interval averages is (1,000 + 1) ÷ 2 = 500.5 ms, about 250 times the weighted figure, and it comes entirely from the single slow execution in interval A. The same weighting is applied to duration, CPU time and logical reads, across every interval and every plan the comparison covers (a query with more than 50 plans is aggregated over its 50 heaviest, and the row says so), and the sums are done in SQL before the roll-up so an active interval's several rows for the same key all count.

The row's estimated workload is that weighted average multiplied by the executions: the total time or I/O the period spent on the query, which separates a 5 ms query that ran a million times from a 5 second one that ran twice.

Which executions are counted

Averages use regular executions only (execution_type = 0). Aborted (3) and exception (4) executions are counted separately and shown in the detail pane as their own row, so a batch of client timeouts does not quietly inflate the average duration of the successful runs.

Units

The catalog stores duration and CPU time in microseconds and logical reads in 8-KB pages. Nothing raw reaches the grid: durations are rendered as µs, ms or s depending on size, and page counts carry the MB equivalent in parentheses (1,024 pages is 8.0 MB). The detail pane repeats the conversion under its table.

Row statuses: minimum sample and missing baseline

A delta is only shown where it means something. Every row carries one of four statuses:

Min executions in the toolbar sets that threshold; it defaults to 5 executions in each period. Deltas are empty for every non-compared status — a missing baseline is never rendered as a measured zero.

Rows are ranked with regressions first: compared rows by the size of the change in the selected metric, then new queries by their recent average, then low-sample and not-comparable rows by recent workload. Clicking a column header re-sorts the loaded rows and the choice is kept with the tab.

Query and context identity

A row is one query_id on one connection and database. The detail pane shows query_id, query_text_id, context_settings_id and the module (schema.object, or "ad hoc" for a query that belongs to no object), because identical SQL text executed under different SET options is a different query to SQL Server with its own plans and its own statistics. Catalog ids are carried as decimal strings end to end, so an id beyond JavaScript's safe integer range still matches the server's.

Result bounds

The comparison and displayed detail lists are capped on the server, with each cap reported. The comparison ranks at most 2,000 candidate queries and, for each of them, aggregates its 50 heaviest plans by the chosen metric; a row whose query used more plans is marked (heaviest only) in the Plans column and in the detail pane. The detail pane reads the newest 2,000 per-interval rows (interval × plan × execution type) inside the two periods and the 50 most recently compiled plans; when either list was cut, a note above it says so ("Showing the newest 2,000 rows …", "Showing the 50 most recently compiled plans …"). The interval table renders 200 rows at a time with a Show more button, so a 7-day period at a one-minute interval length does not turn into twenty thousand table rows at once.

Plans

The plans section lists the 50 most recently compiled plans the catalog holds for the query, which of the two periods used it and how many regular executions it had in each (counted over the whole period, independently of the 2,000-row cap on the interval table below), whether the server has it forced, its last compile time, engine version and compatibility level. The A/B pickers use all plans that ran in each period, including plans omitted from this display limit. An older baseline plan remains available for comparison even when it is absent from the list. Plan XML is fetched only when you open a plan, never with the list.

• Compare plans A → B opens both plans side by side in the existing execution-plan compare view, titled Plans A vs B (query N, estimated).
• When exactly one plan ran in each period and it is the same plan, the compare button is replaced by Open plan and the note "The same plan (N) ran in both periods — this change is not explained by a plan change."
• A plan the catalog lists but whose XML cleanup has already removed reports "Plan N is no longer in Query Store (removed by cleanup or never captured). Refresh the list to see the current plans." and offers to refresh.

Two things about these plans are worth stating plainly. First, sys.query_store_plan.query_plan is the estimated compiled plan — the runtime statistics next to it are separate aggregates over many executions and are not attached to individual operators, so the compare view never shows per-operator actual rows. Second, the compare view matches operators by node id, which highlights structural differences between two separately compiled plans; it does not prove that two differently shaped subtrees are semantically equivalent.

A walkthrough on the synthetic sample

The numbers below come from the fixture that ships with the app's mock engine and is used by its automated tests and screenshots. They are synthetic sample data, not a measurement of any real database.

1. Right-click TestDB in Object Explorer and choose Query Store…. The header reports Read-write, capture AUTO, 12 / 1000 MB, a 60-minute interval and 73 intervals of history.
2. With the defaults (24 hours, Duration, min 5 executions) the top row is query 101, dbo.usp_GetOrdersByCustomer, status Compared. Below it sit a CPU-only regression that kept the same plan, a query with aborted executions, a New / no baseline row and a Low sample row.
3. Query 101 ran 120 times in each period, so its comparison is a like-for-like one:

   Metric (execution-weighted average)	Baseline	Recent	Δ
   Duration	10 ms	55 ms	+45 ms (+450%)
   CPU time	8 ms	50 ms	+42 ms (+525%)
   Logical reads	120 pages (0.9 MB)	900 pages (7.0 MB)	+780 pages (+6.1 MB)

4. Switching the metric to CPU time re-runs the comparison and re-ranks the list: the row that moved most on CPU while keeping the same plan comes to the top.
5. Selecting query 101 shows its text, its identity line, the per-interval rows behind those averages, and two plans: 1001 used in the baseline, 1002 in the recent period.
6. Compare plans 1001 → 1002 opens the plan compare view. In the fixture the baseline plan is an index seek and the recent one a clustered index scan — a candidate explanation for the duration, and the starting point for a real investigation rather than its conclusion.

The same shape is exercised against a live server by the real-database test: a 20,000-row table, one parameterised query run first without a supporting index and then with one, at a one-minute interval length, read back through the workspace.

Cancel, refresh and timeouts

Nothing polls. The comparison runs when you open the tab and when you change a filter; Refresh re-runs it, and the state header has its own re-check button. While a comparison is running the refresh button becomes Cancel, which sends the driver's attention signal to the catalog statement running on the server — including the state check when the comparison triggered it — rather than only discarding its reply; a cancel that lands between two statements stops the comparison before the next one is sent. Every statement is bounded to 60 seconds. Changing a filter re-runs the comparison against the state the tab already checked; Refresh re-checks the state first. Changing the database or a filter cancels the request in flight, and a late answer from an abandoned one is discarded instead of overwriting the newer view.

What is kept between sessions

The tab saves what identifies and shapes the view: the database, the period length, the anchor, the in-progress toggle, the metric, the minimum executions, the loaded result size, the column sort and which query was selected. It never saves query text, result rows, plan XML or plan artifact ids — plans opened from here are held for the life of that plan tab and re-fetched by their (database, query id, plan id) reference when you click again.

After a restart the data is fetched again. If the connection the tab belonged to is gone, the tab opens in its error state with a retry button and the usual workspace connection banner instead of turning into something else.

Limits of this release

• Bounded candidate set. The server ranks at most 2,000 queries by the chosen metric's total across both periods before the roll-up; the footer says when that cap was reached. Because the cut is by total workload, a rarely executed query with a large relative regression drops out before a busy query with a small one, and a query whose executions were all aborted or ended in an exception is never a candidate.
• Bounded result list. 50 rows at a time, Show more adds another 50, and the footer reads n of N queries.
• Retention is the server's. Query Store cleans up by its own stale-query threshold and storage cap; what is gone from the catalog cannot be shown, and Jam never flushes or clears to change that.
• Adjacent periods only. The baseline is always the period immediately before the recent one. Two arbitrary non-adjacent windows are not supported yet.
• All replicas are summed. Rows are not filtered by replica_group_id; on a primary-only on-premises database that is the whole story, on a read-scale setup it is not.
• No wait statistics. sys.query_store_wait_stats is not read.
• No plan forcing, no alerting, no saved baselines. Deliberately out of scope for a read-only investigation surface.

Storage and privacy

Query text, rows and plan XML live in the app's memory for as long as the tab is open and are never written to the session file. Analytics record that the workspace was opened and in which state, the metric, outcome and a result-size bucket for a comparison, and whether a plan open or compare succeeded — never query text, query ids, database names, connection names or server names.

Frequently asked questions