Which Transact-SQL statement should you run?

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)

View Answer

Answer: C

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));

References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-thequery-store