The Query Store feature in Azure SQL Database provides you with insightful information about query execution plans, query performance, and more. This tool is particularly useful for database administrators (DBAs) who are preparing for the DP-300: Administering Microsoft Azure SQL Solutions exam.
Introduction to Query Store
Query Store is an integral part of Azure SQL Database and SQL Server designed to capture query execution history over time and provide a method for easy performance analysis and troubleshooting. The tool traps query texts, query plans, runtime stats, and wait stats.
Query store preserves all this data across database restarts, upgrades, and even failovers on the Azure SQL Database. This kind of consistency makes it an essential tool in monitoring databases, identifying performance problems and fixing them.
Understanding Query Store Components
The Query Store has major components that include:
- Query Store Runtime Stats
- Query Store Wait Stats
- Query Text
- Query Plan
These stats capture the number of times a query has been executed, its CPU time, elapsed time, logical reads, physical reads, writes, and memory among other stats. The data is cleared during Azure SQL reboots.
These stats hold information on a query’s wait event. They provide detailed information about why a query had to delay, which can be very useful in troubleshooting.
This contains the SQL text of the query which has been executed.
This holds the execution plans which SQL Server optimizer has generated for the respective queries.
Enabling Query Store
Query Store is not enabled by default. You’ll need to enable it manually. Here’s a simple T-SQL query to enable the Query Store:
ALTER DATABASE <Your_Database_Name> SET QUERY_STORE = ON;
Following this, you can customize your Query Store parameters, including `QUERY_CAPTURE_MODE`, `DATA_FLUSH_INTERVAL_SECONDS`, `MAX_STORAGE_SIZE_MB`, and many more.
The `QUERY_CAPTURE_MODE` determines which queries are captured. It can be set to `ALL`, `AUTO` (default), or `NONE`. `DATA_FLUSH_INTERVAL_SECONDS` specifies the frequency at which data is flushed to disk. `MAX_STORAGE_SIZE_MB` is the maximum amount of disk space Query Store can use.
Monitoring with Query Store
Query Store provides various visualization and analytical tools to monitor performance. You can access these reports directly from the Azure portal or use SQL Server Management Studio for SQL Server.
You can access the overall resource consumption for all queries on the server with Query Store. Using this data, you can determine which queries are consuming the most resources.
An important feature is the `Regressed Queries` view, which can help identify queries that have started performing poorly compared to their past performance. When a query suddenly starts taking more time or resources to execute, it can affect the overall performance of your database. Identifying and optimizing these queries can drastically improve your database performance.
Conclusion
Query Store is an efficient and sophisticated tool for monitoring Azure SQL solutions. Learning to use Query Store when preparing for the DP-300 exam can be very beneficial. By examining how to enable and utilize it, you’ll have a powerful tool for identifying and resolving database performance issues.
Practice Test
True or False: Query Store in Azure SQL Solutions helps in monitoring and diagnosing issues with the query performance.
- True
- False
Answer: True.
Explanation: The Query Store feature in Azure SQL Solutions provides insights on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by changes in query plans.
What can you do with the Query Store Dashboard in Azure SQL server?
- a) Analyze the performance of databases
- b) View the most expensive queries
- c) Analyze the disk usage
- d) All of the above.
Answer: d) All of the above.
Explanation: Query Store Dashboard in Azure SQL server lets you analyze the performance of databases, view and work with query data, and examine disk usage.
True or False: Query Store can track all executed queries.
- True
- False
Answer: True.
Explanation: Query Store has the capability to track all executed queries and their runtime statistics.
The Query Store retains query data for a specified amount of time. What is this specified time known as?
- a) Data Retention Period
- b) Query Store Time
- c) Storing Time
- d) Query Retention Time
Answer: a) Data Retention Period.
Explanation: The Data Retention Period refers to the specific amount of time that the Query Store retains query data.
True or False: Query Store doesn’t support monitoring on Read-Only replicas in Azure SQL DB.
- True
- False
Answer: False.
Explanation: In Azure SQL DB, Query Store can be used to monitor queries specifically on Secondary Read-Only replicas.
Which of the following types of queries can Query Store capture?
- a) Prepared queries
- b) Ad hoc queries
- c) Both a and b.
Answer: c) Both a and b.
Explanation: Query Store can capture both ad hoc and prepared query types.
What Query Store operation mode should be used to monitor a workload without any performance impact?
- a) Read-Write
- b) Read-Only
- c) Off
Answer: b) Read-Only.
Explanation: Read-Only mode allows to monitor workload with minimal performance impact, however, new queries and runtime stats won’t be captured.
True or False: The Query Store size can exceed the maximum setting.
- True
- False
Answer: True.
Explanation: Although infrequently, the Query Store size can exceed the maximum size due to transitions between the operation modes.
What purge policy does Query Store use when the maximum storage size is reached?
- a) Delete all data.
- b) Delete oldest data.
- c) Delete most recent data.
Answer: b) Delete oldest data.
Explanation: When the maximum size is reached, Query Store automatically purges the oldest data (based on the Data Retention Period).
Query Store can be configured to capture queries that consume at least a specific amount of resources. True or False?
- True
- False
Answer: True.
Explanation: Query Store has a feature to capture queries that consume resources above a certain threshold. This helps to focus on resource-intensive queries.
Interview Questions
What is the purpose of utilizing Query Store in Azure SQL Database?
Query Store is used to maintain a history of query execution plans with their performance data, and to quickly identify queries that have gotten slower recently, allowing a DBA or developer to force the last known good plan.
What are the components of the Query Store feature?
The components of Query Store are the plan store, runtime stats store, the request execution component, and the Query Store management component.
What is the purpose of the Plan Store in the Query Store?
Plan Store stores all execution plans for every query, ensuring no recompilation of query results. It helps in recognizing plan regressions and allows forcing previous plans.
What is the forced plan feature in Query Store?
The forced plan feature in Query store allows a DBA or developer to force the SQL Server Query Store to use a specific plan for executing a query. It is used when the Query Optimizer selects a sub-optimal plan.
How can I enable Query Store in Azure SQL database?
Query Store can be enabled by running ALTER DATABASE statement setting the QUERY_STORE = ON.
What happens when you force a plan using the Query Store in Azure SQL Database?
Forcing a plan using the Query Store essentially restricts the Query Optimizer to only use the forced plan. If the plan cannot be used due to changes in the view schema, index, or permissions, the query will fail to execute.
What is the benefit of Query Store’s Wait Statistics feature?
Wait Statistics allows you to identify the types of waits affecting the query performance and the time lengths of those waits. The information helps in diagnosing performance problems.
What is the role of the Query Store in Performance insight in Azure SQL Database?
Query Store provides information on query performance, allowing Performance Insight to identify possible problems and enabling troubleshooting.
What default Query Store settings would you modify if you are dealing with a read-intensive workload in Azure SQL?
For read-intensive workload in Azure SQL, it is recommended to change the DATA_FLUSH_INTERVAL_SECONDS to a higher value and increase MAX_STORAGE_SIZE_MB to allow Query Store to collect more data.
If the Query Store runs out of space, what is the general default action?
If Query Store runs out of space, the default action is to change to a read-only mode; however, you can set the QUERY_CAPTURE_MODE to AUTO that changes to manage runtime statistics in a more size effective manner.
Is it possible to export Query Store data in Azure SQL Database?
Yes, you can export Query Store data to an XEL file using feature Export to an Extended Event.
What role does the Query Store play in the Automatic Tuning feature in Azure SQL Database?
Query Store provides plans and performance metrics to the Automatic Tuning feature, which uses machine learning algorithms to evaluate and apply tuning recommendations.
How long does Query Store keep data for in Azure SQL Database?
By default, Query Store keeps data for up to 30 days. However, these settings can be adjusted depending on your needs.
Can the Query Store help in identifying the most resource consuming queries?
Yes, Query Store comes with built-in reports that helps to identify top resource consuming queries, which assists in troubleshooting and optimizing queries.
How is the Query Store different from SQL Server trace or extended events?
Unlike SQL Server trace or extended events, Query Store is not a tracing mechanism. It’s a store that keeps information about query plans and statistics. It collects data per database without a significant performance overhead.