Monitoring Azure SQL Database

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:

More info:

Monitoring database performance in Azure SQL Database

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in Azure SQL Database, SQLServerPedia Syndication. Bookmark the permalink.

Comments are closed.