Query Store Health
Overview
Every page under the Query Store folder reads Query Store, and none of them fails when Query Store stops. The catalog views stay readable after collection ends, so a ranking over “the last 24 hours” quietly becomes a ranking over the 24 hours before it stopped, drawn in full, with a verdict under it. There is no error and no empty state, and nothing to tell that page apart from the same page yesterday.
This page is the one that checks. It opens when you click the Query Store folder in the tree, and it is built to be most useful exactly when the other pages are least trustworthy: on a store that is OFF, READ_ONLY, full, or collecting with holes in it.
The ways Query Store goes quiet
The operation mode is the loudest way and the least common. The ones that catch people out leave Query Store reading READ_WRITE throughout:
| What happens | Why nobody notices |
|---|---|
The store fills. At about 90 percent of MAX_STORAGE_SIZE_MB, size based cleanup starts discarding history. At 100 percent Query Store turns itself READ_ONLY. |
The limit was 100 MB by default before SQL Server 2019. A database configured for 30 days and holding four is not misconfigured, it is full, and no setting says so. |
| Collection has holes. Query Store only writes an interval when something ran, and writes nothing while it is off, read only, or the instance is down. | A week with a two day hole reads as a complete week on every page that sums over it. |
| Wait statistics are a separate switch. | With WAIT_STATS_CAPTURE_MODE off, Waits by Query and Load Sensitivity draw a database that did no waiting. |
| Capture is selective. AUTO, the default from SQL Server 2019, drops cheap and infrequent queries. | A query missing from the pages is not evidence that it does not run. |
Where to find it
In the tree, under a database, click the Real Time → Query Store folder itself, or open Real Time → Query Store → Query Store Health.
The Query Store folder is hidden below SQL Server 2016, and on master and tempdb, where Query Store cannot be turned on. Reaching the page another way runs the same checks and produces a message instead. The wait statistics check needs SQL Server 2017 or newer; on SQL Server 2016 it says so.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 or newer | Query Store arrived in SQL Server 2016. |
VIEW DATABASE STATE |
To read the Query Store catalog views and sys.dm_db_partition_stats. |
VIEW SERVER STATE (optional) |
For sys.dm_db_index_usage_stats, which tells an idle database apart from a stopped store. Without it that one check says it cannot tell. |
The page works whether Query Store is on, off, or read only.
The toolbar
| Control | What it does |
|---|---|
| Copy the fixes | Puts every change this page suggests for a Warning or Problem on the clipboard, as ALTER DATABASE statements. Nothing is run. |
| Turn Query Store on / Fix Query Store | Only when Query Store is off or read only and a setting would fix it. Asks for confirmation and shows the statement first. |
| Refresh | Reads everything again. |
There is no window control. Every check is about the store as a whole, and the gap check reads every interval Query Store holds, because a hole anywhere is a hole in every window that spans it.
Reading the chart
At the top, a verdict answers the only question anybody opens this page with: can the other Query Store pages be believed? It names the worst problem first, and on a store that is OFF or READ_ONLY it says how much history is still there and when it ends.
Six tiles carry the headline readings. Click one to select its row in the grid.
| Tile | What it is |
|---|---|
| Operation mode | The state in effect, and why it is read only when it is. |
| Room to grow | Days until the store reaches 90 percent of its limit at the current growth rate, “not growing” when as much ages out as arrives, or “none” when it is already past the line. |
| History on hand | How far back the oldest interval really is, against the retention setting. |
| Last written | How long ago the last interval was completed. |
| Gaps | Holes in the history, the share of it covered, and the longest hole. |
| Capture mode | The capture mode, whether wait statistics are captured, and how many queries are held. |
The gauges
The chart is two dials, each running from nothing to its own limit, so a 1,000 MB store and a 30 day retention setting can be read side by side.
Storage is the size Query Store reports against MAX_STORAGE_SIZE_MB:
- The fill is split into what is taking the space: runtime statistics, wait statistics, plans, query text, and the rest, measured from Query Store’s internal tables. The key under the dial gives each in MB.
- The amber mark at 90% is where size based cleanup starts discarding history (or, when cleanup is OFF, where nothing will make room).
- The red mark at 100% is where Query Store turns READ_ONLY.
- The hatched arc ahead of the fill is where storage will be in 30 days at the current growth rate. It is not drawn for a store that is not growing.
- The number in the middle is the percentage used, colored by how the storage check came out.
History is the days of history on hand against STALE_QUERY_THRESHOLD_DAYS. A full dial is the store holding what it was configured to hold. The blue 7 days mark shows whether the longest window most pages offer is covered. The key under it gives the last write, the gaps, and the share of the history covered.
Hover over a dial or a mark for the detail, and click it to select the matching row.
Reading the grid
One row per check, in reading order: the operation mode before the storage that decides it, and the retention setting immediately before the measurement that contradicts it. Sort by # to get that order back.
| Column | What it is |
|---|---|
| # | Reading order. |
| Check | What was looked at. |
| Status | OK, Note, Warning, or Problem. |
| This database | The setting or the measurement on this database. |
| Wanted | What a healthy store looks like. |
| What it means | What it costs the other pages to leave it as it is. |
The checks
| Check | What it looks at |
|---|---|
| Operation mode | Actual against desired state, the decoded readonly_reason, and any additional information the engine gives. |
| Storage in use | Current size against the limit. Warning from 75 percent, Problem from 90. |
| Room to grow | Growth per day, estimated from the runtime statistics rows written in the last 7 days against those due to age out in the next 7, and the days until 90 percent. Warning under 30 days, Problem under 7. |
| Size based cleanup | Whether a full store makes room or stops. |
| Retention setting | STALE_QUERY_THRESHOLD_DAYS. Note under 14 days, Warning under 7. |
| History on hand | The oldest interval against the retention setting. Short of it on a nearly full store with cleanup on is cleanup discarding history early; short of it with room is a young or recently cleared store. |
| Last written | Whether an interval has been completed within two intervals and a flush. When it has not, the last user activity on the database decides whether this is an idle database or a store that has stopped recording work that is happening. |
| Gaps in the history | Holes between consecutive intervals. Scattered short holes are a Note (an idle database looks like that); a hole of a day or more, or a run of holes covering a quarter of the history, is a Warning. |
| Capture mode | ALL is what the ranking pages need; AUTO and CUSTOM are Notes; NONE is a Problem. |
| Custom capture policy | Only for CUSTOM on SQL Server 2019 and newer: the thresholds a query must cross to be kept. |
| Wait statistics capture | Whether waits are recorded, and whether any were in the last 7 days. |
| Statistics interval | INTERVAL_LENGTH_MINUTES. A day long interval hides when anything happened. |
| Plans per query | The most plans any query holds against MAX_PLANS_PER_QUERY. |
| Forced plans | How many plans are forced, and how many have failed to force. |
Double-click a row to read the check in full. Right-click for:
| Action | What it does |
|---|---|
| Explain this check | The check, its reading, and the setting that would change it. |
| Copy the setting that fixes this | One ALTER DATABASE statement, when there is one. |
| Copy every fix as a script | The same script as the toolbar button. |
| List the gaps in the history | On the gaps row: every hole, newest first. |
| Go to Waits by Query | On the wait statistics row. |
| Go to Automatic Tuning | On the forced plans row, when plans are forced. |
| Go to Plan Regressions | On the plans per query row. |
| Copy the query behind this report | The whole batch, ready to run in SSMS. |
Where the data comes from
| Source | What it gives |
|---|---|
sys.database_query_store_options |
State, reason, sizes, retention, capture mode, cleanup mode, interval, flush interval, plans per query, and the version dependent columns. |
sys.internal_tables, sys.dm_db_partition_stats |
The size of each Query Store internal table, for the composition on the storage dial. |
sys.query_store_runtime_stats_interval, sys.query_store_runtime_stats |
Completed intervals, the rows in each, the oldest and newest, and the holes. |
sys.query_store_wait_stats |
Whether waits were recorded recently (SQL Server 2017 and newer, read only where it exists). |
sys.query_store_query, sys.query_store_plan |
Queries, plans, forced plans and forcing failures. |
sys.dm_db_index_usage_stats |
The last user read or write on a user table, converted to UTC. |
Columns that arrived after SQL Server 2016 are read through dynamic SQL after a COL_LENGTH test, so the page runs on every version from 2016. Only completed intervals are read: the interval being filled has an end time in the future.
Messages you may see
Query Store is off on this database, so nothing new reaches any Query Store page. Turning it on starts collection at once. The page still reports how much history the store holds.
Query Store is READ_ONLY and stopped collecting. The verdict gives the reason from
readonly_reason. When the reason is the size quota, the fix raises the limit and sets READ_WRITE; when the database itself is read only, nothing here fixes it.
Distrust the other Query Store pages until this is fixed. A check came out as a Problem while Query Store is on. The body says what it costs.
Room to grow: not enough history. Query Store holds less than a day of runtime statistics, so there is no rate to project from.
Last written: user tables were read or written, and Query Store is not recording the work. The store says READ_WRITE and has not completed an interval while the database was busy.
Related reports
| Report | Why you would go there |
|---|---|
| Waits by Query | Once wait capture is confirmed on. |
| Load Sensitivity | Also needs wait capture. |
| Plan Regressions | When a query is at its plans per query ceiling. |
| Automatic Tuning | To review forced plans one at a time. |
| One Time Use Queries | When capture mode ALL is filling the store with single use statements. |
| Workload Change | Needs 14 days of history to compare a week with the week before. |
Frequently asked questions
The retention setting says 30 days, but History on hand says 4. If the store is nearly full, size based cleanup is discarding history to make room; raise the limit. If the store has room, it has only been collecting for 4 days, or was cleared 4 days ago.
Why is Room to grow “not growing” on a busy database? Once a store reaches its retention setting, about as much history ages out each day as arrives. The estimate compares the two, so a store in that steady state is correctly called flat.
Why does the storage dial show more than 100%? Query Store can go over its limit before it notices, and the size it reports can be above the limit. The fill stops at the end of the scale, and the number in the middle says how far over it is.
Gaps appear every night. Is Query Store broken? Probably not. Query Store writes an interval only when something runs, so a database idle overnight has holes. A single hole of a day or more is more likely a stopped store or an instance that was down.
Does Copy the fixes change anything? No. It puts statements on the clipboard. The only thing this page runs is the Turn Query Store on or Fix Query Store button, after you confirm the statement it shows you.