Query Store ON is the new default for Azure SQL Database

Publicado el 7 diciembre, 2016

Senior Program Manager, SQL Server Team

We are happy to announce that Query Store is turned ON in all Azure SQL databases (including Elastic Pools) which will bring benefits both to the end users and the entire Azure SQL Database platform.

Why is this important?

Query Store acts as a “flight data recorder” for the database, continuously collecting critical information about the queries. It dramatically reduces resolution time in case of performance incidents, as pre-collected, relevant data is available when you need it, without delays.

You can use Query Store in scenarios when tracking performance and ensuring database performance predictability is critical. The following are some examples where Query Store is going to significantly improve your productivity:

  • Identifying and fixing application performance regressions (more details in this blog article)
  • 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 (more details in this blog article)
  • Assessing impact of any application or configuration change (A/B testing)
  • Identifying and improving ad-hoc workloads (more details here)

Query Store also provides the foundation for performance monitoring and tuning features, such as the SQL Database Advisor. Query Store powers SQL Database Performance Insights which allows you to monitor and troubleshoot database performance directly from the Azure portal. With Query Store turned ON we ensure that relevant information about your most critical queries is available first time you open the queries chart on SQL Database Performance Insights:

Query Perfrormance Insights

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

The default configuration is automatically applied by Azure SQL Database. If you want to switch to a customized Query Store configuration, use ALTER DATABASE with Query Store options. Also check out Best Practices with the Query Store to learn how to choose optimal parameter values.

Next steps

For more detailed information, check out the online documentation: