Configure and monitor activity and performance in Azure SQL
In this, we will learn how to configure and monitor activity and performance in Azure SQL.
Start monitoring the CPU and IO resources consumed by your workload in relation to the degree of database performance you choose when selecting a certain service tier and performance level in Azure SQL Database and Azure SQL Managed Instance.
However, Azure SQL Database has a variety of Database Advisors that offer intelligent performance tweaking advice as well as automated tuning choices. 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
Azure SQL Database and Azure SQL Managed Instance enable resource metrics monitoring via the Azure portal. Database advisers are provided by Azure SQL Database, and query optimization tips and performance analysis are provided by Query Performance Insight. You can also activate automated tuning for logical SQL servers and their single and pooled databases in the Azure portal.
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. You may use these metrics to check if a database is using all of its processing, memory, or IO resources. The presence of a high DTU or processor percentage, as well as a high IO percentage, indicates that your workload may require additional CPU or IO resources. It might also point to questions that need to have improvement.
Database advisors in Azure SQL Database
Database consultants are included in Azure SQL Database and give performance adjustment advice for both single and pooled databases. These suggestions are available both in the Azure interface and through PowerShell. You may also activate automated tuning, which will have Azure SQL Database apply these tuning recommendations automatically.
Query Performance Insight in Azure SQL Database
Query Performance Insight shows the performance in the Azure portal of top-consuming and longest-running queries for single and pooled databases.
Generate intelligent assessments of performance issues
Intelligent Insights for Azure SQL Database and Azure SQL Managed Instance employs built-in intelligence to continuously monitor database usage and detect disruptive events that cause poor performance using artificial intelligence. Furthermore, Intelligent Insights diagnoses database performance issues based on query execution delay times, failures, or time-outs automatically. Once recognized, a thorough investigation is carried out, resulting in the creation of a resource log (dubbed SQLInsights) that contains an intelligent evaluation of the problems. This assessment also includes a root cause analysis of the database performance issue and, where applicable, performance enhancement recommendations.
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
Log Analytics workspace in Azure Monitor
In Azure Monitor, you may stream metrics and resource logs to a Log Analytics workspace. Furthermore, the data transmitted here may be ingested by SQL Analytics, a cloud-based monitoring service that delivers intelligent database monitoring, including performance reports, alarms, and mitigation recommendations. Data streamed to a Log Analytics workspace may also be evaluated with other monitoring data, allowing you to take use of Azure Monitor capabilities like alarms and visualisations.
Azure Event Hubs
You can stream metrics and resource logs to Azure Event Hubs. Streaming diagnostic telemetry to event hubs to provide the following functionality:
- Firstly, stream logs to third-party logging and telemetry systems Here, stream all of your metrics and resource logs to a single event hub to pipe log data to a third-party SIEM or log analytics tool.
- Secondly, build a custom telemetry and logging platform. In this, the highly scalable publish-subscribe nature of event hubs allows you to flexibly ingest metrics and resource logs into a custom telemetry platform.
- Lastly, view service health by streaming data to Power BI. Here, use Event Hubs, Stream Analytics, and Power BI to transform your diagnostics data into near real-time insights on your Azure services.
Monitoring Microsoft Azure SQL Database and Azure SQL Managed Instance performance using dynamic management views
A subset of dynamic management views is available in Microsoft Azure SQL Database and Azure SQL Managed Instance to detect performance issues. However, this might be due to things like blocked or long-running queries, resource constraints, and bad query planning, among other things.
Microsoft Azure SQL Database and Azure SQL Managed Instance partially support three categories of dynamic management views:
- Firstly, database-related dynamic management views.
- Secondly, execution-related dynamic management views.
- Lastly, transaction-related dynamic management views.
Identify CPU performance issues
If CPU consumption is above 80% for extended periods of time, consider the following troubleshooting steps:
If issue is occurring right now, there are two possible scenarios:
Many individual queries that cumulatively consume high CPU
Use the following query to identify top query hashes:
SQL
PRINT ‘– top 10 Active CPU Consuming Queries (aggregated)–‘;
SELECT TOP 10 GETDATE() runtime, *
FROM (SELECT query_stats.query_hash, SUM(query_stats.cpu_time) ‘Total_Request_Cpu_Time_Ms’, SUM(logical_reads) ‘Total_Request_Logical_Reads’, MIN(start_time) ‘Earliest_Request_start_Time’, COUNT() ‘Number_Of_Requests’, SUBSTRING(REPLACE(REPLACE(MIN(query_stats.statement_text), CHAR(10), ‘ ‘), CHAR(13), ‘ ‘), 1, 256) AS “Statement_Text” FROM (SELECT req., SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1) AS statement_text
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ) AS query_stats
GROUP BY query_hash) AS t
ORDER BY Total_Request_Cpu_Time_Ms DESC;
Long running queries that consume CPU are still running
Use the following query to identify these queries:
SQL
PRINT ‘–top 10 Active CPU Consuming Queries by sessions–‘;
SELECT TOP 10 req.session_id, req.start_time, cpu_time ‘cpu_time_ms’, OBJECT_NAME(ST.objectid, ST.dbid) ‘ObjectName’, SUBSTRING(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1), CHAR(10), ‘ ‘), CHAR(13), ‘ ‘), 1, 512) AS statement_text
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY cpu_time DESC;
GO
Monitor resource use
SQL Database Query Performance Insight may be used to track Azure SQL Database resource utilisation. Query Store may be used to monitor Azure SQL Database and Azure SQL Managed Instance. These views, on the other hand, may be used to keep track of usage:
- Firstly, Azure SQL Database: sys.dm_db_resource_stats
- Secondly, Azure SQL Managed Instance: sys.server_resource_stats
- Lastly, Both Azure SQL Database and Azure SQL Managed Instance: sys.resource_stats
sys.dm_db_resource_stats
You can use the sys.dm_db_resource_stats view in every database. The sys.dm_db_resource_stats view shows recent resource use data relative to the service tier. In this the average percentages for CPU, data IO, log writes, and memory is recorded every 15 seconds and are maintained for 1 hour.
Because this view provides a more granular look at resource use, use sys.dm_db_resource_stats first for any current-state analysis or troubleshooting. For example, this query shows the average and maximum resource use for the current database over the past hour:
sys.server_resource_stats
You can use sys.server_resource_stats to return CPU usage, IO, and storage data for an Azure SQL Managed Instance. The data is collected and aggregated within five-minute intervals. There is one row for every 15 seconds reporting. However, the data returned includes CPU usage, storage size, IO utilization, and managed instance SKU. Historical data retains for approximately 14 days.
sys.resource_stats
The sys.resource_stats view in the master database has additional information that can help you monitor the performance of your database at its specific service tier and compute size. The data is collected every 5 minutes and is maintained for approximately 14 days.
Furthermore, based on the statistics, this database has a peak CPU load of a little over 50% when compared to the P2 compute size (midday on Tuesday). If CPU is the most important resource in the application’s resource profile, P2 could be the best compute size to ensure that the workload always fits. However, if you expect an application to grow over time, it’s a good idea to add an extra resource buffer so that the programme doesn’t ever approach the performance-level limit. Additionally, increasing the compute size might help eliminate customer-visible failures that can arise when a database lacks the ability to adequately execute queries, particularly in latency-sensitive applications. A database that supports an application that paints websites depending on database calls is one example.
Under each server’s master database, the database engine provides spent resource statistics for each active database in the sys.resource stats view. The information in the table has consolidate into 5-minute intervals. Because the data in the table for the Basic, Standard, and Premium service levels might take up to 5 minutes to display, this data is better suited to historical analysis than near-real-time analysis. Query the sys. resource stats view to observe a database’s recent history and confirm that the reservation you selected provided the performance you need when needed.
This example shows you how the data in this view exposes:
SQL
SELECT TOP 10 *
FROM sys.resource_stats
WHERE database_name = ‘resource1’
ORDER BY start_time DESC
Reference: Microsoft Documentation, Documentation 2