Query Store is a feature introduced in SQL 2016 that keeps track of multiple metrics (CPU, duration, I/O…) of the queries executed in your database. This information is aggregated at configurable intervals, so the activity can be analysed with different levels of detail depending on your needs.
Although this functionality has been available since SQL 2016, starting SQL 2017 wait statistics are being captured as well, along with the corresponding changes in SSMS to access these new metrics’ reports.
However, I don’t recommend enabling this functionality on just any SQL 2017 instance you can get your hands on. A fix was released in SQL 2017’s Cumulative Update 15 to solve a bug that would cause access violation errors when accessing corrupted execution plans. Without this patch, opening the Query Store reports may wind up loading on of such corrupted plans: best case you will get an error message and a memory dump; worst case your instance will crash.
Enabling Query Store
Query Store is enabled on a per-database basis, so open the properties of the database you want to enable it on.
Let’s start by enabling it with the default values. A detailed explanation of the available settings will be provided in a following post, but for the purpose of learning how to use the SSMS interface built on top of it, the defaults will serve just fine
Starting now, the Query Store is capturing details of the queries that are being executed on the database, provided they exceed the minimum threshold set in the AUTO capture mode by SQL server. These thresholds are not published, but they are meant to exclude queries that, due to their low number of executions and small impact on the SQL engine (that being CPU, duration, I/O, memory….) are not deemed worth of being captured by the SQL engine.
Accessing the reports
The reports shipped with SSMS cover most if not all the data captured by Query Store
Regressed Queries
These are those queries whose metrics have worsened. These metrics can measure a number of values, from total duration (the default used), to standard deviation of the queries’ memory consumption, and selecting one or another will change the queries flagged as regressed.
This is a useful report when there is a recent change in the SQL performance, such as a sudden increase in the CPU, or deterioration of a particular query’s performance. If the issue has been caused by a change in the execution plan, just select the ones you want to compare and hit the “Compare Plans” button (only two plans at a time).
Overall Resource Consumption
This report gives an overview of some basic measurements. In my opinion, whilst the “CPU Time” can give you an idea on how much of the server’s CPU was occupied by queries executed on the current database, the other metrics, lacking the proper context, don’t provide useful information.
Top Resource Consuming Queries
This report sort the queries captured by any metric selected. The default is “Duration (ms) / Total”, but any combination is possible.
Queries With Forced Plans
Query Store allows you to force a specific plan to be used for a query. This can be useful when outdated statistics would otherwise persuade the SQL engine to use a plan that would cause a performance impact, such as switching from an index seek to an index scan operation. Those queries can be found using this view.
Queries With High Variation
There are queries that may have a very regular duration over time, but with certain executions that deviate from the usual behaviour. This report will highlight them so you can identify whether they represent an issue and should be looked further into.
Query Wait Statistics
Since the query wait statistics were introduced in SQL 2017, they were not part of the original system tables and don’t fully integrate with the pre-existing reports, so this separate report fills that need. It lets you drill down from the general view (all wait types), to the specific queries that incurred in that specific wait type.
Tracked Queries
Contrary to what the name seems to indicate, we can’t select queries to track information on them over, or have a filtered view of Query Store’s data on them. This is not a report but a detailed view of an specific query, so once a specific query has been identified details about it can be seen here.
Notes on this post
All the tests have been performed on a SQL 2019 instance using SSMS 18.4. Different versions of both SQL server and the SSMS client may present slight differences.
Also, in order to capture query details for the post, I modified some of the settings in Query Store such as capture mode to ALL (in order to capture every query executed), and the Statistics Collection Interval reduced to 1 minute so quick changes would be captured by Query Store rather than waiting for it to capture several hours of data before obtaining any report.