Exam 70-762 topic 1 question 167 discussion

Actual exam question from Microsoft's 70-762

Question #: 167
Topic #: 1

You have a Microsoft Azure SQL Database. You enable Query Store for the database and configure the store to use the following settings:
✑ SIZE_BASED_CLEANUP_MODE = OFF
✑ STALE_QUERY_THRESHOLD_DAYS = 60
✑ MAX_STORAGE_SIZE_MB = 100
✑ QUERY_CAPTURE_MODE = ALL
You use Azure Query Performance Insight to review queries. You observe that new queries are not displayed after 15 days and that the Query Store is set to read-only mode.
If the Query Store runs low on data space, the store must prioritize queries that run regularly or queries that consume applicant resources.
You must set the Query Store to read_write mode and determine the performance of queries from the past 60 days.
Which three actions should you perform? Each correct step presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Set the value of the CLEANUP_POLICY setting to (STALE_QUERY_THRESHOLD_DAYS = 75)
B. Set the value of the QUERY_CAPTURE_MODE setting to AUTO
C. Increase the value for the MAX_STORAGE_SIZE_MB setting
D. Set the value of the SIZE_BASED_CLEANUP_MODE setting to AUTO
E. In the Azure portal, navigate to Query Performance Insight. Use the Custom tab to select a period of 2 months.

Show Suggested Answer

Suggested Answer: BCD 🗳️
B: Capture mode:
All "" Captures all queries. This is the default option.
Auto "" Infrequent queries and queries with insignificant cost are ignored. (Ad hoc recommended)
None "" Query Store stops capturing new queries.
C: Max Size (MB): Specifies the limit for the data space that Query Store can consume within the database. This is the most important setting that directly affects operation mode of the Query Store.
While Query Store collects queries, execution plans and statistics, its size in the database grows until this limit is reached. When that happens, Query Store automatically changes the operation mode to read-only and stops collecting new data. You should monitor this closely to make sure you have sized the store appropriately to contain the full history you'd like to retain.
D: Size Based Cleanup Mode: Specifies whether automatic data cleanup will take place when Query Store data size approaches the limit.
It is strongly recommended to activate size-based cleanup to makes sure that Query Store always runs in read-write mode and collects the latest data.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store

by Slava_bcd81 at Aug. 17, 2020, 10:34 a.m.

Comments

Submit Cancel

lh2607

Highly Voted 4 years, 5 months ago

I'd go with BDE. Without E we are not doing anything to monitor the performance which is the whole point of the question. We are also not told to solve the issue, which is what the answer C would help with, the question is just about monitoring the performance, which is what the Query Performance Insight is for. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15

upvoted 5 times

...

Slava_bcd81

Highly Voted 4 years, 8 months ago

"You must... ... and determine the performance of queries from the past 60 days" I think the answer is BCE

upvoted 5 times

Luzix

4 years, 5 months ago

D: it´s recommended. E: it´s a must.

upvoted 1 times

...

4 years, 7 months ago

must prioritize queries - B read_write - C + D - read the https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store 60 days - already set BCD is Correct

upvoted 5 times

...

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 167 discussion

Comments

lh2607

Slava_bcd81

Luzix

BabyBee

BabyBee

vrab

vrab

vrab

Cococo

SY0-701