How to check Query Store settings on model database?

6 votes

1 answer

808 views

sql-server sql-server-2019 query-store smo

                          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?

Asked by Zikato (5724 rep)

Sep 9, 2021, 08:52 AM
Last activity: Sep 9, 2021, 03:53 PM

How to check Query Store settings on model database?

Related Questions