Query Store properties

This week I moderated a very excellent and informative PASS Marathon live webinar's session by Thomas LeBlanc @TheSmilingDBA

The topic of the presentation is "Query Store: An Introduction - Performance metrics in a SQL Server database" I really enjoyed Thomas' presentation, which stimulates my interest in having a deep look at query store.

By default, Query Store is not enabled.

You can enable it at the database level in SQL Server Management Studio by using Object Explorer to navigate to the database for which you want to enable Query Store. Right-click the database name to open the Database Properties dialog box and click the Query Store tab.

As shown above, in the Operation Mode (Requested) drop-down list, Read Write is selected. You can later change the Operation Mode (Requested) property to Read Only when you want to retain existing query plans and execution statistics information available in the query store, but no longer want to add new information. SQL Server automatically switches to this mode when the query store reaches its maximum allocated space. To disable Query Store, change this property to Off.

Aside from Operation Mode (Requested), there are several other properties that you can configure to manage the query store:

Data Flush Interval (Minutes) The frequency in minutes at which SQL Server writes data collected by the query store to disk.
Statistics Collection Interval The granularity of time for which SQL Server aggregates runtime execution statistics for the query store. You can choose one of the following intervals: 1 minute, 5 minutes, 10 minutes, 15 minutes, 30 minutes, 1 hour, or 1 day. If you capture data at a high frequency, bear in mind that the query store requires more space to store more finely grained data.
Max Size (MB) The maximum amount of space allocated to the query store. The default value is 100 MB per database. If your database is active, this value might not be large enough to store query plans and related information.
Query Store Capture Mode The specification of the types of queries for which SQL Server captures data for the query store. You can choose one of the following options:
None The query store stops collecting data for new queries, but continues capturing data for existing queries.
All The query store captures data for all queries.
Auto The query store captures data for relevant queries. It ignores infrequent queries and queries with insignificant compile and execution duration.
Size Based Cleanup Mode The specification of whether the cleanup process activates when the query store data approaches its maximum size (Auto) or never runs (OFF).
Stale Query Threshold (Days) The number of days that SQL Server keeps data in the query store.

You can also use the statement as the following to enable the query store, replacing <databasename> with the name of the database that you want to configure.

ALTER DATABASE <databasename> SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE , CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 30 ), DATA_FLUSH_INTERVAL_SECONDS = 3000, MAX_STORAGE_SIZE_MB = 500, INTERVAL_LENGTH_MINUTES = 50 );

You can clear the data from the query store by clicking Purge Query Data on the Query Store tab of the Database Properties dialog box or by executing either of the statements as follows,

--Option 1: Use the ALTER DATABASE statement ALTER DATABASE <databasename> SET QUERY_STORE CLEAR ALL; GO

--Option 2: Use a system stored procedure EXEC sys.sp_query_store_flush_db; GO

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

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.

Answer 1: BCD

B: Capture mode: All for Captures all queries. This is the default option. Auto for Infrequent queries and queries with insignificant cost are ignored. (Ad hoc recommended) . None for 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

Example 2:

You have a Microsoft Azure SQL Database named MyDb that uses server version V12. You plan to use Query Performance Insight to troubleshoot performance problems. The database query store is not enabled. You need to enable the database query store to meet the following requirements for the database:

✑ Statistics must be aggregated every 15 minutes. ✑ Query stores must use no more than 1,024 megabytes (MB) of storage. ✑ Query information must be retained for at least 15 days. ✑ Queries must be captured based on resource consumption.

You connect to the database by using SQL Server Managements Studio. How should you complete the Transact-SQL statements?

NOTE: More than one combination of answer choices is correct.