Azure Synapse Analytics Monitoring Reference

Microsoft Docs

Monitoring Azure Synapse Analytics

This document provides a comprehensive reference for monitoring your Azure Synapse Analytics resources. Learn about the metrics, logs, and tools available to ensure the performance, availability, and security of your Synapse workspaces.

Table of Contents

Overview of Monitoring

Azure Synapse Analytics integrates deeply with Azure Monitor, providing a robust platform for collecting, analyzing, and acting on telemetry data from your Synapse resources. Understanding these capabilities is crucial for maintaining optimal performance and identifying potential issues proactively.

Key components of Synapse monitoring include:

  • Metrics: Numerical values collected at regular intervals, providing insights into performance and resource utilization.
  • Logs: Event-based records that capture detailed information about operations, errors, and resource activities.
  • Alerts: Automated notifications triggered by specific metric or log conditions.
  • Dashboards: Visualizations that consolidate key monitoring data in a single view.

Key Metrics

Azure Synapse Analytics emits a variety of metrics that help you understand the health and performance of your dedicated SQL pools, serverless SQL pools, and Spark pools. Here are some of the most commonly used metrics:

Metric Name Description Resource Type Aggregation Type
CPU Usage % Percentage of time the CPU is busy executing non-idle threads. Dedicated SQL Pool Average, Maximum
Data IO % Percentage of time the data disk subsystem is busy. Dedicated SQL Pool Average, Maximum
DWU Used Data Warehouse Unit consumption. Dedicated SQL Pool Average
Serverless SQL Pool Queries Number of queries executed by the serverless SQL pool. Serverless SQL Pool Count
Spark Application Runs Number of Spark applications run. Spark Pool Count
Active Sessions Number of active user sessions. Dedicated SQL Pool Average

You can access these metrics through the Azure portal, Azure CLI, Azure PowerShell, and the Azure Monitor REST API.

Diagnostic Logs

Diagnostic logs provide detailed insights into the operations and activities within your Synapse Analytics workspace. You can configure diagnostic settings to send these logs to various destinations, including Log Analytics workspaces, Storage Accounts, and Event Hubs.

Common log categories for Synapse Analytics include:

  • SqlPool: Logs related to activities within dedicated SQL pools.
  • SqlPoolQuery: Logs specific to query execution in dedicated SQL pools.
  • SqlPoolAudit: Audit logs for sensitive data access and modifications.
  • SparkJob: Logs for Spark jobs and applications.
  • Workspace: Logs for workspace-level operations.

Example log entry for a query execution:


{
    "time": "2023-10-27T10:30:00Z",
    "resourceId": "/subscriptions/.../resourceGroups/.../providers/Microsoft.Synapse/workspaces/.../sqlPools/MyDedicatedSqlPool",
    "operationName": "EXECUTE",
    "category": "SqlPoolQuery",
    "properties": {
        "query_id": "a1b2c3d4-e5f6-7890-1234-567890abcdef",
        "duration_ms": 1500,
        "statement": "SELECT * FROM dbo.MyTable",
        "user_name": "sqladmin",
        "database_name": "MyDatabase"
    }
}

Alerting and Notifications

Set up alerts in Azure Monitor to be notified when critical conditions are met. This helps you respond quickly to performance degradation, errors, or security incidents.

You can create alerts based on:

  • Metric thresholds (e.g., CPU Usage % exceeds 80%).
  • Log search conditions (e.g., number of errors in the last hour).
  • Activity log events.
Tip: Configure alert rules with appropriate severity levels and action groups to ensure timely and relevant notifications to your operations team.

Azure Dashboards

Create custom dashboards in the Azure portal to visualize your most important Synapse Analytics metrics and logs. This provides a centralized view of your workspace's health and performance.

You can pin charts and logs from Azure Monitor directly to your dashboards, allowing for quick analysis and monitoring.

Kusto Query Language (KQL) for Synapse Analytics

When using Log Analytics workspaces to store your Synapse diagnostic logs, you can leverage the powerful Kusto Query Language (KQL) to analyze the data. KQL allows for complex data exploration and pattern detection.

Example KQL query to find slow queries:


SynapseSqlPoolQueryLogs
| where TimeGenerated > ago(1h)
| where duration_ms > 5000
| project TimeGenerated, operation_Name, query_id, duration_ms, statement, user_name, database_name
| order by duration_ms desc

Troubleshooting Common Issues

Monitoring plays a vital role in troubleshooting common Synapse Analytics issues:

  • Performance Degradation: Analyze CPU Usage, Data IO %, and DWU Used metrics to identify bottlenecks in dedicated SQL pools. For Spark, monitor executor memory and CPU.
  • Query Failures: Examine SqlPoolQuery logs for detailed error messages and stack traces.
  • Connectivity Issues: Check workspace logs for any network-related errors.
  • Cost Management: Monitor DWU consumption and serverless query costs to optimize resource utilization.