Interpret performance metrics
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.
Monitoring and performance tuning in Azure SQL Database and Azure SQL Managed Instance
To monitor the performance of a database in Azure SQL Database and Azure SQL Managed Instance, start by monitoring the CPU and IO resources used by your workload relative to the level of database performance you chose in selecting a particular service tier.
However, Azure SQL Database provides a number of Database Advisors to provide intelligent performance tuning recommendations and automatic tuning options to improve performance. Additionally, Query Performance Insight shows you details about the queries responsible for the most CPU and IO usage for single and pooled databases.
Monitoring and tuning capabilities in the Azure portal
In the Azure portal, Azure SQL Database and Azure SQL Managed Instance provide monitoring of resource metrics. Azure SQL Database provides database advisors, and Query Performance Insight provides query tuning recommendations and query performance analysis. Further, in the Azure portal, you can enable automatic tuning for logical SQL servers and their single and pooled databases.
Azure SQL Database and Azure SQL Managed Instance resource monitoring
You can quickly monitor a variety of resource metrics in the Azure portal in the Metrics view. These metrics enable you to see if a database is reaching 100% of processor, memory, or IO resources. High DTU or processor percentage, as well as high IO percentage, indicates that your workload might need more CPU or IO resources. It might also indicate queries that need to be optimized.
Database advisors in Azure SQL Database
Azure SQL Database includes database advisors that provide performance tuning recommendations for single and pooled databases. These recommendations are available in the Azure portal as well as by using PowerShell. Moreover, you can also enable automatic tuning so that Azure SQL Database can automatically implement these tuning recommendations.
Generate intelligent assessments of performance issues
Intelligent Insights for Azure SQL Database and Azure SQL Managed Instance uses built-in intelligence to continuously monitor database usage through artificial intelligence and detect disruptive events that cause poor performance. Moreover, Intelligent Insights automatically detects performance issues with databases based on query execution wait times, errors, or time-outs. Once detected, a detailed analysis is performed that generates a resource log (called SQLInsights) with an intelligent assessment of the issues.
Intel7ligent Insights is a unique capability of Azure built-in intelligence that provides the following value:
- Firstly, proactive monitoring
- Secondly, tailored performance insights
- Thirdly, early detection of database performance degradation
- Then, root cause analysis of issues detected
- After that, performance improvement recommendations
- Scale out capability on hundreds of thousands of databases
- Lastly, positive impact to DevOps resources and the total cost of ownership
Reference: Microsoft Documentation, Documentation 2