Administering a SQL Database Infrastructure — Question 63

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?

Answer options

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

Correct answer: C

Explanation

The correct answer is C, as setting the CLEANUP_POLICY with a STALE_QUERY_THRESHOLD_DAYS of 14 days helps retain query data for a longer period, thereby maximizing the time the Query Store is in Read-Write mode. Option A, while it enables all query capture, does not directly influence the duration in Read-Write mode. Options B and D do not address the need to maximize Read-Write mode duration effectively.