Azure Documentation

SQL VM Performance Monitoring

This document provides guidance on monitoring the performance of SQL Server on Azure Virtual Machines (SQL VMs). Effective monitoring is crucial for ensuring optimal performance, identifying bottlenecks, and proactively addressing potential issues.

Key Performance Metrics to Monitor

Focus on the following categories and specific metrics:

• CPU Utilization:
  • % Processor Time (Overall CPU usage)
  • % Privileged Time (Time spent by CPU in kernel mode)
  • % User Time (Time spent by CPU in user mode)
• Memory Usage:
  • Available MBytes (Free physical memory)
  • Pages/sec (Rate of page reads/writes)
  • SQL Server: Buffer Manager\Page life expectancy (A key indicator of buffer cache efficiency)
• Disk I/O:
  • % Disk Time (Disk queue length and busy time)
  • Avg. Disk sec/Read and Avg. Disk sec/Write (Read/Write latency)
  • Disk Reads/sec and Disk Writes/sec (Throughput)
• SQL Server Specific Metrics:
  • SQLServer:SQL Statistics\Batch Requests/sec (Query throughput)
  • SQLServer:SQL Statistics\SQL Compilations/sec and SQLServer:SQL Statistics\SQL Re-Compilations/sec (Indicates query plan efficiency)
  • SQLServer:Wait Statistics\CPU idle, SQLServer:Wait Statistics\PAGEIOLATCH_SH, SQLServer:Wait Statistics\WRITELOG (Identifies common wait types impacting performance)

Tools for Monitoring

Azure provides several integrated tools and services for monitoring your SQL VMs:

• Azure Monitor:

  This is the foundational monitoring service for Azure. It collects and analyzes telemetry from your Azure resources.

  • Metrics: Provides real-time and historical performance data. You can set up alerts based on metric thresholds.
  • Logs: Collects diagnostic logs from your SQL VMs, which can be queried using Kusto Query Language (KQL) for in-depth analysis.
  • Application Insights: While primarily for applications, it can be integrated to monitor SQL database performance from an application perspective.
• SQL Server Performance Dashboard (built into SQL Server Management Studio - SSMS):

  SSMS offers built-in dashboards that provide quick insights into key SQL Server performance counters and wait statistics.

• Performance Monitor (PerfMon) on the VM:

  The traditional Windows tool for collecting performance counter data directly on the VM.

• Azure Advisor:

  Provides recommendations for optimizing performance, security, and cost of your Azure resources, including SQL VMs.

Setting up Monitoring and Alerts

Leverage Azure Monitor to set up alerts for critical performance metrics:

1. Navigate to your SQL VM resource in the Azure portal.
2. Under "Monitoring," select "Alerts."
3. Click "Create alert rule."
4. Configure the target, condition (e.g., CPU percentage greater than 90% for 15 minutes), and actions (e.g., send an email, trigger a webhook).

Best Practices for SQL VM Performance Monitoring

• Establish Baselines: Understand what "normal" looks like for your SQL VM workloads.
• Monitor Proactively: Don't wait for users to report performance issues. Set up alerts for key metrics.
• Analyze Wait Statistics: These are critical for understanding why SQL Server is performing slowly. Look for high-impact wait types.
• Optimize Queries: Poorly written queries are a common cause of performance problems. Use query performance insights and execution plans.
• Tune VM Configuration: Ensure your VM size and disk configuration are appropriate for your workload.
• Regularly Review Logs: Use Azure Log Analytics to correlate performance issues with other events.
• Use Resource Health: Monitor Azure Resource Health for platform-level issues affecting your SQL VM.

Example: Monitoring CPU Usage with Azure Monitor Metrics

To monitor CPU utilization:

1. In the Azure portal, go to your SQL VM resource.
2. Select "Metrics" under the "Monitoring" section.
3. Click "Add metric."
4. Select "Virtual Machine Host" as the resource type and "Percentage Processor Time" as the metric.
5. You can add other related CPU metrics and choose an appropriate aggregation (e.g., Average) and time range.

By consistently monitoring these aspects, you can ensure your SQL Server instances on Azure VMs are performing at their best.