Understanding Key Metrics for Azure SQL Database

Monitoring is crucial for ensuring the performance, availability, and health of your Azure SQL Database. This tutorial explores the essential metrics you should track to proactively identify and resolve issues.

Performance Metrics

These metrics help you understand how your database is performing under load and identify potential bottlenecks.

CPU Percentage

Measures the percentage of allocated CPU resources used by your database.

  • High CPU can indicate inefficient queries or undersized tiers.
  • Monitor trends over time.

Data IO Percentage

Indicates the percentage of data input/output throughput utilized.

  • High IOPS or throughput usage can be a bottleneck.
  • Correlate with query performance.

Log IO Percentage

Measures the percentage of transaction log throughput utilized.

  • Crucial for write-heavy workloads.
  • Excessive log generation might point to large transactions or inefficient indexing.

Memory Percentage

Shows the percentage of allocated memory used by the database.

  • High memory usage might affect buffer pool efficiency.
  • Consider memory-optimized tables if applicable.

DTU Percentage (for DTU model)

Represents the consumed Database Transaction Units (DTUs).

  • A single measure of CPU, memory, and IO resources.
  • Helps in choosing the right service tier.

Workers Percentage

Indicates the percentage of available worker threads being used.

  • High worker utilization can lead to request throttling.
  • Check for long-running queries or deadlocks.

Availability & Health Metrics

These metrics are vital for understanding the operational status and reliability of your database.

Storage Percentage

Shows the percentage of allocated storage space used.

  • Prevents unexpected downtime due to full storage.
  • Plan for storage growth.

Deadlocks

Counts the number of deadlocks occurring in the database.

  • Investigate queries involved in deadlocks to optimize transaction isolation and locking.
  • Use Extended Events for detailed deadlock information.

Errors

Logs critical errors encountered by the database engine.

  • Review error logs for troubleshooting.
  • Specific error codes provide clues to the root cause.

Connection & Throttling Metrics

Monitor these to ensure seamless access to your database.

Connections Total

The total number of active connections to your database.

  • High connection counts can strain database resources.
  • Ensure efficient connection pooling.

Blocked Tasks

The number of tasks currently blocked by other tasks.

  • Indicates contention and potential performance issues.
  • Identify blocking sessions and their queries.

Using Azure Monitor

Azure Monitor provides a comprehensive platform for collecting, analyzing, and acting on telemetry from your Azure and on-premises environments. You can use Azure Monitor to:

  • Visualize metrics in dashboards.
  • Set up alerts based on metric thresholds.
  • Export metrics to Log Analytics for deeper analysis and correlation with other data sources.

Viewing Metrics in the Azure Portal

  1. Navigate to your Azure SQL Database resource in the Azure portal.
  2. In the left-hand menu, under "Monitoring", select "Metrics".
  3. Choose the desired metric from the dropdown list.
  4. Configure the aggregation, splitting, and time range as needed.

Alerting on Metrics

Setting up alerts is crucial for proactive management. You can create alerts that notify you when a metric crosses a predefined threshold.

  1. In the Azure portal, navigate to your Azure SQL Database.
  2. Under "Monitoring", select "Alerts".
  3. Click "+ Create" and choose "Alert rule".
  4. Configure the target, conditions (select your metric and threshold), and actions (notifications, automation).

Example: Monitoring CPU Usage

To monitor CPU usage, you would typically select the 'CPU Percentage' metric. It's good practice to set an alert for when CPU Percentage consistently exceeds 80% for a sustained period (e.g., 15 minutes) to allow for investigation before performance is significantly impacted.

Azure Monitor:
Metric: CPU Percentage
Condition: Greater than
Threshold Value: 80
Aggregation Granularity: 5 Minutes
Frequency: Every 15 Minutes
Action Group: Notify DBA Team

Further Resources