Query Store Set Up SQL 2019

/*================================================================================================================================	
ScriptName		:	QueryStore_SetUp.sql
Story			:	PEGASUS-101357 DB: Setting up Query Store for Non Prod 
================================================================================================================================*/



ALTER DATABASE <DBName> -- Enter DB Name Manually Before Executing. Not going for Dynamic here, to keep it simple.
SET QUERY_STORE = ON
? ? (
? ? ? OPERATION_MODE = READ_WRITE,
?
 ? ?  CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 7 ), 
--1 Week is sufficient retention, Def: Controls the retention period of persisted runtime statistics and inactive queries, expressed in days.
? ? ? 
      DATA_FLUSH_INTERVAL_SECONDS = 600, 
--10 Minutes is to balance Memory and Disk Pressure, Also need to enable trace flag 7745. Def: Specifies maximum period during which captured runtime statistics are kept in memory, before flushing to disk.
?
 ? ?  MAX_STORAGE_SIZE_MB = <Configure based on DB Size and workload>, 
--Starting point for Setting as 1.5 Perc of Per DB Size, Def: Specifies the limit for the data space that Query Store can take inside the customer database.

? ? ? INTERVAL_LENGTH_MINUTES = 5, 
--Setting as 5 Minutes to balance concurrent execution unique plan capture and store size. Def: Size of time window during which collected runtime statistics for query plans are aggregated and persisted. Every active query plan has at most one row for a period of time defined with this configuration.

? ? ? SIZE_BASED_CLEANUP_MODE = AUTO, 
--Keeping as Auto to avoid movement to read only state. Def: whether automatic data cleanup takes place when Query Store data size approaches the limit.

? ? ? MAX_PLANS_PER_QUERY = 100, 
--Keep as 100 to balance store size and debugging potential.

? ? ? WAIT_STATS_CAPTURE_MODE = ON, 
-- We will capture the wait stats to help in resource contention debugging.

? ? ? QUERY_CAPTURE_MODE = CUSTOM,
--Except for STALE_CAPTURE_POLICY_THRESHOLD, these options define the OR conditions that need to happen for queries to be captured in the defined 
Stale Capture Policy Threshold value.

? ? ? QUERY_CAPTURE_POLICY = (
? ? ? ? STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS, 
--evaluation interval period to determine if a query should be captured.

? ? ? ? EXECUTION_COUNT = 50, 
-- Setting as 50 for the Day to capture high count execs with low times, Def:the number of times a query must executed over the evaluation period to be captured.
? ? ? ? TOTAL_COMPILE_CPU_TIME_MS = 1000, --Persisting default
? ? ? ? TOTAL_EXECUTION_CPU_TIME_MS = 100 --Persisting default
? ? ? )
? ? )
GO
/*
7745	Forces Query Store to not flush data to disk on database shutdown. Helps in a faster failover which is what is needed.


Note: Using this trace may cause Query Store data not previously flushed to disk to be lost in case of shutdown.?
For a SQL Server shutdown, the command SHUTDOWN WITH NOWAIT can be used instead of this trace flag to?
force an immediate shutdown.
*/
DBCC TRACEON (7745, -1);??
GO??

?