Query Store Status for Every Database in SQL Server

Query Store status for every database takes two queries, and the first query isn’t enough.

Why Check Every Database

A web hosting company ran thousands of small databases on one powerful server. Performance had been fine for a year. Then it dropped, even when the server wasn’t busy. One database, busy with dynamic queries, was the first suspect, and soon nearly 100 others showed the same symptoms.

The next question was what had changed. The team had recently turned on Query Store for some databases. A query against sys.databases listed which ones, and all the slow databases had it on. Turning it off for them, one at a time, brought performance back.

The team then tested more. Query Store for 100 or more databases needed a more powerful server and some tuning. After a hardware upgrade, they enabled it again, one important database at a time. Query Store isn’t bad. Like any feature, it takes resources, so check the capacity first.

Set Up Four Demo Databases

The demo creates four databases. One keeps the default. One has Query Store off. One has Query Store in READ_ONLY mode. The last one is a read-only database. From SQL Server 2022 on, a new database starts with Query Store on, because the model database has it on. On SQL Server 2019 and earlier, add ALTER DATABASE QsStatusDemoOn SET QUERY_STORE = ON; after the CREATE.

IF DB_ID(N'QsStatusDemoOn')     IS NULL CREATE DATABASE QsStatusDemoOn;
IF DB_ID(N'QsStatusDemoOff')    IS NULL CREATE DATABASE QsStatusDemoOff;
IF DB_ID(N'QsStatusDemoFrozen') IS NULL CREATE DATABASE QsStatusDemoFrozen;
IF DB_ID(N'QsStatusDemoLocked') IS NULL CREATE DATABASE QsStatusDemoLocked;
GO
ALTER DATABASE QsStatusDemoOff    SET QUERY_STORE = OFF;
ALTER DATABASE QsStatusDemoFrozen SET QUERY_STORE = ON (OPERATION_MODE = READ_ONLY);
ALTER DATABASE QsStatusDemoLocked SET READ_ONLY WITH ROLLBACK IMMEDIATE;

The Quick Check

The classic Query Store status check reads one column of sys.databases.

SELECT name, is_query_store_on
FROM sys.databases
WHERE name LIKE N'QsStatusDemo%'
ORDER BY name;

Three databases show 1. That reads as three databases collecting data. It isn’t true. One is frozen on purpose, and one can’t write anything, because the database itself is read-only. The column says only that Query Store isn’t off.

The Real Status of Every Database

Each database keeps its own Query Store options in sys.database_query_store_options. You can’t read that view for all databases in one query. The next script builds one query per database, joins them with UNION ALL and runs the result. It skips databases you can’t open. The demo filter keeps it to the four demo databases. To scan the server, delete the condition AND d.name LIKE N'QsStatusDemo%' and keep the closing parenthesis and semicolon.

DECLARE @sql nvarchar(max) = (
    SELECT STRING_AGG(CONVERT(nvarchar(max),
           N'SELECT N' + QUOTENAME(d.name, '''') + N' AS DatabaseName, o.desired_state_desc, o.actual_state_desc, o.readonly_reason, o.max_storage_size_mb FROM '
           + QUOTENAME(d.name) + N'.sys.database_query_store_options AS o'), N' UNION ALL ')
    FROM sys.databases AS d
    WHERE d.database_id > 4 AND d.state = 0 AND HAS_DBACCESS(d.name) = 1
      AND d.name LIKE N'QsStatusDemo%');
SET @sql += N' ORDER BY DatabaseName;';
EXEC (@sql);

Read two columns together. The desired state is what someone asked for. The actual state is what Query Store does. QsStatusDemoLocked wants READ_WRITE but runs READ_ONLY, and the reason code 1 means its database is read-only. A Query Store that reaches its storage limit also switches to READ_ONLY on its own, with reason 65536. That case never shows in the quick check.

The script works on databases with a case-sensitive collation too. A server can hold databases with different collations. A script that joins names from several databases can fail on that.

What Query Store Costs

Every database with Query Store holds its own memory and its own background work. You can see the memory side in the memory clerks.

SELECT CAST(SUM(pages_kb) / 1024.0 AS decimal(8, 1)) AS QueryStoreMemoryMB
FROM sys.dm_os_memory_clerks
WHERE type LIKE N'MEMORYCLERK_QUERYDISKSTORE%';

On the test server, one run returned 10.6 MB with more than 40 databases on. Expect your figure to differ, because it grows with the number of databases and with their activity. Run it before and after you switch Query Store on for a group of databases. Don’t enable it on a hundred databases at once.

Keep SQL Server patched, too. An older bug in SQL Server 2016 and 2017 let Query Store block log truncation. A cumulative update fixed it. Query Store also needs its settings reviewed, such as the capture mode and the size limit.

Turn It On or Off in Bulk

Once you know the status, the next step is to change it for a group. Query Store On or Off for Every Database: Review First shows a script that does it. Do you use an availability group? Read Disable Query Store on an Always On Database: What Blocks It first.

New databases copy the model database, and model has Query Store on.

SELECT name, is_query_store_on
FROM sys.databases
WHERE name = N'model';

Every new database on this server starts with Query Store on. On a host that creates databases all day, every new database then runs Query Store.

You could argue that is_query_store_on is enough, because on or off is all most people need. It is enough for a first look. A frozen Query Store collects nothing and raises no error, and an empty report is the first sign. The second query finds that case before the report does.

What to Remember

Check Query Store status in two steps. Read the flag in sys.databases, then read the desired and actual state of each database. Watch the reason code. Enable Query Store where you need it, and measure the cost before you add more databases.

When you finish, drop the demo databases.

USE master;
GO
DROP DATABASE IF EXISTS QsStatusDemoOn;
DROP DATABASE IF EXISTS QsStatusDemoOff;
DROP DATABASE IF EXISTS QsStatusDemoFrozen;
DROP DATABASE IF EXISTS QsStatusDemoLocked;

A Query Store flag is not the status, it is only the first half of it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.