Exploring SQL Server 2016 Query Store GUI

Introduction

Query store is a new feature, introduced in SQL Server 2016, that allows database administrators to historically review queries and their associated plans using the GUI available in SQL Server Management Studio, as well as to analyze query performance using certain Dynamic Management Views. Query Store is a database scoped configuration option and is available for use if the compatibility level of the database in question is 130.

Query Store is similar to such technologies in the Oracle database platform as Automatic Workload Repository (AWR). The AWR captures performance statistics on an even greater scale than Query Store and allows a database administrator to historically analyze performance. Such concepts as a retention period and storage limits of data collected are available in the AWR architecture as they are in Query Store. The following key configuration options are available when enabling Query Store:

  • Operation Mode: Determines if Query Store will accept newly captured data (ReadWrite Mode) or merely store old data available for reports (Read-only Mode)
  • Data Flush Interval: Determines how often the Query Store memory buffers are flushed to a disk. Recall that Query Store data is stored in the database where Query Store is enabled. The default value is 15 minutes.
  • Statistics Collection Interval: Determines how often query store runtime statistics are collected.
  • Maximum Size: Determines on how much the repository for Query Store statistics can grow. By default, it is 100MB.
  • Query Store Capture Mode: Determines the granularity of query captures. ALL, AUTO, and NONE are the available options. The default value is AUTO.
  • Size Based Cleanup Mode: Determines whether Query Store will flush old data when the maximum size is reached.
  • Stale Query Threshold: Determines the number of days for which Query Store retains data. The default value is set to thirty days.
Fig. 2 Query Store Options

Query store is a database scoped feature that can be enabled either by using the GUI (SQL Server Management Studio) or by running the following command:

The query telemetry collected by Query Store is stored in system tables within the database where Query Store has been enabled.

Sample Queries & Default Reports

So far everything I have written is available from many other sources; some of them can be found in the references section.

In this section, we shall take a little bit deeper look at what we can actually do with Query Store once we have enabled it using simple examples. Let’s consider the following two queries:

Listing 1: Fetching Records using a Specific Filter

Listing 2: Fetching Records using a Range

Pay attention to the alternate version of these queries written in the upper case:

Listing 1: Fetching Records using a Specific Filter (Upper Case)

Listing 2: Fetching Records using a Range (Upper Case)

As you can see, we ran these queries multiple times using the GO keyword. Thus, we have some reasonable amount of data to work with. The first thing we should be aware of when using Query Store to analyze performance is that there are six default reports built into the SQL Server 2016 Query Store as shown in Fig. 3.

Fig. 3 Query Store Reports

The names of the reports are described in details in the previous articles as well as in the Microsoft documentation. The data provided by these reports is retrieved from the key Dynamic Management Views listed below:

Plan Stats DMVs

Run Time Stats DMVs

Much more details on how to use these DMVs are available in the referenced Microsoft Documentation. In this article, we shall simply use the GUI mostly.

As you can see from Fig. 4, we review the Overall Resource Consumption report while in the next section, we are going to narrow down to the queries we listed earlier and the data we can retrieve from these simple queries.

Fig. 4 Overall Resource Consumption Report

Analyzing Queries Using the GUI

A few key things should be considered helpful when using the Query Store Reports:

  • You can configure the environment by clicking the button highlighted in Fig 4. Fig. 5 shows us the details we can change to suit our use case: criteria of data to be returned, date range, and data set to be returned. For example, if I want to see clearly the Query ID associated with the queries I am reviewing I would like to reduce my data set from the default Top 25 to Top 10, for example.
    Fig. 5 Report Configuration Options
    Fig. 6 Top 25 Queries Executed on 1st May 2018
    Fig. 7 Top 10 Queries Executed on 1st May 2018

     

  • The bar charts display data mostly with time on the x-axis but you can drill down a specific data point to view data based on Query IDs. Each Query ID determines a specific query. It is important to note that a query is uniquely identified by hashing the text. Thus, a query in the lower case differs from the same query in the upper case. This should be a common knowledge: ad-hoc queries are a concern for the plan cache and they are also bad for the query store both in terms of space usage and proper analysis.
    Fig. 8 Query in Listing 1 (Lower Case, Query 42480)
    Fig. 9 Query in Listing 3 (Upper Case, Query 42490)
  • The third important observation is the fact that when a data point is highlighted green, the detailed execution plan shown in the lower pane pertains to that data point. In Fig. 7, this data point refers to Query ID 42481 which we earlier executed (full query shown in Listing 2). Hovering our mouse over this data point displays the query, its ID, and the number of plans associated with this query (see Fig. 8).
    Fig. 10 Query 42481 Details

    Our query was executed 1391 times as it is accurately captured by Query Store and displayed in the y-axis (execution count) of the bar chart in Fig. 10. The report was being pulled while the batch was still executing. Thus, we do not have the full count (1500) informing us that there is a real-time capture of data each time a query is executed. On the right-hand side, we also see the Plan being used for these multiple executions (Plan 675). We can verify this using the query in Listing 5.

Listing 5

Fig. 11 Query ID and Plan ID from DMVs

A Little Bit of Tuning

Let’s take a look at another query.

When we run the query in Listing 6 and examine the details from Query Store, the execution plan details reveal that we need an index to get 51% improvement.

Listing 6: Sub-optimal Query

Fig. 12 Sub-optimal Query Details
Fig. 13 Sub-optimal Query Execution Plan

Once we create the recommended index using the statement in Listing 7, we cause the Query Optimizer to generate a new execution plan. In this case, the new execution plan is expected to improve performance. However, there are cases where certain changes may cause degraded performance such as significant changes in data volume that effectively invalidate statistics or drop the number of indexes and so on. Such queries are said to have regressed in performance and can be examined using the Regressed Queries report in Query Store.

Listing 7: Index Creation

Fig. 14 Optimized Query (Change in Execution Plan)
Fig. 15 Optimized Query (Two Plans)
Fig. 16 Optimized Query (Force Plan)

When there are multiple plans for a query as shown in Fig. 14 and Fig. 16, we can tell the optimizer to always use a plan we choose by clicking the Force Plan button. This was a bit tedious task in the previous versions of SQL Server.

As you can see from Fig. 17, Query Store allows us to compare the different plans associated with a query using a number of metrics.

Fig. 17 Comparing Execution Plans

Again, we can use the query in Listing 8 to verify the plans associated with this query ID using DMVs. (Refer to Fig. 11)

Listing 8: Plans Associated with Query 42497

Exploring Variations

Another useful report that Query Store avails us is the Queries With High Variation. This report shows us how far apart the desired metrics are for a specific query over a given period. This is very useful for historical analysis of performance. Using the statements in Listing 9, we generate data that can give a picture of what variations would look like. The steps in the procedure simply create a small table and then insert records using different batch sizes.

Listing 9: Plans Associated with Query 42497

Query Store shows us details such as the minimum and maximum values of these metrics for specific execution intervals of the query we are interested in. In this example, we find that this is simply a result of the number of batches per execution (note that the parameters are actually being used to execute the INSERT statement). In the production, other factors could be responsible for such variations.

Fig. 18 Variations in Duration
Fig. 19 Variation in Logical Writes
Fig. 20 Variation in Physical Reads

Conclusion

In this article, we have reviewed the SQL Server 2016 Query Store GUI environment and a few things we can deduce regarding the performance of our instance (with respect to SQL) using Query Store. There are several articles on the Internet that show even more advanced use cases and much deeper explanation of the internals. This article should be of good use to mid-level DBAs who want to get a head start in using Query Store for performance evaluation/tuning.

References

Kenneth Igiri

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud.
Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.
Kenneth Igiri