How to check Query Store settings on model database?

Question

Query Store can be enabled on the model database and ensures that every new database has the same settings as the model database.

The GUI option is missing

But it can be enabled using TSQL

ALTER DATABASE model
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Since there is no GUI, I can't check the default settings there.

Using TSQL again

USE model;
select * from sys.database_query_store_options;

Returns empty result

When I create a new database (that uses the model as a template and query the settings, it shows me the result)

create database TestQs;
go
use TestQs;
select * from sys.database_query_store_options;

Also, the settings must be saved somewhere because when I change the Query Store options, the change is propagated to new databases

ALTER DATABASE model
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 22);

I've tried to use SMO to find those options, but no luck.

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'localhost'
$sqlServer.Databases['TestQs'].QueryStoreOptions

But the same query for the model database yields nothing

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'localhost'
$sqlServer.Databases['model'].QueryStoreOptions

Is there a way to check Query Store settings on the model database without creating a new database and checking there?

Answer 1

Is there a way to check Query Store settings on the model database without creating a new database and checking there?

Nothing supported, no. QDS is not supposed to be enabled on system databases, workloads shouldn't be running in master or model, for example. I understand the point of view here that it helps with automation, though I'm not a personal fan of putting things in model (or master for that matter) and wouldn't use that approach but rather a programmatic approach so that configuration drift doesn't occur at creation time (continued drift may occur depending on variables, after creation). Still, I get your use case and viewpoint.

This seems like a bug to me. [...] - Erik Darling

The reason you get nothing back is because QDS shouldn't be enabled on system databases, thus no rowset will be returned. It's not a bug, it was implemented that way.

If you really want this to work you can edit the binary to disable the check, or hot patch with a debugger, but I wouldn't call those supported options.