--- title: "SQL Server Query Store" description: "Read SQL Server Query Store history from Mac, Windows or Linux: compare two periods with execution-weighted averages and open the stored compiled plans." url: "https://jamsql.com/docs/query-store/" html_url: "https://jamsql.com/docs/query-store/" generated: "2026-09-20T00:14:45.275Z" --- 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 `SELECT`s 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: `. Opening the same connection and database again focuses the tab you already have rather than opening a second one. [Screenshot: query-store-overview.png] ## 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. Service Query Store workspace Notes SQL Server 2016 and later (on-premises, VM, Linux, containers) Yes EngineEdition 1–4 with ProductMajorVersion 13 or higher. Verified in this build against SQL Server 2025 Developer Edition, including a seeded database whose runtime statistics and plans were read back through the workspace. SQL Server 2014 and older No Query Store shipped in SQL Server 2016. The tab shows "Query Store requires SQL Server 2016 or later" together with the version the server reported. Azure SQL Database Expected to work, not verified by us EngineEdition 5 is treated as supported and the catalog views are the same ones. Mapped from Microsoft's documentation; we have not run the workspace against a live Azure SQL database, so treat it as unverified. Azure SQL Managed Instance Expected to work, not verified by us EngineEdition 8, same catalog views and the same caveat: documented, not exercised against a live instance in this build. Other EngineEdition values (Azure Synapse = 6, Azure SQL Edge = 9, …) Probed Treated as supported and checked against the catalog. When `sys.database_query_store_options` does not exist the tab reports "Not supported" with the note that the server does not expose the Query Store catalog views. PostgreSQL, MySQL, Oracle, SQLite, Kusto No Query Store is a SQL Server feature. The launchers do not appear on those connections. Browser build No Reports the feature as not supported and links to the desktop download. ## Query Store state and permissions The header line reports the facts the server gave, in this order: `actual · desired · capture · / MB · interval min`, then `· intervals` when the interval count could be read, `· stale after d` only when `stale_query_threshold_days` is greater than zero, and `· SQL Server ` only when the server returned its product version. Its own refresh button re-checks the state without re-running the comparison. Badge When What you see **Read-write** `actual_state_desc` is READ\_WRITE and at least one interval exists Normal browsing. **Read-only** READ\_ONLY with history "Query Store is READ\_ONLY: history is retained but no new data is captured." When READ\_WRITE was requested, the message names the `readonly_reason` instead (often storage full). Browsing works exactly as it does in read-write. When the interval count itself could not be read, the message says so and the header shows no interval count. **Enabled, no history yet** enabled, zero intervals "Query Store is enabled but has not recorded any runtime-statistics intervals yet.", plus a note to run the workload and wait for the first interval (its length is on the header line). A READ\_ONLY store with no intervals keeps its read-only message and `readonly_reason`, and the note says that a DBA has to restore READ\_WRITE before anything is recorded. **Off** OFF "Query Store is OFF for this database, so no history is being collected." followed by the `ALTER DATABASE` statement a DBA would run, ending with "Jam SQL Studio never changes this setting." **Off** ERROR "Query Store is in an ERROR state for this database." followed by the same `ALTER DATABASE` guidance. **Permission denied** SQL error 229, 297 or 300 The server's own message plus the grant for your version (see below). **Not supported** see the table above The version the server reported, and why it is not enough. **Error** anything else — a dropped connection, a timeout, a driver failure The server's or driver's own message (there is no fixed text) and a re-check button. [Screenshot: query-store-off-state.png] ### 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: Interval Executions Average duration Contribution (avg × executions) A 1 1,000 ms 1,000 ms B 999 1 ms 999 ms **Period** **1,000** **1.999 ms** (1,999 ÷ 1,000) **1,999 ms** 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: Status Meaning **Compared** Both periods have at least the minimum number of executions. Absolute and relative deltas are shown; the relative delta only when the baseline average is greater than zero. **New / no baseline** The baseline period exists but this query did not run in it. Reported as new rather than as an infinite increase. **Low sample** Fewer than the minimum executions in at least one period. The numbers are still shown; the label is there so you do not read confidence into three executions. **Not comparable** There is no baseline period of the same length: history does not reach back that far at all, or only into part of the baseline period (the message above the table says what share it covers). The recent period is still listed. **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. [Screenshot: query-store-detail.png] ### 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. [Screenshot: query-store-plan-compare.png] ## 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 Can I read SQL Server Query Store on a Mac or Linux? Yes. The Query Store workspace is part of the Jam SQL Studio desktop app, which runs on macOS, Windows and Linux, and it reads the catalog views over your normal SQL Server connection. Nothing about it is Windows-only; there is no Query Store screen in the browser build, which reports the feature as not supported and points at the download page instead. Do I need to enable Query Store first? Yes, and a DBA has to do it. Query Store is a per-database setting that is off by default on most databases. When it is off, the workspace shows an Off badge with the statement that would turn it on: ALTER DATABASE \[db\] SET QUERY\_STORE = ON (OPERATION\_MODE = READ\_WRITE). Jam SQL Studio prints that guidance as text and never runs it, so opening the tab on a database with Query Store off changes nothing on the server. Which permissions does the Query Store workspace need? Reading the sys.query\_store\_\* catalog views needs VIEW DATABASE STATE on the database. SQL Server 2022 added the narrower VIEW DATABASE PERFORMANCE STATE, which is enough as well. When the server answers with error 229, 297 or 300, the workspace shows the server's own message plus the grant that matches your version, and on Azure SQL Database it points at the database owner. Jam never grants permissions for you. Are the plans in Query Store actual or estimated plans? Estimated. sys.query\_store\_plan.query\_plan holds the estimated compiled plan, so that is what the plan viewer and the compare view open, with the word estimated in the tab title. Query Store's runtime statistics are separate per-interval aggregates over many executions and are never attached to individual plan operators. Why is the query list empty? Either Query Store has recorded nothing yet, or no completed interval falls inside the period you picked. A freshly enabled Query Store shows the Enabled, no history yet state until the first interval closes; a short period on a long interval length shows the message that no completed interval ends inside the recent period, which you fix by lengthening the period, moving the anchor, or ticking Include in-progress interval. Cleanup also removes old queries: retention is set on the database with the stale query threshold, not by Jam. Can Jam SQL Studio force a plan from Query Store? No. This release is read-only investigation: it runs SELECTs over the sys.query\_store\_\* catalog views, SERVERPROPERTY() and the metadata functions OBJECT\_SCHEMA\_NAME and OBJECT\_NAME, and nothing else. It never calls sp\_query\_store\_force\_plan, sp\_query\_store\_unforce\_plan, sp\_query\_store\_remove\_plan or sp\_query\_store\_flush\_db, never alters the Query Store configuration and never clears history. The Forced column in the plan list reports what the server already has; it is not a switch.