There are a number of options to monitor Azure SQL Database. In this post I will briefly cover the built-in options and not 3rd-party products that I blogged about a while back (see Azure SQL Database monitoring).
Monitoring keeps you alert of problems. Another reason monitoring helps you is to determine whether your database has excess capacity or is having trouble because resources are maxed out, and then decide whether it’s time to adjust the performance level and service tiers of your database. You can monitor your database using:
- Graphical tools in the Azure portal (click “Resource” on the Overview blade): monitor a single database’s metrics of CPU percentage, DTU percentage, Data IO percentage, Database size percentage and more. You can configure alerts if metrics exceed or fall below a certain threshold over a time period – click “Alerts (Classic)” under “Monitoring”.
- Use SQL dynamic management views (DMV): The two main one’s are sys.resource_stats in the logical master database of your server, and sys.dm_db_resource_stats in the user database. You can use the sys.dm_db_resource_stats view in every SQL database. The sys.dm_db_resource_stats view shows recent resource use data relative to the service tier. Average percentages for CPU, data IO, log writes, and memory are 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. The sys.resource_stats view in the master database has additional information that can help you monitor the performance of your SQL database at its specific service tier and performance level. The data is collected every 5 minutes and is maintained for approximately 14 days. This view is useful for a longer-term historical analysis of how your SQL database uses resources. See Monitoring Azure SQL Database using dynamic management views for other DMV’s you might want to use
- Monitor resource usage using SQL Database Query Performance Insight (requires Query Store). Review top CPU consuming queries and view individual query details
- Azure SQL Intelligent Insights is proactive monitoring that uses built-in intelligence to continuously monitor database usage through artificial intelligence and detect disruptive events that cause poor performance. Once detected, a detailed analysis is performed that generates a diagnostics log (usually to Azure Log Analytics) with an intelligent assessment of the issue. This assessment consists of a root cause analysis of the database performance issue and, where possible, recommendations for performance improvements. Intelligent Insights analyzes SQL Database performance by comparing the database workload from the last hour with the past seven-day baseline workload. It also monitors absolute operational thresholds and detects issues with excessive wait times, critical exceptions, and issues with query parameterizations that might affect performance. The system automatically considers changes to the workload and changes in the number of query requests made to the database to dynamically determine normal and out-of-the-ordinary database performance thresholds. Integration of Intelligent Insights with Azure Log Analytics is performed through first enabling Intelligent Insights logging (selecting “SQLInsights” under LOG) and then configuring Intelligent Insights log data to be streamed into Azure Log Analytics, which is a feature of the Operations Management Suite (OMS)
- Azure SQL Analytics: provides reporting and alerting capabilities on top of the Intelligent Insights and other diagnostics log data as well as metric data
Other ways of monitoring SQL Database:
- Overall Azure service health: See Track service health
- Azure Resource Health: Helps you diagnose and get support when an Azure service problem affects your resources. It informs you about the current and past health of your resources. And it provides technical support to help you mitigate problems.
- SQL Database Auditing: See Get started with SQL database auditing and Monitor your Azure SQL Database Auditing activity with Power BI
- Unified Alerts: A new unified alert experience that allows you to manage alerts from multiple subscriptions and introduces alert states and smart groups. Define your alert criteria by choosing a signal (ie. create a database) and defining your alert condition, alert details, and action group (ie. send a text)
- Emit metrics and diagnostics logs: Azure SQL Database can emit metrics and diagnostics logs for easier monitoring. You can configure SQL Database to store resource usage, workers and sessions, and connectivity into Azure Storage, Azure Event Hubs, or Azure Log Analytics
- SQL Database Threat Detection: See Get started with SQL Database Threat Detection
- Extended Events: See Extended Events for Azure SQL Database
- System Center: See Microsoft System Center Management Pack for Microsoft Azure SQL Database