Query Store not enabled

What this check looks for

Databases that are online and writable and do not have Query Store turned on: no row in sys.database_query_store_options at all, or a row whose actual_state_desc is OFF.

This check only runs on SQL Server 2019 and newer. Query Store itself has been available since SQL Server 2016, but the check is scoped to 2019+ deliberately.

Left out of scope, on purpose:

  • tempdb — Query Store is not supported there.
  • master, model, msdb — the system databases, not a typical Query Store target.
  • Read only databases — the ALTER DATABASE ... SET QUERY_STORE that would turn it on fails on one, so flagging it would recommend an action that cannot run.
  • Availability group secondaries — Query Store settings come from the primary replica and reach the secondary from there; changing it locally is not possible.

Why it matters

Query Store is SQL Server’s flight recorder for query plans. Once it is on, it keeps a history of the plans a query has used and how each one performed, interval by interval. Without it, the only view of “did this get slower” is whatever happens to still be in the plan cache, which can be minutes old.

That history is what several other reports and checks in this product are built on: Plan Regressions, Waits by Query, and the running-at-older-compatibility-level check on this same report leans on Query Store to make a compatibility level change safe to attempt. A database that has never collected any of this has none of that safety net, and the first sign of a regression is usually a support ticket rather than a chart.

It is also inexpensive to turn on. The action this check offers uses the same defaults the product’s own Query Store reports use: a 2048 MB quota, QUERY_CAPTURE_MODE = ALL so nothing cheap-but-frequent is dropped, automatic size based cleanup, and a 30 day stale query threshold. Query Store starts empty, so there is nothing to see until it has been collecting for a window, but there is no downtime or maintenance window required to turn it on.

How to confirm it yourself

sys.database_query_store_options is scoped to whatever database it is queried from — there is no single cross-database view for it. The check itself loops every database with a cursor and dynamic SQL for exactly that reason. The quickest whole-instance view is the flag SQL Server surfaces on sys.databases:

SELECT [name], [is_query_store_on]
  FROM sys.databases WITH (NOLOCK)
 WHERE [database_id] > 4
 ORDER BY [name];

And, inside a specific database, the detail behind that flag:

USE [YourDatabase];
GO
SELECT [actual_state_desc], [desired_state_desc], [readonly_reason]
  FROM sys.database_query_store_options;

How to fix it

From Quick Scan, right click the finding. Two actions are offered:

  • Turn Query Store on for <database>… — runs the statement below against that one database, after a confirmation dialog that names every setting it changes.
  • Turn Query Store on for all databases on this instance… — runs the same statement for every database this check flagged, in one pass, after a single confirmation naming how many databases are affected. Follows the instance the same way the Autoclose and Autoshrink fixes on this report do, and is offered on other connected instances afterward.

The statement either action runs, per database:

ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 2048,
    QUERY_CAPTURE_MODE = ALL,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

CLEANUP_POLICY has to be written as a nested clause exactly like this. Written flat alongside the other options, the way most abbreviated examples show it, it is a syntax error on every version from 2016 to 2022.

How long it takes

About an hour, most of it waiting: the ALTER DATABASE itself is immediate, but Query Store starts empty and the reports built on it have nothing to say until it has been collecting for a representative window, ideally including a normal business cycle.

Report Why you would go there
Plan Regressions The main reason to turn this on: finding a plan that got worse.
Waits by Query Needs Query Store’s runtime stats to attribute waits to a query.
Query Store Health Readiness, storage and capture settings for a database once it is on.
Check
Running at older compatibility level Recommends Query Store be on before raising the level, so a regression can be found and rolled back.
Database set to Autoclose Another Quick Scan finding with a “fix for one database or all databases” pair of actions.

Frequently asked questions

Why 2019 and not 2016, where Query Store actually shipped? This check is scoped to SQL Server 2019 and newer by design, not because Query Store needs it.

Does turning this on change any query plans? No. Query Store only records what already happens; it does not change how a query is optimized or executed. CLEANUP_POLICY, capture mode and the quota only control how much history is kept.

What happens once the quota fills up? Query Store flips to READ_ONLY and keeps reporting itself as enabled while collecting nothing new. That state is what the Query Store Health report and its own “fix” actions are for, not this check — this check only looks at databases that are still fully off.

Will this recommend turning it on for an availability group secondary? No. Query Store settings on a secondary come from the primary, so the check skips secondaries entirely rather than recommend a change that cannot be made locally.