James Jenkins (imported from SE)
Query Store is available in SQL Server 2016+ and on Azure SQL DB it is on by default. With an on-premises SQL Server, it has to be started on each database individually.

Is there a way I can have Query Store on by default when new databases are created?
Top Answer
SqlWorldWide (imported from SE)
Query Store is not active for new databases by default.

If you turn it on for model database, it will be on for any new databases you create in the same instance.  I tested in SQL 2017.

To turn it on in model database run following:

    ALTER DATABASE model SET QUERY_STORE = ON; 

I suggest you read this article and decide if you want to turn it on for all databases and settings you want to modify.

[Best Practice with the Query Store][1]

Note: If you restore a database that did NOT already have query store = on, it will remain off until activated. Model will only start Query Store on new databases created on the instance.
 


  [1]: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.