The SQL Server Query Store is a relatively new feature introduced in SQL Server 2016. It is basically a SQL Server “flight recorder” or “black box”, capturing a history of executed queries, query runtime execution statistics, execution plans etc. against a specific database. This information helps in identifying performance problems caused by query plan changes and troubleshooting by quickly finding performance differences, even after SQL Server restart or upgrade. All data that SQL Server Query Store capture are stored on disk.
This article will cover an explanation of the SQL Server Query Store options and a brief description of the SQL Server Query Store built-in reports.
Here are the common scenarios where the SQL Server Query Store feature can be useful:
- Find the most expensive queries for CPU, I/O, Memory etc.
- Get full history of query executions
- Get information about query regressions (a new execution plan generated by query engine is worse than the older one). Quickly find performance regression and fixing it by forcing the previous query plan whose performances are much better than a newly generated plan
- Determine how many times a query was executed in the given range of time
Quick facts
- The SQL Server Query Store is per-database-level feature which means that it can be enabled on every SQL database separately by using SQL Server Management Studio or T-SQL. It is not an instance level setting.
- The SQL Server Query Store allows analyzing query performance using built-in reports and DMWs quickly and easily.
- The SQL Server Query Store feature is available on every SQL Server editions.
- Note, on Azure databases, the SQL Server Query Store feature is enabled by default.
-
The minimum permissions for using the SQL Server Query Store is VIEW DATABASE STATE permission:
123USE AdventureWorks2014GOGRANT VIEW DATABASE STATE TO [USER];
- This permission is not for forcing execution plan, data flush Interval etc. For this, you need to have db_owner fixed database role.
Setup
To enable SQL Server Query Store for a database on on-promise SQL Server, right click on a database in Object Explorer and from the context menu, choose the Properties option:
From the Database Properties dialog in the Select a page section, select the Query Store page:
From the Operation Mode (Requested) drop down box, choose the Read Write item:
As soon as the Read Write item is chosen, the other fields under the Operation Mode (Requested) drop down box will be pre-populated with default values:
By pressing the OK button on the Database Properties dialog, the SQL Server Query Store is enabled to capture query execution plans and runtime information.
To confirm that the SQL Server Query Store is enabled on the chosen database, go to Object Explorer, refresh and expand the database. The SQL Server Query Store folder will appear with the list of available built-in reports:
To enable the SQL Server Query Store by using T-SQL execute the following statement in a query window:
|
1 |
ALTER DATABASE [AdventureWorks2014] SET QUERY_STORE = ON; |
Note, SQL Server Query Store cannot be enabled for master or tempdb database.
The SQL Server Query Store will have performance impact on SQL Server 3-5% on average by Microsoft indication.
Options
The first option under the SQL Server Query Store page of the Database Properties dialog is the Operation Mode (Active) option:
This option is disabled and cannot be changed. This option indicates the state of the SQL Server Query Store. There are three modes of the SQL Server Query Store and those are Off, Read Only and Read Write.
Off – The SQL Server Query Store turned off
Read Only – This mode indicates that new query runtime statistics or executed plans will not be tracked (collected)
Read Write – Allows capturing query executed plans and query runtime statistics
In the Operation Mode (Requested), in the drop-down box, the same options can be set as those mentioned for the previously option. Setting here the values will directly have impact on the state of the Query Store.
For example, if in the Operation Mode (Requested), from the drop-down box the Read Only value is chosen:
The equivalent T-SQL code for the same option is:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( OPERATION_MODE = READ_ONLY ); |
In the Data Flush Interval (Minutes) option, an interval in minutes can be set which shows how frequent the query runtime statistics and query execution plans will be flushed from memory of SQL Server instance to disk. By default, this option is set to 15 minutes:
If this option is set to lower value than the frequency of flushes, data from memory to disk will occur often which will have negative impact on performance of SQL Server instance. But If the value is increased, more SQL Server Query Store information will be placed in memory of the SQL Server instance before it flushes to disk, which increases a risk of losing that data in case of SQL Server restart/crashes.
Below is the T-SQL code for setting the Data Flush Interval (Minutes) option:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( DATA_FLUSH_INTERVAL_SECONDS = 900 ); |
The Statistics Collection Interval option defined aggregation interval of query runtime statistics that should be used inside the SQL Server Query Store. By default, it is set to 60 minutes. Lower value means that granularity of query runtime statistics is finer, because of that, more intervals occur which requires more disk space for storing query runtime statistics.
The T-SQL code for setting the Statistics Collection Interval option:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( INTERVAL_LENGTH_MINUTES = 1440 ); |
Note, in the T-SQL code for the Statistics Collection Interval option the following values in minutes 1, 5, 10, 15, 30, 60, 1440 can be set.
In case that is in INTERVAL_LENGTH_MINUTES set some other number:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( INTERVAL_LENGTH_MINUTES = 900 ); |
The following message will appear when the statement is executed:
Msg 12432, Level 16, State 1, Line 1
SQL Server Query Store Interval length cannot be changed because an invalid value was provided. Please try again with a valid value (1, 5, 10, 15, 30 & 60).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
The Max Size (MB) option is for configuring the maximum size of the SQL Server Query Store. By default, the maximum size of the SQL Server Query Store is set to 100 MB. The data in the SQL Server Query Store is stored in the database where the SQL Server Query Store is enabled. The SQL Server Query Store doesn’t auto grow and once the SQL Server Query Store reaches the maximum size, the Operation Mode will be switched to the Read Only mode, automatically, and new query execution plan and query runtime statistics will not be collected:
The T-SQL code for setting maximum size of the SQL Server Query Store is:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( MAX_STORAGE_SIZE_MB = 1024 ); |
The Query Store Capture Mode option determines what type of query will be captured in the Query Store. By default, the Query Store Capture Mode option is set to All, which means that every executed query will be stored in the SQL Server Query Store that runs on the database. When the Query Store Capture Mode option is set to Auto then the SQL Server Query Store will try to triage which query capture, by priority, and try to ignore infrequently executed and other ad hoc queries. Also, there is the third value in the Query Store Capture Mode drop down box which is None. When the None value is chosen, then the SQL Server Query Store will not gather information for the new queries and will continue gathering information only on the queries that it has been recorded previously:
The T-SQL code to set this option is:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( QUERY_CAPTURE_MODE = ALL ); |
The Size Based Cleanup Mode option is for cleaning the SQL Server Query Store data when the maximum size in the Max Size (MB) option is reached to 90% of capacity. The cleanup process will remove the oldest and less expensive query data. The cleanup process stops when 80% of the maximum size in the Max Size (MB) option is reached. By default, this option is set to Auto. If in the Size Based Cleanup Mode drop down box the Off value is set, then the cleanup process will not be performed when the size of the SQL Server Query Store reaches 90% of the maximum size and the SQL Server Query Store will go to Read Only mode when the maximum size is reached:
The T-SQL code to set this option is:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( SIZE_BASED_CLEANUP_MODE = AUTO ); |
The Stale Query Threshold (Days) option is for defining how long the data will stay in the SQL Server Query Store. By default, it is set for 30 days
The T-SQL code to set this option is:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30) ); |
On more options that can be set via T-SQL and not present in the SQL Server Query Store tab is MAX_PLANS_PER_QUERY:
|
1 2 3 4 5 6 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( MAX_PLANS_PER_QUERY=200 ); |
With this option, maximum Execution Plans can be set that will be stored in the SQL Server Query Store per query. By default, this is set to 200 Execution Plans per query.
The last option on the SQL Server Query Store tab is an option that clears/purges all data in the SQL Server Query Store by pressing the Purge Query Data button:
The same can be done by executing the following T-SQL code in the query window:
|
1 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE CLEAR; |
Architecture and collection
The SQL Server Query Store collects the query text, query plan and the query run time statistics and place that in the memory.
Note, query plans that show the SQL Server Query Store are estimated execution plans not actual execution plans.
Note: More about estimated and actual plans can be found on the SQL Server Estimated Vs Actual Execution Plans article.
The SQL Server Query Store extracts every query statement from the query. For example, if in the stored procedure multiple query statements exist, the SQL Server Query Store will store every query statement separately and get the query run time statistics from every query statement.
The unique query plan and query text, if new, will directly be passed to ASYNC Writer which will write it to disk.
The query run time statistics will not be directly written to the ASYNC Writer. The query run time statistics will be written at a specific time interval that is set in the Data Flush Interval (Minutes) option.
The SQL Server Query Store gathers all information together no matter if the information is stored on the disk or in memory and shows them in built-in reports:
Reports
As mentioned earlier in the article, once enabled, the SQL Server Query Store will start to collect query runtime statistics and query Execution Plans. Built-in reports use collected data analyzing them and show them in grid or chart format depending on what is set in the reports.
Currently, there are six built-in reports
Regressed Queries is a built-in report that shows all queries that execution matrices are degraded in specific time range (last hour, day, week)
The Regressed Queries built-in report is divided in several pans. By default, the top 25 regressed queries in the last hour are shown.
Here, different options can be set in order to see desired information. For example, if want to see the raw date instead of the graph press the button for showing regressed queries in a grid format:
The Overall Resource Consumption built-in report shows summary resource consumption during the specific set of time. By default, results are shown for the last month and the results are shown in four charts: Duration, CPU Time, Logical Reads and Execution count:
To set additional charts report, time and aggregation interval, press the Configure button and the Configure Overall Resource Consumption dialog will appear where different options can be set for the Overall Resource Consumption report:
The Top Resource Consuming Queries built-in report shows, by default, the top 25 queries against specific database that consume most of resources like CPU Time, Memory Consumption, Physical Reads etc. over specific set of time:
With the Tracked Queries built-in report, query runtime statistics and query Execution plans can be tracked for the specific query over time. In the Tracking query text box, enter the query id (e.g. 205) and press the green play button next to the Tracking query box:
The Queries With Forced Plans built-in report shows all forced Execution Plans for specific queries:
To force SQL Server to use a specific Execution Plan for the particular query, in the Regressed Queries, Top Resource Consuming Queries, Queries With Hight Variation or Tracked Queries built-in reports, first select the Execution Plan Id and click the Force Plan button:
Press the Yes button on the Confirmation message box:
By doing this, you force SQL Server to use this Execution Plan for specific query from now on when that query is executed. This means that SQL Server will not generate a new Execution Plans for that query until unforce that plan.
To unforce SQL Server to use a specific Execution Plan for the particle query in the Queries With Forced Plans, Regressed Queries, Top Resource Consuming Queries, Queries With High Variation or Tracked Queries report, select the Execution Plan and press the Unforce Plan button:
In the Confirmation message box, press the Yes button to confirm removing forced Execution Plan:
This will remove the Execution Plan from the Queries With Forced Plans report.
The Queries With High Variation built-in report analyze the queries and show the queries with the most frequent parameterization problems:
Conclusion
The SQL Server Query Store is an powerful feature, for users of SQL Server 2016 and higher, that keeps tracking of query runtime execution and query execution plans, monitoring and analyzing the performance of queries and showing results in built-in reports. This provides powerful new capabilities comparedto previous methods DBAs were relegated to using in previous versions of SQL Server
He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more.
In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music.
See more about Marko at LinkedIn
View all posts by Marko Zivkovic
- How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
- How to install MySQL on Ubuntu - March 10, 2020
- Using SSH keys to connect to a remote MySQL Server - November 28, 2019