Query Store Best Practices

enable_query_store.sql

USE [master]
GO
ALTER DATABASE [<db_name>] SET QUERY_STORE = ON
GO
ALTER DATABASE [<db_name>] SET QUERY_STORE (
OPERATION_MODE = READ_WRITE, 
DATA_FLUSH_INTERVAL_SECONDS = 3600, 
MAX_STORAGE_SIZE_MB = 5120,
INTERVAL_LENGTH_MINUTES = 60,
SIZE_BASED_CLEANUP_MODE = AUTO,
QUERY_CAPTURE_MODE = AUTO,
MAX_PLANS_PER_QUERY = 50)
GO 

--TF can used to improve performance of Query Store during High Availability and Disaster Recovery scenarios
--TF7745 will prevent the default behavior where Query Store writes data to disk before SQL Server can be shutdown.
--TF7752 enables asynchronous load of Query Store, and also allows SQL Server to run queries before Query Store has been fully loaded. Default Query Store behavior prevents queries from running before the Query Store has been recovered.
--Starting with SQL Server 2019 (15.x), this behavior is controlled by the engine, and trace flag 7752 has no effect.

DBCC TRACEON (7745, 7752, -1);

SELECT * FROM sys.database_query_store_options;