Home » Microsoft » 70-764 v.2 » Which Transact-SQL statement should you run?
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)
E. ALTER DATABASE DB1SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
Correct Answer: E
Explanation/Reference:
When the actual state is read-only, use the readonly_reason column to determine the root cause. Typically you will find that Query Store transitioned to read-only mode because the size quota was exceeded.Consider the following steps to switch Query Store to read-write mode and activate data collection:
• Increase the maximum storage size by using the MAX_STORAGE_SIZE_MB option of ALTER DATABASE.
• Clean up Query Store data by using the following statement:
• ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;
You can apply one or both of these steps by the executing the following statement that explicitly changes operation mode back to read-write:
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
References:https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store
