Exam 70-764 topic 1 question 48 discussion

Actual exam question from Microsoft's 70-764

Question #: 48
Topic #: 1

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)

Show Suggested Answer

Suggested Answer: C 🗳️
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-the-query-store

by TheSwedishGuy at Jan. 24, 2020, 11:42 a.m.

Comments

Submit Cancel

dxico

4 years, 5 months ago

This question is missing a fifth option: ALTER DATABASE DB1 SET QUERY_STORE (OPERATION_MODE = READ_WRITE); This should be the right one

upvoted 1 times

p48m1

4 years, 5 months ago

Nope, if query store is full it will be set automatically to read only. Options are expanding space or deleting old data. In this case, lower the retention period.

upvoted 1 times

...

shb

5 years, 4 months ago

One choice E. is missing, which is: E. ALTER DATABASE DB1 SET QUERY_STORE (OPERATION_MODE = READ_WRITE); So the E. would be the possible answer.

upvoted 1 times

TheSwedishGuy

5 years, 4 months ago

If the data in the Query Store hits the MAX_STORAGE_SIZE_MB limit, the Query Store automatically changes the state from read-write to read-only and stops collecting new data. If you clean up historical data, it will maximize the time the query store will be in read-write mode.

upvoted 3 times

KC

4 years, 10 months ago

Agreed. This question seems to be testing our knowledge of the default size of MAX_STORAGE_SIZE. We obviously want to increase that size. But the provided answer of 50MB is less than the default of 100MB. So it probably isn't the answer. So I think the best other option is to clean up historical data.

upvoted 1 times

...

TheSwedishGuy

5 years, 5 months ago

Keeping QUERY_STORE query statistics for 30 days is nonsense in such an active database. Lower it to increase performance.

upvoted 1 times

...