Query Store is enabled by default in SQL Server 2022

August 18, 2022

Product
SQL

Part of the SQL Server 2022 blog series.

Query Store is one of the most powerful database-scoped features in SQL Server for troubleshooting performance and improving the stability of your database workloads.

The Query Store feature first became available in SQL Server 2016 and provides the ability for database administrators (DBAs) to gain insights on query plan choice and the impact these choices have on SQL Server performance.

In SQL Server 2022, Query Store is now enabled by default for all newly created SQL Server databases to help customers better track performance history, troubleshoot query plan–related issues, and enable new query processor capabilities that we will introduce in the subsequent SQL Server 2022 blog series.

When enabled, Query Store asynchronously captures a history of queries, plans, and runtime statistics, and persists this data at the database scope. This means that when you migrate your databases to another version of SQL Server, migrate to different hardware, or even migrate your databases to the cloud—you can examine the performance difference down to the query plan level.

Query Store accomplishes this by separating the performance data by time windows so DBAs can identify usage patterns and understand when query plan changes happened on the server.

If an application starts having a performance issue which can occur in a number of scenarios such as high usage, post-migration, and especially when data distributions change—Query Store provides a method to force which plan a query will use. The ability to control which plans a query will use makes it much easier to respond to errant query plans and provides database administrators with the assurance they can upgrade to later versions of SQL Server, change database compatibility levels, and confidently migrate database applications to the cloud.

For all these scenarios, Query Store gives DBAs the confidence that they can easily monitor their database application’s performance and quickly respond to performance issues when needed.

Query Store is available for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. In Azure SQL database, Query Store is referred to as Query Performance Insights and is managed on the Query Performance Insight navigation blade.

Query Performance Insights in Azure SQL Database

While it is a new scenario to have Query Store enabled by default for SQL Server, Query Store has be enabled by default for Azure SQL Database (including elastic pools) and Azure SQL Managed Instance databases for some time now and this capability has been extended to SQL Server 2022.

Query Store has been a popular feature since it was first made available in SQL Server 2016, and now in SQL Server 2022 we are leveraging Query Store’s ability to persist query plan history and health to enable new capabilities.

Query Store in SQL Server 2022 extends the ability of the query processor by enabling features such as Query Store hints, cardinality estimation (CE) feedback, degree of parallelism (DOP) feedback, memory grant feedback (MGF) persistence, and optimized plan forcing.

Query Store’s ability to asynchronously make a record of your database application’s performance data enables these new capabilities in SQL Server 2022.

While we are enabling Query Store by default for all newly created databases in SQL Server 2022, it is important to note that for all databases that have been restored from other SQL Server instances and for those databases that are upgraded from an in-place upgrade to SQL Server 2022, these databases will retain the previous Query Store settings—whether they had Query Store enabled or not.

As a post-migration step, for databases that are restored from previous SQL Server instances, it is recommended to enable Query Store and separately evaluate the database compatibility level settings as some Intelligent Query Processing features are enabled by the compatibility level setting.

Note: For optimal SQL Server performance and migration health, it is recommended to closely follow the recommendations provided in the post-migration validation and optimization guide.

Query Store overhead

As mentioned, Query Store has been available in Azure SQL Database and Azure SQL Managed Instance since 2016; additionally, we have improved Query Store performance in the last several releases of SQL Server with many improvements being surfaced through cumulative updates in SQL Server 2019.

Query Store improvements introduced via cumulative updates

Microsoft has improved ad-hoc workloads with Query Store with several cumulative updates in SQL Server 2019.

The most relevant cumulative updates that influence Query Store health are:

KB5000642—Cumulative Update 9 for SQL Server 2019: Fixes Query Store scalability improvement for ad-hoc workloads.
KB4577194—Cumulative Update 8 for SQL Server 2019: Query Store scalability improvement for ad-hoc workloads. Query Store now imposes internal limits to the amount of memory it can use and automatically changes the operation mode to READ_ONLY until enough memory has been returned to the Database Engine, preventing performance issues.
KB4563110—Cumulative Update 6 for SQL Server 2019: This improvement can force the Query Store option to be turned off by specifying the additional option FORCED in the ALTER DB command. The FORCED option allows you to turn off Query Store immediately by aborting all background tasks. ALTER DATABASE {0} SET QUERY_STORE = OFF (FORCED)

Note: It is always recommended to evaluate the latest cumulative updates for the release of SQL Server your organization is maintaining for the best performance and stability of your database application environment.

Query Store setting improvements

In addition to the stability improvements, we introduced via the cumulative updates in SQL Server 2019, the Query Store default behavior has been refined to reduce the volume of the captured Query Store details while increasing the amount of Query Store history that can be retained.

The setting changes that were introduced in SQL Server 2019 have been carried forward and will improve the performance of the Query Store behavior while still optimizing the Query Store’s ability to serve both as a “flight data recorder,” but to also enable the query processor’s ability to leverage new capabilities in SQL Server 2022.

To improve the performance of the Query Store settings, the capture mode has changed from ALL to AUTO, and the max size (MB) has changed from 100 MB to 1024 MB.

Under the AUTO capture mode setting, we now capture Query Store details when any of the following thresholds are hit:

1 second = any compilation
100 milliseconds = execution CPU time
30 executions = execution count

These settings help further reduce any impact Query Store could have on SQL Server, while still ensuring that the critical data is captured for troubleshooting and providing the ability to enable new capabilities in SQL Server 2022.

Custom capture policies

In order to take advantage of certain SQL Server 2022 capabilities, it is necessary to have Query Store enabled by default.

If there is still any concern about the overhead Query Store may introduce, database administrators can leverage custom capture policies to further tune the Query Store capture behavior.

Custom capture policies are available to help further tune Query Store captures. Custom capture policies can be used to be more selective about which queries and query details are captured. For example, an administrator may choose to capture only the most expensive queries, repeated queries, or queries that have a high level of compute overhead.

Custom capture policies can help Query Store capture the most important queries in your workload.

Please see the example ALTER DATABASE script below that would enable a custom capture policy for Query Store:

ALTER DATABASE [QueryStoreDB]
 SET QUERY_STORE = ON
 (
 OPERATION_MODE = READ_WRITE,
 CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
 DATA_FLUSH_INTERVAL_SECONDS = 900,
 MAX_STORAGE_SIZE_MB = 1000,
 INTERVAL_LENGTH_MINUTES = 60,
 SIZE_BASED_CLEANUP_MODE = AUTO,
 MAX_PLANS_PER_QUERY = 200,
 WAIT_STATS_CAPTURE_MODE = ON,
 QUERY_CAPTURE_MODE = CUSTOM,
   QUERY_CAPTURE_POLICY = (
   STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
   EXECUTION_COUNT = 30,
   TOTAL_COMPILE_CPU_TIME_MS = 1000,
   TOTAL_EXECUTION_CPU_TIME_MS = 100
   ) );

Note: Except for STALE_CAPTURE_POLICY_THRESHOLD, these options define the OR conditions that need to happen for queries to be captured in the defined Stale Capture Policy Threshold value.

QUERY_CAPTURE_MODE = CUSTOM,
 QUERY_CAPTURE_POLICY = ( 
STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS, 
EXECUTION_COUNT = 30, 
TOTAL_COMPILE_CPU_TIME_MS = 1000, 
TOTAL_EXECUTION_CPU_TIME_MS = 100 
)

Summary

Query Store in SQL Server has been a popular feature since the SQL Server 2016 release to provide the ability for Query Store to act as a “flight data recorder” for SQL Server. Query Store accomplishes this by collecting information about query performance over time. As a result, Query Store has a strong capability to reduce the time it takes for a database administrator to respond to critical performance events and help ease the migration of databases between on-premises systems (hardware and SQL Server releases) as well as migrate database applications to the cloud.

DBAs use Query Store in many of the following scenarios to:

Ensure the health of database upgrades and migrations.
Discover and address application performance regressions.
Tune in the most expensive queries based on resource consumption (elapsed time, compute overhead, memory, I/O, and more).
Maintain performance between database compatibility level upgrades.
And many other Query Store Usage Scenarios.

SQL Server 2022 now supports the enabled by default behavior and introduces the ability for the Query Processor to be able to leverage the historical performance data.

This capability extends Query Store’s power beyond troubleshooting and migration scenarios, and to the next level of intelligent query processing behavior in SQL Server.

Next steps

Download the latest release of SQL Server 2022 if you haven’t already done so and check out the SQL Server 2022 Overview and What’s New references. There are many new features and improved functionality being added to this release.