Setting up SQL Server Query Store and checking it in production

In short

Check the actual state first, not the setting you expect: desired and actual state, capture mode, used and maximum storage, cleanup and the history that really exists. Query 1 returns all of that in one row. Then decide which questions Query Store must answer later: which workloads, which period, which queries. Capture mode, storage limit and retention follow from that. Change the settings deliberately and then check that the expected queries actually arrive. A full store or a read-only state only gets noticed if someone looks.

What Query Store records and what it doesn't

Query Store works per database and stores its data in the database itself. It records:

• the query text as SQL Server compiles it, that is, already rewritten for auto-parameterized queries,
• the plans used for that query, as the XML showplan of the compiled plan, without actual row counts,
• runtime figures per interval: execution count, duration, CPU, logical and physical reads, memory grant, each as average, minimum, maximum, last value and standard deviation,
• wait times by category such as locks, CPU or buffer I/O, from SQL Server 2017.

That tells you whether a query has used a different plan since a certain point, whether it does more work per execution or whether it simply runs more often. That is what Query Store is built for, and it is our most important source for the runtime history of a database. SQL Server also keeps forced plans and Query Store hints there.

What Query Store does not capture

• No individual executions. The figures are aggregated per interval and plan. The parameter values of one particular slow execution are not in there. The stored plan only contains the values it was compiled with.
• No blocking chain. The category "Lock" says that a query waited on locks, not on whom. For that there is the blocking diagnosis, and for deadlocks the deadlock graph.
• No DDL plans. In an earlier PSG lab, a TRUNCATE had no entry of its own; its wait time ended up on the calling statement. Details in Where does the time go?.
• Nothing that ran before capture. If you switch Query Store on today, your history starts today. In AUTO mode, the beginning of every fast query is missing as well, as the lab below shows.

Check the actual state of your database

Query 1 reads the settings and state of the current database and adds how far back the existing history really goes. Run it in every database you want to check. The finding column summarizes what to look at first.

/* PSG · Query Store · Query 1: state, capture and the history that actually exists   Run it in the database you want to check (Query Store works per database).   Reads catalog views only. Changes no setting and deletes nothing.   SQL Server 2017 and later. Permissions: VIEW DATABASE STATE; from SQL Server 2022, VIEW DATABASE PERFORMANCE STATE is enough.   OPTION (MAXDOP 1): a diagnostic query should not occupy parallel workers, least of all on a busy server. */SELECT    DB_NAME()                                        AS database_name,    CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(20)) AS version,    o.desired_state_desc                             AS desired,    o.actual_state_desc                              AS actual,    o.readonly_reason,    (SELECT STRING_AGG(r.reason, N'; ')     FROM (VALUES (1, N'database is read-only'), (2, N'single-user mode'), (4, N'emergency mode'),                  (8, N'secondary replica'), (65536, N'MAX_STORAGE_SIZE_MB reached'),                  (131072, N'internal memory limit: number of statements'),                  (262144, N'internal memory limit: data waiting to be persisted (temporary)'),                  (524288, N'database reached its disk size limit')) AS r (bit, reason)     WHERE o.readonly_reason & r.bit = r.bit)          AS reason,    o.query_capture_mode_desc                        AS capture,    o.current_storage_size_mb                        AS used_mb,    o.max_storage_size_mb                            AS max_mb,    CAST(100.0 * o.current_storage_size_mb / NULLIF(o.max_storage_size_mb, 0) AS decimal(5, 1)) AS used_percent,    o.size_based_cleanup_mode_desc                   AS size_based_cleanup,    o.stale_query_threshold_days                     AS retention_days,    h.oldest_interval,    CAST(DATEDIFF(hour, h.oldest_interval, SYSDATETIMEOFFSET()) / 24.0 AS decimal(6, 1)) AS history_days,    o.interval_length_minutes                        AS interval_min,    o.flush_interval_seconds                         AS flush_s,    o.wait_stats_capture_mode_desc                   AS waits,    (SELECT COUNT(*) FROM sys.query_store_query)     AS queries,    (SELECT COUNT(*) FROM sys.query_store_plan)      AS plans,    CASE        WHEN o.actual_state_desc = N'ERROR' THEN N'Error state: Query Store is not writing.'        WHEN o.actual_state_desc = N'OFF' THEN N'Query Store is off: no history is being kept.'        WHEN o.desired_state_desc <> o.actual_state_desc THEN N'Desired and actual differ: read the reason column.'        WHEN o.actual_state_desc = N'READ_ONLY' THEN N'Read-only on purpose: no new data is added.'        WHEN o.query_capture_mode_desc = N'NONE' THEN N'New queries are not captured.'        WHEN o.current_storage_size_mb >= 0.9 * o.max_storage_size_mb AND o.size_based_cleanup_mode_desc = N'OFF'            THEN N'Storage almost full and no size-based cleanup: READ_ONLY is coming.'        WHEN o.current_storage_size_mb >= 0.9 * o.max_storage_size_mb            THEN N'Storage at the limit: cleanup is deleting history.'        ELSE N'Capturing. Is the history long enough for your questions?'    END                                              AS findingFROM sys.database_query_store_options AS oCROSS JOIN (    SELECT MIN(i.start_time) AS oldest_interval    FROM sys.query_store_runtime_stats_interval AS i    WHERE EXISTS (SELECT 1 FROM sys.query_store_runtime_stats AS rs                  WHERE rs.runtime_stats_interval_id = i.runtime_stats_interval_id)) AS hOPTION (MAXDOP 1);

In the lab, Query 1 returned this row on SQL Server 2022 for a database whose storage limit had been exceeded, shown vertically and with some columns omitted:

database_name       QsFull
version             16.0.4295.3
desired             READ_WRITE
actual              READ_ONLY
readonly_reason     65536
reason              MAX_STORAGE_SIZE_MB reached
capture             ALL
used_mb             31
max_mb              10
used_percent        310.0
size_based_cleanup  OFF
retention_days      30
history_days        .0
queries             6111
finding             Desired and actual differ: read the reason column.

The 10 MB limit is a lab value so that the case happens within minutes. Query 1 ran without errors on SQL Server 2017, 2019, 2022 and 2025, including with Query Store switched off and with an empty history.

How to read the key columns:

• desired and actual are two different things. desired is what was configured, actual is what Query Store is doing right now. If they differ, reason names the cause. More on that below in Why Query Store is READ_ONLY.
• used_mb and max_mb show how close the limit is. The used value can lag behind reality for a while, because Query Store only checks the limit when it writes to disk. In the lab it stood at three times the limit that way.
• retention_days and history_days: the first is the configured retention, the second reaches back as far as runtime data actually exists. If history_days is much smaller although Query Store has been running longer, size-based cleanup has already deleted data, Query Store was off or read-only in between, someone cleared it, or it simply hasn't been running that long.
• capture decides which queries get in at all. The next section shows what that means in practice.

Under ALL, by the way, Query 1 itself lands in the Query Store of the database you run it in. That is harmless, but don't be surprised to find it there later.

Know the defaults and your database's starting state

Which settings a database has depends on how it came into being. In the lab we created a new database on each version, read model, and restored a database created on SQL Server 2019 onto 2022 and 2025:

The same on all four versions: interval 60 minutes, writing to disk every 900 seconds, retention 30 days, size-based cleanup on, wait capture on.

Two things stand out. First, a new database inherits its Query Store state from model; from SQL Server 2022 on, model is set to READ_WRITE. Second, a restored database arrives with its own state. The database from SQL Server 2019 stayed OFF on 2022 and 2025, although new databases there would have had Query Store switched on. Its compatibility level stayed at 150 as well. After an upgrade via backup and restore, your most important database may therefore have no Query Store, while every new test database has one.

Right before an upgrade, that is the wrong state. To compare before and after, Query Store must already have been recording on the old version. The full procedure is described in Will an upgrade make our application faster?.

Match the capture mode to what you need to diagnose

The capture mode determines which queries Query Store takes in. What must be captured follows from the questions you will ask later: knowing which queries were expensive needs something different from knowing which queries an application runs at all.

ALL, AUTO, CUSTOM and NONE in the lab

On each version we ran the same workload in separate databases, one per mode:

• one frequent, fast query 50 times,
• the same kind of query exactly 1, 5, 10, 29, 30, 31 and 60 times, each with its own text,
• 100 different fast queries once each,
• one expensive query once, about one second of CPU.

Then we counted how many of these executions Query Store had recorded. SQL Server 2019 and 2022 returned identical numbers:

The numbers are the executions Query Store had recorded afterwards. A dash means the query is not in Query Store.

ALL captures every query from its first execution. It is the default on SQL Server 2017.

AUTO, the default from SQL Server 2019, captured the CPU-intensive query right away. For the fast queries, counting only began with the 30th execution: of 50 executions Query Store had recorded 21, of 60 it had 31, of 29 none. The first 29 executions of every fast query are missing for good; they are not added later when the query reaches the threshold. The 100 rare queries did not appear at all. This matches the thresholds Microsoft lists as the defaults for CUSTOM mode: 30 executions, one second of compile CPU or 100 ms of execution CPU, each summed up within one day. For AUTO itself, Microsoft describes the thresholds as internally determined, and SQL Server 2017 shows that they can change: there, AUTO captured from the third execution, so 48 of 50. Queries executed only once were missing there too.

CUSTOM (from SQL Server 2019) lets you set your own thresholds. With EXECUTION_COUNT = 5, Query Store counted from the fifth execution.

NONE takes in no new queries but keeps counting known ones. In the lab, the frequent query went from 50 to 100 executions after switching, and a new query did not appear. So NONE is not the same as off. Microsoft advises using NONE only for a specific purpose.

What follows? AUTO keeps the store small and does not lose a CPU-intensive query. Because the thresholds depend on CPU and execution count, a rarely running query that waits a long time but uses little CPU may stay below them; we did not measure that separately. For the questions "How often does this query really run?" or "Which queries does the application generate?", AUTO gives numbers that are too small and an incomplete list. If you compare execution counts, know this before you read a frequency off a Query Store report. ALL gives complete numbers, but for applications with many different query texts it can be expected to use more storage. There is no standard recommendation for every production system.

Interval, flush and wait statistics

• Interval: runtime figures are aggregated per interval, 60 minutes by default. Allowed values are 1, 5, 10, 15, 30, 60 and 1440 minutes. Shorter intervals show peaks more precisely but produce more rows.
• Flush: Query Store first collects in memory and writes to disk every 900 seconds by default. Only then is the storage limit checked. sys.sp_query_store_flush_db writes early. Whatever was not yet written when the server crashes is missing afterwards.
• Wait statistics: on by default from SQL Server 2017. They are aggregated by category and do not show a blocking chain.

Sizing storage, retention and cleanup

Query Store limits its space with MAX_STORAGE_SIZE_MB: 100 MB up to SQL Server 2017, 1,000 MB from 2019. Two mechanisms keep the store below that limit:

• Retention (STALE_QUERY_THRESHOLD_DAYS, default 30 days) removes data older than that.
• Size-based cleanup (SIZE_BASED_CLEANUP_MODE = AUTO, default) starts, according to Microsoft, at about 90% of the limit and deletes down to about 80%, cheapest and oldest queries first.

In our experience, how much space you need depends mainly on the number of different query texts and plans, plus interval length and retention, and hardly on the database size. In the lab, about 6,100 different queries with a join, each run once, took about 30 MB on SQL Server 2022 and 2025: roughly 5 KB per query with its plan and one runtime row. Frequently executed queries add rows per interval. An application that writes values straight into the SQL text instead of using parameters creates a new query for every new value and fills Query Store accordingly fast.

What happens when the limit is too tight

To see the behavior at the limit, we deliberately set it to 10 MB in the lab and, after the workload, ran 6,000 different queries, once without and once with size-based cleanup. 10 MB is a lab value, not a size for production.

Without cleanup, Query Store went READ_ONLY on all four versions. SQL Server 2022 and 2025 had captured all 6,000 queries by then and stood at 31 and 30 MB, just over three times the limit: the size is only checked when writing to disk. SQL Server 2017 and 2019 stopped earlier, at about 3,500 instead of 6,000 queries and a reported 17 to 20 MB, with reason 262144, the internal limit for data not yet written. There, 15 to 17% of the queries were missing completely in the end.

With cleanup, the same happened at first: READ_ONLY at about 30 MB. Then cleanup began deleting, between two of our measurement points 15 seconds apart a few hundred to almost 2,000 queries each time:

On three of four versions, not a single query was left in the end, not even the frequent and the expensive one. Only then was Query Store back to READ_WRITE. Instead of stopping at 80%, the history was empty. On SQL Server 2019 cleanup ran more slowly and, by the end of our observation, had removed the cheap queries first, as Microsoft describes. On all versions, used_mb dropped much more slowly during deletion than the number of queries. Our guess, not verified further: cleanup goes by this value and therefore does not notice in time that it has reached its target.

This is an edge case with a tiny limit exceeded threefold. But it shows what matters: the limit must fit the number of different queries, and it should be sized so that cleanup stays the exception. And history_days in Query 1 is the number that shows whether your history really reaches back as far as planned. In an earlier PSG lab under load, a size-based cleanup took 30 to 55 seconds, and application response times were higher during that time, more so under an availability group than on a single server. Those were single runs; we did not isolate the exact cause there. So cleanup is work you would rather have rarely.

Retention starts with your questions

If a problem only occurs at month-end close, the last month-end close must be in the history, preferably the last two. If you want to compare after a release, the time before it must still be there. A configured number of days does not guarantee that, because size-based cleanup can delete earlier. So after a full business cycle, check whether history_days reaches the configured retention.

Why Query Store is READ_ONLY

"Query Store should write" and "Query Store is writing" are two different statements. Query 1 shows both side by side. If actual says READ_ONLY although desired says READ_WRITE, readonly_reason gives the reason. The value is a bitmask; several reasons can apply at once. Query 1 breaks it down in the reason column:

In the lab we saw three of these reasons:

• Database read-only (1): after ALTER DATABASE … SET READ_ONLY, Query Store went READ_ONLY with reason 1 and returned on its own within seconds after SET READ_WRITE.
• Storage limit reached (65536): after raising MAX_STORAGE_SIZE_MB from 10 to 40 MB, Query Store returned to READ_WRITE on its own on all four versions. An extra OPERATION_MODE = READ_WRITE was not needed. In the lab this took 16 to 61 seconds because we had it write every 60 seconds. With the default interval of 900 seconds it can take up to a quarter of an hour until Query 1 shows READ_WRITE again.
• Internal memory limit (262144): on SQL Server 2017 and 2019, Query Store reported this reason instead of 65536 during the fill, when many new queries arrived at once, and took in no new ones from then on. Microsoft describes the state as temporary. In the run without cleanup it stayed until the limit was raised; in the run with cleanup it turned into 65536.

Besides READ_ONLY, actual can also be ERROR. Query Store is not writing then either, and readonly_reason does not help. Query 1 reports this case separately; Microsoft describes how to fix it in its best practices for Query Store. We did not produce ERROR in the lab.

What matters is what happens in between: during the read-only phase we ran the frequent query 20 times and a new query 20 times. Afterwards Query Store had recorded neither the 20 additional executions nor the new query. After returning to READ_WRITE it counted again, but the gap remained. The application gets no error. Without a regular look at Query 1, such a gap only shows up when you need the data.

That gives you the order:

1. Save the state with Query 1 before you change anything.
2. Read the reason. More storage is the right fix for reason 65536, not for a read-only database or a secondary replica.
3. Make the matching, limited change.
4. Run Query 1 again after the next write: is actual back to READ_WRITE?
5. Use Query 2 to check whether a known query gets new executions again.

ALTER DATABASE … SET QUERY_STORE CLEAR is not a repair. It deletes the entire history, and no setting brings it back. The forced plans attached to the stored plans go with it.

Why an expected query is missing

You are looking for a query in Query Store and can't find it. Check in this order:

1. Database and period. What counts is the database context the statement runs in. In the lab, an ad hoc query that accessed the target database from master was missing from the target's Query Store. A procedure of the target database, called from master, did appear there. Query Store cannot be enabled in master and tempdb.
2. State. Was Query Store off or read-only at the time in question? history_days and actual from Query 1 give the first hint.
3. Capture mode. Under AUTO, fast, rare queries don't appear, and from SQL Server 2019 the first 29 executions are missing.
4. Retention and cleanup. Older or cheap queries may already be deleted.
5. Text and context. The same statement can sit under several entries, or its text looks different from the code.

Query 2 checks the fifth point. It searches for a text fragment and shows, for each match, whether it is an ad hoc query or part of a procedure, under which SET context it ran and how often. The text search reads all stored query texts; with a Query Store in the gigabyte range that takes time. Run it when you need it, not on a monitoring job's schedule.

/* PSG · Query Store · Query 2: where is my query?   Searches the captured query texts of the current database for a fragment and shows   under which entries (ad hoc, procedure, SET context) the same statement is stored.   Reads catalog views only. Changes nothing. SQL Server 2017 and later; permissions as for query 1.   Caution: query texts can contain confidential literals and names. Do not pass the result on unchecked.   OPTION (MAXDOP 1): a diagnostic query should not occupy parallel workers. */DECLARE @search nvarchar(400) = N'%Orders%';  -- fragment of the query text, with % before and after SELECT TOP (50)    q.query_id,    CASE WHEN q.object_id = 0 THEN N'(ad hoc)'         ELSE ISNULL(OBJECT_SCHEMA_NAME(q.object_id) + N'.' + OBJECT_NAME(q.object_id),                     N'(object ' + CAST(q.object_id AS nvarchar(20)) + N', name not visible)') END AS object_name,    q.context_settings_id                                    AS context,    CASE WHEN CAST(cs.set_options AS bigint) & 4096 = 4096 THEN N'ON' ELSE N'OFF' END AS arithabort,    s.plans,    s.executions,    s.first_execution,    s.last_execution,    REPLACE(REPLACE(LEFT(qt.query_sql_text, 150), CHAR(13), N' '), CHAR(10), N' ') AS query_textFROM sys.query_store_query_text AS qtJOIN sys.query_store_query AS q ON q.query_text_id = qt.query_text_idJOIN sys.query_context_settings AS cs ON cs.context_settings_id = q.context_settings_idOUTER APPLY (    SELECT COUNT(DISTINCT p.plan_id)    AS plans,           SUM(rs.count_executions)     AS executions,           MIN(rs.first_execution_time) AS first_execution,           MAX(rs.last_execution_time)  AS last_execution    FROM sys.query_store_plan AS p    LEFT JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id    WHERE p.query_id = q.query_id) AS sWHERE qt.query_sql_text LIKE @searchORDER BY s.last_execution DESCOPTION (MAXDOP 1);

In the lab we ran the same sum query in four ways: twice parameterized through sp_executesql, once with ARITHABORT ON and once with OFF, once inside the procedure dbo.GetCustomerTotal, and once as ad hoc text with a leading comment and the literal value 7. Query 2 found four entries:

query_id  object_name           context  arithabort  executions  query_text
116       (ad hoc)              3        ON          1           (@1 tinyint)SELECT SUM([Amount]) [w_ctx2] FROM [dbo].[Orders] WHERE [CustomerId]=@1
115       dbo.GetCustomerTotal  3        ON          1           (@CustomerId int)SELECT SUM(Amount) AS w_ctx FROM dbo.Orders WHERE CustomerId = @CustomerId
114       (ad hoc)              1        OFF         1           (@CustomerId int)SELECT SUM(Amount) AS w_ctx FROM dbo.Orders WHERE CustomerId = @CustomerId
113       (ad hoc)              3        ON          1           (@CustomerId int)SELECT SUM(Amount) AS w_ctx FROM dbo.Orders WHERE CustomerId = @CustomerId

Columns plans, first_execution and last_execution omitted; run on SQL Server 2022 with @search = N'%w[_]ctx%'. The other versions returned the same four entries.

• Two entries for the same text: ARITHABORT ON and OFF produce different contexts and therefore different query_ids. SQL Server Management Studio sets ARITHABORT ON; applications using ODBC or .NET usually don't. In the lab, a procedure call through sqlcmd without its own setting ran with OFF. If you reproduce a slow application query in SSMS, you may be looking at the wrong entry and the wrong plan.
• A separate entry for the procedure, recognizable by the object name.
• A rewritten text: SQL Server auto-parameterized the ad hoc query. Query Store holds it as (@1 tinyint)SELECT SUM([Amount]) [w_ctx2] FROM [dbo].[Orders] WHERE [CustomerId]=@1, without the comment, with square brackets and without the value 7. A search for CustomerId = 7 won't find it. So search for a table or column name, not for literals, spacing or comments.

Permissions for both queries

Both queries read Query Store views and need a read permission on the database for that. We tested both with a separate user without further permissions. A user without that permission got message 262 on all versions, verbatim:

Msg 262, Level 14, State 1, Server …, Line 6
VIEW DATABASE STATE permission denied in database 'QsALL'.

That was the wording on SQL Server 2017 and 2019. On 2022 and 2025 the same message names VIEW DATABASE PERFORMANCE STATE. VIEW DATABASE STATE was enough on all four versions. From SQL Server 2022, the narrower VIEW DATABASE PERFORMANCE STATE alone was sufficient. For a monitoring account that is the better choice, because it grants access to the performance views only.

Without permission to see object definitions (VIEW DEFINITION), the user doesn't know the procedure names. Query 2 then shows (object 965578478, name not visible) instead of dbo.GetCustomerTotal. The other columns stay complete.

Change settings and check the result

-- Changes the Query Store configuration of the database. Values are examples.ALTER DATABASE CURRENT SET QUERY_STORE = ON (    OPERATION_MODE = READ_WRITE,    QUERY_CAPTURE_MODE = AUTO,              -- ALL if you need complete execution counts    MAX_STORAGE_SIZE_MB = 2048,             -- size it to the number of different queries    SIZE_BASED_CLEANUP_MODE = AUTO,    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 45),   -- at least one full business cycle    INTERVAL_LENGTH_MINUTES = 60,    WAIT_STATS_CAPTURE_MODE = ON);

The statement requires ALTER permission on the database. We ran it unchanged on all four versions. Afterwards, check in three steps:

1. After the first write (at the latest after the flush interval): Query 1. Do desired and actual match?
2. After a typical working day: Query 2 for two or three known queries. Do they arrive, and do the execution counts roughly match what you expect?
3. After a business cycle: Query 1 again. Does history_days reach as far as planned, and how far has used_percent climbed?

For every change, write down what applied before, what you expect and when you will look again. Going back is easy as long as only settings are involved. Deleted history cannot be restored, though.

And the overhead? In an earlier PSG lab with 2,400 operations per second, mostly short reads with few different queries, the median p99 response time across 18 short runs was 20.6 ms without Query Store and 21.4 to 21.6 ms with it. That is one test setup with plenty of spare CPU, not a promise for every workload. We did not measure applications with very many different query texts there. Cleanup can be more expensive than capture, see above.

Treat query texts and plans as confidential

Query Store contains your application's query texts, with table and column names, comments and every value written directly into the text. The stored plans also contain the parameter values they were compiled with. Treat extracts and exports like application data: only for authorized people, not in public forums, not unchecked in tickets. The fact that Query Store keeps no complete parameter history does not make the data it has harmless.

From capture to analysis

This article ends where the actual diagnosis begins. Once Query Store is recording reliably, these articles take you further:

• SQL Server is sometimes fast and sometimes slow: narrowing down plan changes and parameters with Query Store.
• Where does the time go?: how Query Store counts triggers, procedures and functions.
• Will an upgrade make our application faster?: comparing before and after with Query Store.
• One query, many plans: why the optimizer picks a particular plan.

For a look back across many databases and weeks, PSG QX analyzes the Query Store of your databases on request: which query, which plan, since when. QX does not record anything new itself. It needs a Query Store that was recording beforehand. That is exactly why the check in this article pays off before things get urgent.

Frequently asked questions

Can I just switch Query Store on? Technically yes, with one statement. It becomes useful when you first read the actual state and decide which history you need. Then you choose capture mode, storage limit and retention and check that the expected queries arrive. There is no universal configuration for every application.

Does Query Store slow down my server? In our load lab, the 99th percentile was about one millisecond higher with Query Store, with few different queries and plenty of spare CPU. Size-based cleanup can be more noticeable when the limit is too tight and many queries are deleted at once. An adequate storage limit is therefore also a performance question.

Why is my query not in Query Store? Check database and period, the actual state, the capture mode, retention and cleanup, and finally the text. Under AUTO, fast, rare queries don't appear. Auto-parameterization rewrites the text. A missing entry does not prove that the query never ran.

Does more storage fix every READ_ONLY state? No. More storage is the right fix for reason 65536. A read-only database, a secondary replica or an internal memory limit needs a different answer. Read readonly_reason before you change anything.

Does a database take its Query Store along through backup and restore? According to Microsoft, yes, because Query Store lives in the database. In the lab we checked the state: a database from SQL Server 2019 arrived on 2022 and 2025 with OFF, although new databases there have Query Store switched on. Check with Query 1 after every move.

Is Query Store data anonymous? No. Query texts and plans can contain table names, values and compile-time parameters. Treat access and export like application data.

When the history is missing just when you need it

Often it only turns out during an incident that Query Store was off, had been read-only for days or that cleanup had already deleted the decisive week. For a joint investigation, it helps to have:

• the output of Query 1 from the affected databases,
• the period in question and what was different during it: a release, an upgrade, month-end close,
• two or three queries you expect in Query Store, with the output of Query 2.

With that, we quickly see whether the history is enough for the question, and if not, what must be captured before it happens again.

Sources

• Microsoft: Monitor performance by using the Query Store
• Microsoft: Manage the Query Store
• Microsoft: sys.database_query_store_options
• Microsoft: ALTER DATABASE SET options, Query Store
• Microsoft: Best practices for managing the Query Store

Read on: SQL Server is sometimes fast and sometimes slow · Where does the time go? · Will an upgrade make our application faster? · SQL Server performance tuning