In this blog I’m going to introduce Query Store, how it can help you with troubleshooting performance, and then walk through how to view the tool.

Query Store is a feature that was introduced to SQL Server 2016 that can give insight into query history, plans being used, and runtime statistics. This information can be used to troubleshoot performance issues as well as help understand a query’s impact on resource consumption. The feature boasts a straightforward graphical user interface that can simplify performance tuning for a DBA or make it more accessible for the uninitiated.

A scenario that Query Store is useful for is monitoring regressed queries or identifying your most expensive queries during performance testing in a sandbox. Another scenario is Query Store can be reviewed from the production database for everyday performance troubleshooting. It’s important to note that you cannot view this data from production directly without restoring it to a sandbox first. Because Query Store is enabled by default and is a part of the AXDB database (the Finance and Operations application database), it will come along with a database movement operation when restored to the sandbox environment.

If you are interested in analyzing Query Store data from production, change the operational mode to read-only after the database is restored to preserve what was captured in production. If this step isn’t completed, the data collected from production begins to be overwritten with runtime data collected in the sandbox itself.

Before we walk through using Query Store, you will need to request Just-in-time (JIT) access to the sandbox database you are interested in. You can use a blog I wrote for this by clicking here. Beyond this point, I will assume you have requested JIT access to your sandbox database and are connected to the  SQL Server database instance.

 

Setting the Query Store to Read-only in a Sandbox

When you bring down a fresh copy of the production database to a sandbox using a database movement operation, you can follow the below steps to preserve production Query Store data. It’s possible to accomplish this using SQL queries as well, but I will be using the GUI in SQL Server Management Studio.

  1. From Object Explorer, expand Databases under your current SQL Database Engine connection.
  2. Right click your AxDB database and click properties. (This database will be named something like ‘db_d365opsprod_xyz_ax_etc’ and is the ‘AXDB’ database you requested JIT access to if you followed my blog linked in the previous section).
    This screenshot is showing the object explorer pane of SQL server management studio. The databases node is expanded and the AXDB database is highlighted to show which database to right click to access the properties menu.
  3. Click the Query Store page
  4. Click the drop down next to Operation Mode (Requested) and select Read only.
    This screenshot shows the properties page for the database and has the query store page selected. The query store page is highlighted as well as the operation mode configuration to show this is where you configure the query data store to read only.
  5. Click OK.

 

How to Start Using Query Store

  1. From the Object Explorer, expand the contents of your AxDB
  2. Expand the Query Store node to show the below Query Store views. We’re going to look at Top Resource Consuming Queries.
    1. Regressed Queries
    2. Overall Resource Consumption
    3. Top Resource Consuming Queries
    4. Queries with Forced Plans
    5. Queries With High Variation
    6. Tracked Queries
  3. Double click the Top Resource Consuming Queries view.
  4. A screen will open showing the top 25 resource consumers for the database. My data isn’t very interesting as it’s from a lightly used test environment. Yours may look different.
     This screenshot shows the top 25 resource consumers screen after clicking the top resources consuming queries view. It is a bar chart of query execution times by duration, it also demonstrates there are 2 query plans being used by a query we have clicked on, and also shows the execution plan of the query plan on the bottom half of the image.


The screen will default to the bar chart view where you can hover over each bar to see the query, duration, and execution counts. In my screenshot, I am looking at the duration (ms) metric and total duration selected for the axis. These would be your longest running queries.

In the top right, you can see there are two query plans being used by the query I have clicked on in the bar chart. The blue and pink represent different query plans and are charted in the graph based on total duration as shown in the screenshot. You can also click between the plans under ‘Plan Id’ to view the query plan at the bottom section of this screen. I’ve also highlighted a ‘…’ button next to the query that can be clicked to open a new editor window with the full query copied into it.

Query tuning is a broad topic that is outside the scope of this blog, but when viewing the query plan, you will generally read this as a tree with the execution logically flowing from right to left. Each icon is an operator and displays a cost percentage relative to the total cost of the query.

If you hover the mouse over the operator furthest to the left, you can see the estimated subtree cost of the query in the tool tip. This number is the estimated total cost (in terms of CPU/IO requirements) that SQL Server thinks it’s going to need to execute the query. The higher the number means the more expensive it is. If your estimated subtree cost decreases after tuning a query, that is a great indicator to show how much you have decreased the total cost in addition to how much faster the query executes.

If you click between the available query ids for a query (note: some may only have one query plan), you can view the differences between the plans. Viewing the differences in plans can be important because it may reveal why a query is slow in one instance over another. You can also click the force plan button to force the query to always use a particular plan which would only be useful if performance testing from this environment. If you find a bad plan being used and have tested that it solves a performance problem, you can force plan ids in production from Lifecycle Services under SQL Insights.

I hope you explore the other options not covered in this blog and that this gives you more insight into the expensive queries in your environment. It may turn out there isn’t a way to speed up a query but proactively monitoring query performance is always worth your time, especially if customizations are present in your implementation. I also encourage you to look at the other Query Store views, such as the Regressed Queries view, which can show how much more time queries are taking to run now vs historical execution times. I will revisit Query Store in future blogs, but until then, thanks for reading my introductory blog on Query Store!

I want to acknowledge and give a special thanks to Matt Go for peer reviewing this blog. Please comment below if you found this helpful and connect with me on LinkedIn or Twitter! My information is listed in my profile and I hope to hear from you soon!