You have an Azure SQL database named sqldb1. You need to minimize the possibility of Query Store transitioning to a read-only state. What should you do?
A.
Double the value of Data Flush interval
B.
Decrease by half the value of Data Flush Interval
C.
Double the value of Statistics Collection Interval
D.
Decrease by half the value of Statistics Collection interval
From my point of view, the correct answer is C. (Double the value of Statistics Collection Interval). To avoid Query Store transitioning to read-only state, the runtime statistics for the Last Stale Query Threshold days must fit in the defined Max Size (MB). If the Statistics Collection Interval is increased then less space will be used for the persisted runtime statistics and there will be more free space for new data. The Data Flush Interval can delay the transitioning to read-only state, but not minimize the possibility it occurs.
Disagree.
It seems logical that doubling Statistics Collection Interval will make Query Store consume less storage. BUT - when you try to run this:
ALTER DATABASE [DB1]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 120)
GO
It fails with error:
Msg 153, Level 16, State 6, Line 1
Invalid usage of the option interval_length_minutes in the ALTER DATABASE statement.
The GUI doesn't have selection of 2 hours, too.
You can only set it to 30 minutes (or less), or to 1 day 1440 minutes.
So C is not the right answer.
B is correct.
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, which means that your performance analysis is no longer accurate.
MAX_STORAGE_SIZE_MB limit isn't strictly enforced. Storage size is checked only when Query Store writes data to disk. This interval is set by the DATA_FLUSH_INTERVAL_SECONDS option or the Management Studio Query Store dialog option Data Flush Interval.
https://learn.microsoft.com/en-us/sql/relational-databases/performance/manage-the-query-store?view=sql-server-ver16&tabs=ssms
B: Decrease by half the value of Data Flush interval --> to prevent in-memory max size.
While Statistics Collection Interval has direct impact on Query Store size storage wise, you can't double the Statistics collection interval. GUI doesn't allow that, and setting INTERVAL_LENGTH_MINUTES to 120 in T-SQL also fails.
It's B. Decreasing Data Flush is the main way to keep the Query Store outside of read-only, prevent in-memory max size, and when it writes to disk, can delete stale data if enabled. Data Flush can be halved/doubled with ease. Statistics Collection Interval cannot be doubled as easily.
https://learn.microsoft.com/en-us/sql/relational-databases/performance/manage-the-query-store?view=sql-server-ver16&tabs=ssms
Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries, expressed in days. By default, Query Store is configured to keep the data for 30 days, which might be unnecessarily long for your scenario.
Avoid keeping historical data that you don't plan to use. This practice reduces changes to read-only status. The size of Query Store data and 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:
SQL
Copy
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));
B combined with SIZE_BASED_CLEANUP_MODE
https://learn.microsoft.com/en-us/sql/relational-databases/performance/manage-the-query-store?view=sql-server-ver16&tabs=ssms#query-store-maximum-size
The Max Size (MB) limit isn't strictly enforced. Storage size is checked only when Query Store writes data to disk. This interval is set by the Data Flush Interval (Minutes) option. If Query Store has breached the maximum size limit between storage size checks, it transitions to read-only mode. If Size Based Cleanup Mode is enabled, the cleanup mechanism to enforce the maximum size limit is also triggered.
This clears up the confusion in MS documentation...it specifically states that Flush parameters are what controls Max DB store https://docs.microsoft.com/en-us/sql/relational-databases/performance/how-query-store-collects-data?view=sql-server-ver15#remarks
You can't really double or halve the Statistics Collection interval as that is a fixed value with a list of allowed values, see: sys.database_query_store_options You can't halve 15 minutes and can't double 1 hour. Flush Interval can be set to any arbitrary value above 1 minute. Makes sense to have the size checked more often. Tricky but very bad question.
This section is not available anymore. Please use the main Exam Page.DP-300 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
miguelmol
Highly Voted 3 years, 7 months agovoodoo_sh
2 months agoLuke97
3 years, 4 months agoRaffer
3 years, 1 month agomatongax
Highly Voted 2 years, 6 months agoKingChuang
1 year, 3 months agovoodoo_sh
Most Recent 2 months agok6745
5 months, 1 week agoofzrgrz
7 months, 3 weeks agojtu363
1 year, 3 months agojtu363
1 year, 3 months agovittOS
1 year, 4 months agoOneplusOne
1 year, 4 months agoBacky
1 year, 9 months agoeric0718
2 years agoannaandalex2021
2 years, 1 month agothedatadetective
2 years, 3 months agocusman
2 years, 1 month agoarrakis968
2 years, 3 months agoCaptainJameson
2 years, 4 months agocura
2 years, 5 months agocura
2 years, 6 months agoerssiws
2 years, 8 months ago