Skip to content

Latest commit

 

History

History
132 lines (86 loc) · 10.5 KB

monitoring-sql-managed-instance-azure-monitor.md

File metadata and controls

132 lines (86 loc) · 10.5 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic ms.custom monikerRange
Monitoring Azure SQL Managed Instance with Azure Monitor
Start here to learn how to monitor Azure SQL Managed Instance with Azure Monitor
WilliamDAssafMSFT
wiassaf
mathoma
08/10/2023
sql-managed-instance
performance
conceptual
subject-monitoring
= azuresql || = azuresql-mi

Monitor Azure SQL Managed Instance with Azure Monitor

[!INCLUDE sqlmi]

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 Managed Instance. Azure SQL Managed Instance 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 Managed Instance, select Metrics under the Monitoring heading to reach Azure Monitor.

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 Managed Instance and Azure SQL Database.

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 Managed Instance and SQL Server on Azure Virtual Machine.

Monitoring data

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

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

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 for databases in your Azure SQL Managed Instance include:

  • log: SQL Insights, Query Store Runtime Statistics, Query Store Wait Statistics, and Errors

Diagnostic settings available for managed instances include:

  • log: Resource Usage Statistics, Devops operations Audit Logs, and SQL Security Audit Event
  • metrics in the SQL managed instances namespace: Average CPU percentage, IO bytes read, IO bytes written, IO requests count, Storage space reserved, Storage space used, Virtual core count.
  • 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, Azure PowerShell or the Azure CLI. When you create a diagnostic setting, you specify which categories of logs to collect. The categories for Azure SQL Managed Instance are listed in Azure SQL Managed Instance monitoring data reference.

Logs are grouped into Category groups. Category groups are a collection of different logs to help you achieve different monitoring goals. These groups are defined dynamically and may change over time as new resource logs become available and are added to the category group. Note that this may incur additionally charges. The audit resource log category group allows you to select the resource logs that are necessary for auditing your resource. For more information, see Diagnostic settings in Azure Monitor: Resource logs.

Analyzing metrics

You can analyze metrics for Azure SQL Managed Instance 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 Managed Instance, see Monitoring Azure SQL Managed Instance 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 Managed Instance, see Resource Logs for Azure SQL Managed Instance.

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

Sample Kusto queries

Important

When you select Logs from the Monitoring menu of an Azure SQL Managed Instance, Log Analytics is opened with the query scope set to the current Azure SQL Managed Instance. If you want to run a query that includes data from databases or data from other Azure services, select Select scope from the query menu. See Log query scope and time range in Azure Monitor Log Analytics for details.

Note

After creating a diagnostic setting for a resource, 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 sample queries to help you monitor your Azure SQL Managed Instance:

Example A: Display all managed instances with avg_cpu utilization over 95%.

let cpu_percentage_threshold = 95;
let time_threshold = ago(1h);
AzureDiagnostics
| where Category == "ResourceUsageStats" and TimeGenerated > time_threshold
| summarize avg_cpu = max(todouble(avg_cpu_percent_s)) by _ResourceId
| where avg_cpu > cpu_percentage_threshold

Example B: Display all managed instances with storage utilization over 90%, dividing storage_space_used_mb_s by reserved_storage_mb_s.

let storage_percentage_threshold = 90;
AzureDiagnostics
| where Category =="ResourceUsageStats"
| summarize (TimeGenerated, calculated_storage_percentage) = arg_max(TimeGenerated, todouble(storage_space_used_mb_s) *100 / todouble (reserved_storage_mb_s))
   by _ResourceId
| where calculated_storage_percentage > storage_percentage_threshold

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 Azure SQL Managed Instance 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.

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

Signal name Operator Aggregation type Threshold value Description
Average CPU percentage Greater than Average 80 Whenever the average CPU utilization percentage is greater than 80%
Resource Health Current Resource Status NA Degraded or Unavailable Detect resources outages, whether they be Azure initiated or user initiated

Next steps