Configure Query Store

Query Store, introduced with SQL Server 2016, is a flight data recorder for your database. It periodically captures and stores execution plans and runtime statistics for the queries running on your system. Once data is captured and kept in the Query Store, you can use it for identifying performance issues, analyzing query performance, and preventing regressions.

Enable Query Store

Before utilizing the Query Store functionalities, you need to enable it.

The following T-SQL script can be used:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

Once Query Store is enabled, you can configure its settings according to your needs.

Configuring Query Store

You can use the following command to set the operation mode of Query Store to Read-Write.

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Query Store Settings

Here are some important Query Store properties that can be set:

• OPERATION_MODE: It can be either OFF, READ_ONLY, or READ_WRITE. You won’t be able to collect new data with the OFF or READ_ONLY setting.
• CLEANUP_POLICY: This defines the retention policy of the Query Store data. If you don’t want any data to be automatically cleaned up, you can set it to OFF.
• DATA_FLUSH_INTERVAL_SECONDS: This defines the frequency at which Query Store data is flushed to disk.
• MAX_STORAGE_SIZE_MB: This defines the maximum amount of space that the Query Store can occupy in the database.
• QUERY_CAPTURE_MODE: This setting defines what queries will be captured. It can be set to NONE, AUTO, or ALL.

Below is the T-SQL command to configure these settings:

ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 100, QUERY_CAPTURE_MODE = AUTO);

Verify Query Store Configuration

To confirm that Query Store is properly configured and whether it’s working as expected, you can use the following T-SQL command:

SELECT * FROM sys.database_query_store_options

This command returns the current Query Store settings for your database.

Benefit of Using Query Store

Query Store helps in performance tuning, isolating problematic queries, and troubleshooting. Since Query Store tracks queries, it enables database administrators to determine which query has taken most of the resources or whether a query has regressed over a period of time. Importantly, it assists in preventing potential performance issues.

Preparing for the DP-300 Exam on Azure requires a wide array of skills – configuring the Query Store is definitely one of them. Understanding its use can ensure smooth and effective database administration on SQL Server.

Practice Test

True or False: You can enable the Query Store from the Azure portal.

• True
• False

Answer: True

Explanation: The Query Store feature can be easily enabled or disabled from the Azure portal by navigating to the Query Store panel under the SQL Server database section.

The Query Store feature in Azure SQL database automatically cleans up data that is older than what time range?

• A. 30 days
• B. 60 days
• C. 90 days
• D. 120 days

Answer: A. 30 days

Explanation: This is the default retention period for the Query Store. However, you can configure this time frame according to your specific requirements.

True or False: Within the Query Store, you can’t force the execution plan of a specific query.

• True
• False

Answer: False

Explanation: One of the key features of Query Store is the ability to force a specific execution plan for a query.

What is the maximum size of the Query Store in percentage of the max size of the database it is configured for?

• A. 10%
• B. 15%
• C. 20%
• D. 25%

Answer: C. 20%

Explanation: The maximum size of the Query Store can be configured up to 20% of the maximum size of the database.

When the Query Store reaches its maximum size, what happens to the new queries and plans?

• A. They get discarded
• B. They overwrite the old plans
• C. The Query Store stops capturing new queries
• D. It automatically increases its size

Answer: C. The Query Store stops capturing new queries

Explanation: When the maximum size is reached, the Query Store switches to a read-only mode and stops capturing new queries.

True or False: The Query Store monitors and saves the history of all queries, plans, and runtime statistics.

• True
• False

Answer: True

Explanation: The Query Store feature is a powerful tool for monitoring and troubleshooting performance, as it captures a history of queries, plans, and statistics.

Which of the following commands activates the Query Store?

• A. ALTER DATABASE SET QUERY_STORE = ON
• B. ENABLE QUERY_STORE
• C. ALTER DATABASE SET QUERY_STORE = ACTIVE
• D. UPDATE DATABASE SET QUERY_STORE = ON

Answer: A. ALTER DATABASE SET QUERY_STORE = ON

Explanation: The command ‘ALTER DATABASE SET QUERY_STORE = ON’ is used to activate the Query Store.

True or False: The Query Store retains the data indefinitely until manually cleared by the administrator.

• True
• False

Answer: False

Explanation: The Query Store automatically clears out the information older than its configured retention value, it does not keep data indefinitely.

Which of the following is not a Query Store data flushing option in Azure SQL?

• A. On database shutdown
• B. At execution
• C. Every 15 minutes
• D. According to free space left

Answer: D. According to free space left

Explanation: Query Store data flushing in Azure SQL happens at database shutdown, according to an interval of 15 minutes, or at execution. It doesn’t get flushed according to free space left.

True or False: Apart from Azure portal, Query Store can also be configured via Transact-SQL (T-SQL).

• True
• False

Answer: True

Explanation: Indeed, Transact-SQL (T-SQL) is another method to configure the Query Store along with Azure portal.

Interview Questions

What is the primary function of Query Store in Azure SQL Database?

Query Store simplifies performance troubleshooting by helping to identify performance differences caused by query plan changes. It automatically captures a history of queries, plans, and runtime statistics, and retains these for your review.

How can Query Store be configured?

Query Store can be configured using SQL Server Management Studio and executing T-SQL commands or through Azure portal on the Query Performance Insight page.

What is the use of the “Max Size” parameter in Query Store settings in Azure SQL?

The “Max Size” parameter determines the maximum amount of space that Query Store can use. When the maximum amount is reached, the Query Store enters read-only mode.

What happens when the Query Store reaches its maximum size?

When the maximum size is reached, Query Store switches its operational mode to read-only. No further data will be collected until there is available space again.

What is the significance of “Data Flush Interval” in Query Store settings?

The “Data Flush Interval” specifies the frequency at which the data collected in the Query Store runtime statistics buffer is persisted to disk.

How does the ‘Stale Query Threshold’ setting affect Query Store?

The ‘Stale Query Threshold’ setting determines the period in days queries should be stored in the Query Store. Queries that go unused beyond this threshold are cleaned up.

Where can one view information in the Query Store for Azure SQL Database?

Information about the Query Store can be viewed through a number of graphs in the Query Store reports within Management Studio or using Query Performance Insight in the Azure portal.

When is it advisable to clear the data in the Query Store?

It is advisable to clear the data in Query Store when a large schema or workload change occurs, to avoid comparing future plans and statistics against outdated ones.

How can the Query Store feature be enabled in Azure SQL Database?

The Query Store feature can be enabled by setting the QUERY_STORE = ON in the ALTER DATABASE statement, or through the Azure portal by setting the “Query Store” status to ON in database settings.

How can you monitor Query Store performance in Azure SQL Database?

You can use the Query Store reports in SQL Server Management Studio or you can use the Query Performance Insight in Azure Portal. Query Performance Insight provides a straightforward way to assess the impact of query performance on the overall health of your database.

What is the “Capture Mode” setting in the Query Store used for?

The “Capture Mode” setting controls the type of queries that are captured by the Query Store. It can be set to ‘all’ which captures all queries, or to ‘auto’ which captures queries based on resource consumption.

How long is the data in the Query Store retained?

The length of data retention in Query Store is determined by the “Statistics Collection Interval”. The default value is 7 days and the maximum is 365 days.

What can cause Query Store to enter a read-only mode?

Query Store can enter a read-only mode either because it has reached the size specified in the Max Size setting or because there’s an internal error.

How can one tell if the Query Store is in a read-only mode?

One can verify the operational mode of the Query Store using the view “sys.database_query_store_options”. “READ_ONLY” status means that the Query Store is in a read-only mode.

What role does the “Size Based Cleanup Mode” play in Query Store?

The “Size Based Cleanup Mode” setting controls whether automatic cleanup should be triggered when the max size is reached. It helps prevent the Query Store from becoming read-only.