Feedback Edit

Monitor Azure SQL Database with Azure Monitor

  • Article
  • 6 minutes to read

Applies to: Azure SQL Database

When you have critical applications and business processes relying on Azure resources, you want to monitor those resources for their availability, performance, and operation.

This article describes the monitoring data generated by Azure SQL Database. Azure SQL Database can be monitored by Azure Monitor. If you are unfamiliar with the features of Azure Monitor common to all Azure services that use it, read Monitoring Azure resources with Azure Monitor.

Monitoring overview page in Azure portal

View your Azure Monitor metrics for all connected resources by going to the Azure Monitor page directly in the Azure portal. Or, on the Overview page of an Azure SQL DB, select Metrics under the Monitoring heading to reach Azure Monitor.

Azure Monitor SQL Insights (preview)

Some services in Azure have a focused, pre-built monitoring dashboard in the Azure portal that can be enabled to provide a starting point for monitoring your service. These special dashboards are called "insights" and are not enabled by default. For more on using Azure Monitor SQL Insights for all products in the Azure SQL family, see Monitor your SQL deployments with SQL Insights (preview).

After creating a monitoring profile, you can configure your Azure Monitor SQL Insights for SQL-specific metrics for Azure SQL Database, SQL Managed Instance, and SQL Server on Azure VMs.

Note

Azure SQL Analytics (preview) is an integration with Azure Monitor, where many monitoring solutions are no longer in active development. For more monitoring options, see Monitoring and performance tuning in Azure SQL Database and Azure SQL Managed Instance.

Monitoring data

Azure SQL Database collects the same kinds of monitoring data as other Azure resources that are described in Monitoring data from Azure resources.

See Monitoring Azure SQL Database with Azure Monitor reference for detailed information on the metrics and logs metrics created by Azure SQL Database.

Collection and routing

Platform metrics and the Activity log are collected and stored automatically, but can be routed to other locations by using a diagnostic setting.

Resource Logs are not collected and stored until you create a diagnostic setting and route them to one or more locations. Resource logs were previously referred to as diagnostic logs.

Diagnostic settings available include:

  • log: SQLInsights, AutomaticTuning, QueryStoreRuntimeStatistics, QueryStoreWaitStatistics, Errors, DatabaseWaitStatistics, Timeouts, Blocks, Deadlocks
  • metric: All Azure Monitor metrics in the Basic and InstanceAndAppAdvanced categories
  • destination details: Send to Log Analytics workspace, Archive to a storage account, Stream to an event hub, Send to partner solution

For more information on the resource logs and diagnostics available, see Diagnostic telemetry for export.

See Create diagnostic setting to collect platform logs and metrics in Azure for the detailed process for creating a diagnostic setting using the Azure portal, CLI, or PowerShell. When you create a diagnostic setting, you specify which categories of logs to collect. The categories for Azure SQL Database are listed in Azure SQL Database monitoring data reference.

Analyzing metrics

You can analyze metrics for Azure SQL Database alongside metrics from other Azure services using the metrics explorer by opening Metrics from the Monitor menu in the Azure portal. See Getting started with Azure Metrics Explorer for details on using this tool.

For a list of the platform metrics collected for Azure SQL Database, see Monitoring Azure SQL Database data reference metrics

For reference, you can see a list of all resource metrics supported in Azure Monitor.

Analyzing logs

Data in Azure Monitor Logs is stored in tables where each table has its own set of unique properties. This data is optionally collected via Diagnostic settings.

All resource logs in Azure Monitor have the same fields followed by service-specific fields. The common schema is outlined in Azure Monitor resource log schema.

The Activity log is a type of platform log in Azure that provides insight into subscription-level events. You can view it independently or route it to Azure Monitor Logs, where you can do much more complex queries using Log Analytics.

For a list of the types of resource logs collected for Azure SQL Database, see Resource logs for Azure SQL Database.

For a list of the tables used by Azure Monitor Logs and queryable by Log Analytics, see Azure Monitor Logs tables for Azure SQL Database.

Sample Kusto queries

Important

Selecting Logs from the Monitoring menu of a database opens Log Analytics with the query scope set to the current database. This means that log queries will only include data from that resource. If you want to run a query that includes data from other databases or data from other Azure services, select Logs from the Azure Monitor menu. See Log query scope and time range in Azure Monitor Log Analytics for details.

Note

Occasionally, it might take up to 15 minutes between when an event is emitted and when it appears in a Log Analytics workspace.

Use the following queries to monitor your database. You may see different options available depending on your purchase model.

Example A: Log_write_percent from the past hour

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where TimeGenerated >= ago(60min)
| where MetricName in ('log_write_percent')
| parse _ResourceId with * "/microsoft.sql/servers/" Resource
| summarize Log_Maximum_last60mins = max(Maximum), Log_Minimum_last60mins = min(Minimum), Log_Average_last60mins = avg(Average) by Resource, MetricName

Example B: SQL Server wait types from the past 15 minutes

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where TimeGenerated >= ago(15min)
| parse _ResourceId with * "/microsoft.sql/servers/" LogicalServerName "/databases/" DatabaseName
| summarize Total_count_15mins = sum(delta_waiting_tasks_count_d) by LogicalServerName, DatabaseName, wait_type_s

Example C: SQL Server deadlocks from the past 60 minutes

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where TimeGenerated >= ago(60min)
| where MetricName in ('deadlock')
| parse _ResourceId with * "/microsoft.sql/servers/" Resource
| summarize Deadlock_max_60Mins = max(Maximum) by Resource, MetricName

Example D: Avg CPU usage from the past hour

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where TimeGenerated >= ago(60min)
| where MetricName in ('cpu_percent')
| parse _ResourceId with * "/microsoft.sql/servers/" Resource
| summarize CPU_Maximum_last60mins = max(Maximum), CPU_Minimum_last60mins = min(Minimum), CPU_Average_last60mins = avg(Average) by Resource, MetricName

Alerts

Azure Monitor alerts proactively notify you when important conditions are found in your monitoring data. These metrics in Azure Monitor are always collected. They allow you to identify and address issues in your databases or elastic pools before your customers notice them. You can set alerts on metrics, logs, and the activity log.

If you are creating or running an application in Azure, Azure Monitor Application Insights may offer additional types of alerts.

You can also configure alerts with the Azure CLI or PowerShell. For example, see Use PowerShell to monitor and scale a single database in Azure SQL Database.

The following table lists common and recommended alert rules for Azure SQL Database. You may see different options available depending on your purchase model.

Signal name Operator Aggregation type Threshold value Description
DTU Percentage Greater than Average 80 Whenever the average DTU percentage is greater than 80%
Log IO percentage Greater than Average 80 Whenever the average log io percentage is greater than 80%
Deadlocks* Greater than Count 1 Whenever the count of deadlocks is greater than 1.
CPU percentage Greater than Average 80 Whenever the average cpu percentage is greater than 80%

* Alerting on deadlocks may be unnecessary and noisy in some applications where deadlocks are expected and properly handled.

Next steps