Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2021 Release Wave 1Discover the latest updates and new features to Dynamics 365 planned April 2021 through September 2021.
Release overview guides and videos Release Plan | Preview 2021 Release Wave 1 Timeline
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
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.
How to Start Using Query Store
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!
Business Applications communities