You have a database named DB1 that stores more than 700 gigabyte (GB) of data and serves millions of
requests per hour.
Queries on DB1 are taking longer than normal to complete.
You run the following Transact-SQL statement:
SELECT * FROM sys.database_query_store_options
You determine that the Query Store is in Read-Only mode.
You need to maximize the time that the Query Store is in Read-Write mode.
Which Transact-SQL statement should you run?
A.
ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL)
B.
ALTER DATABASE DB1SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 50)
C.
ALTER DATABASE DB1SET QUERY_STORE (CLEANUP_POLICY =
(STALE_QUERY_THRESHOLD_DAYS = 14));
D.
ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE)
Explanation:
Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime
statistics and inactive queries.
By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your
scenario.
Avoid keeping historical data that you do not plan to use. This will reduce changes to read-only status. The size
of Query Store data as well as the time to detect and mitigate the issue will be more predictable. Use
Management Studio or the following script to configure time-based cleanup policy:
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));
https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-thequery-store