Microsoft has announced that Query Store is now turned ON in all Azure SQL databases, which includes Elastic Pools, in order to bring benefits to both the end users as well as the entire Azure SQL Database platform.
The Query Store acts like a “flight data recorder” for databases that collects all the critical information about the queries. It reduces the resolution time in case of performance incidents as pre-collected, relevant data is available as per your requirement, without any kind of delay.
Users are now able to use Query Store in scenarios when tracking performance as well as when ensuring database performance predictability is critical. Given below are some of the examples the company has provided in its official blog, where Query Store is helping to improve your productivity:
- Identifying and fixing application performance regressions
- Tuning the most expensive queries considering different consumption metrics (elapsed time, CPU time, used memory, read and write operations, log I/O, etc.)
- Keeping performance stability with compatibility level 130 in Azure SQL Database
- Assessing impact of any application or configuration change
- Identifying and improving ad hoc workloads
Query Store also provides the foundation for monitoring performance along with feature tuning, such as SQL Database Advisor. With Query Store turned ON, Microsoft states that they have ensured that all the relevant information about the most critical queries is available when you open the queries chart on SQL Database Performance Insights for the first time.
Image Source: azure.microsoft.com
The company goes on to
state,
“We strongly recommend keeping Query Store ON. Thanks to an optimal default configuration and automatic retention policy, Query Store operates continuously using an insignificant part of the database space with a negligible performance overhead, typically in the range of 1-2%.”