Developing SQL Databases — Question 129

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.

Answer options

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

Correct answer: B, C, D

Explanation

The correct answer includes options B, C, and D because setting QUERY_CAPTURE_MODE to AUTO allows for automatic query capturing, increasing MAX_STORAGE_SIZE_MB provides additional storage for query data, and changing SIZE_BASED_CLEANUP_MODE to AUTO ensures that cleanup occurs based on size considerations. Option A is incorrect as it does not address the immediate need for query performance assessment, and option E does not impact the Query Store settings directly.