Quickly Enable the Query Store for a SQL Server Database

Probably the fastest way to enable Query Store for a database is run the “ALTER DATABASE” command in a query window.

First check that the database is not already have the Query Store enabled.

select name as 'DATABASE NAME', 
CASE is_query_store_on when 1 then 'ENABLED'
else 'OTHER' END AS 'QUERY STORE STATE'
from sys.databases
where is_query_store_on = 1
order by 1 ;

In a query window, run the “ALTER DATABASE” command to enable the query store:

-- enabling Query Store for a Database
ALTER DATABASE <database name> SET QUERY_STORE = ON;

Then verify that the Query Store has been enabled for the database with the query shown above:

Note:

Unless the Query Store options are configured differently, the default options will be used. The Max Size will default to the value 100 MB. The Data Flush Interval is the default which is 900 seconds or 15 minutes. The Statistics Collection Interval default is 60 minutes. The default option for the Query Store Capture Mode is to capture all queries. Finally the Stale Query Threshold is is configured to keep the data for 30 days.