Configure Query Store to collect performance data
In this we will learn about how to interpret performance metrics. Query Performance Insight provides intelligent query analysis for single and pooled databases. Moreover, it helps identify the top resource consuming and long-running queries in your workload.
Use Query Performance Insight
Query Performance Insight is easy to use:
- Firstly, open the Azure portal and find a database that you want to examine.
- Secondly, from the left-side menu, open Intelligent Performance > Query Performance Insight.
- Thirdly, on the first tab, review the list of top resource-consuming queries.
- Then, select an individual query to view its details.
- After that, open Intelligent Performance > Performance recommendations and check if any performance recommendations are available.
- Lastly, use sliders or zoom icons to change the observed interval.
Review top CPU-consuming queries
By default, Query Performance Insight shows the top five CPU-consuming queries when you first open it.
- Firstly, select or clear individual queries to include or exclude them from the chart by using check boxes.
- Secondly, if your data becomes stale, select the Refresh button.
- Thirdly, use sliders and zoom buttons to change the observation interval and investigate consumption spikes:
- Optionally, you can select the Custom tab to customize the view for:
- Metric (CPU, duration, execution count).
- Time interval (last 24 hours, past week, or past month).
- Number of queries.
- Aggregation function.
- Lastly, select the Go > button to see the customized view.
View individual query details
To view query details:
- Firstly, select any query in the list of top queries.
- Secondly, select the chart features for details.
- Firstly, the top chart shows a line with the overall database DTU percentage. The bars are the CPU percentage that the selected query consumed.
- The second chart shows the total duration of the selected query.
- Lastly, the bottom chart shows the total number of executions by the selected query.
- Optionally, use sliders, use zoom buttons, or select Settings to customize how query data is displayed, or to pick a different time range.
Review top queries per duration
Two metrics in Query Performance Insight can help you find potential bottlenecks: duration and execution count. However, long-running queries have the greatest potential for locking resources longer, blocking other users, and limiting scalability. They’re also the best candidates for optimization.
To identify long-running queries:
- Firstly, open the Custom tab in Query Performance Insight for the selected database.
- Secondly, change the metrics to duration.
- Thirdly, select the number of queries and the observation interval.
- Then, select the aggregation function:
- Sum adds up all query execution time for the whole observation interval.
- Max finds queries in which execution time was maximum for the whole observation interval.
- Avg finds the average execution time of all query executions and shows you the top ones for these averages.
- Lastly, select the Go > button to see the customized view.
Optimize the Query Store configuration
While using Query Performance Insight, you might see the following Query Store error messages:
- Firstly, “Query Store is not properly configured on this database. Click here to learn more.”
- Secondly, “Query Store is not properly configured on this database. Click here to change settings.”
These messages usually appear when Query Store can’t collect new data.
However, the first case happens when Query Store is in the read-only state and parameters are set optimally. You can fix this by increasing the size of the data store, or by clearing Query Store. And, the second case happens when Query Store is not enabled, or parameters are not set optimally. You can change the retention and capture policy, and also enable Query Store, by running the following commands provided from SQL Server Management Studio (SSMS) or the Azure portal.
Reference: Microsoft Documentation