Azure Documentation

Monitoring Azure SQL Database

Effective monitoring of your Azure SQL Database is crucial for maintaining performance, ensuring availability, and identifying potential issues before they impact your applications. This section covers the key aspects of monitoring your SQL Database instance.

Key Monitoring Areas

• Performance Metrics: Track CPU usage, memory consumption, I/O operations, and query execution times.
• Availability: Monitor the uptime of your database and identify any connectivity issues.
• Query Performance: Analyze slow-running queries, identify bottlenecks, and optimize them for better performance.
• Resource Utilization: Understand how your database is using its allocated resources to prevent throttling.
• Security Events: Monitor for suspicious activities, failed login attempts, and unauthorized access.

Tools and Services for Monitoring

Azure Monitor

Azure Monitor is the foundational service for collecting, analyzing, and acting on telemetry from your Azure and on-premises environments. For Azure SQL Database, it provides:

• Metrics: Collects time-series data about your database's performance and resource usage. Key metrics include cpu_percent, data_io_percent, log_write_percent, and dtu_consumption_percent (for DTU model).
• Logs: Collects diagnostic logs that provide detailed information about database activities, errors, and events. You can query these logs using Kusto Query Language (KQL) in Log Analytics.
• Alerts: Configure alerts based on metrics or log queries to notify you when certain thresholds are met or specific events occur.
• Dashboards: Create custom dashboards in Azure portal to visualize key metrics and logs in a single view.

Query Performance Insight (QPI)

Query Performance Insight provides an intelligent analysis of query performance in your Azure SQL Database. It helps identify the longest-running, most resource-intensive, and top-blocking queries.

• It presents data visually, highlighting top queries by CPU time, duration, and execution count.
• You can drill down into specific queries to view their execution plans.
• QPI uses dynamic management views (DMVs) to gather this data.

Intelligent Insights

Intelligent Insights is an automatic performance monitoring and detection service that identifies database performance issues and recommends corrective actions. It works by analyzing a vast amount of telemetry data and applying machine learning algorithms.

• Detects performance degradation, runaway queries, and other issues.
• Provides root-cause analysis and actionable recommendations.
• Works proactively to alert you to potential problems.

SQL Server Management Studio (SSMS) and Azure Data Studio

These desktop tools offer powerful capabilities for real-time monitoring:

• DMVs: Access and query Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) directly to get detailed, low-level information about server and database states. Some commonly used DMVs for monitoring include:
  • sys.dm_db_resource_stats: Current resource utilization.
  • sys.dm_exec_requests: Information about currently executing requests.
  • sys.dm_exec_sessions: Information about active user sessions.
  • sys.dm_os_wait_stats: Information about waits that occur on the server.
• Activity Monitor: Provides a real-time overview of processes, resource waits, and data file I/O.
• SQL Server Profiler: (Note: Profiler is older; Extended Events is the modern replacement) Captures detailed event information, such as T-SQL statements and their execution statistics.
• Extended Events: A flexible and configurable system for monitoring server health and troubleshooting problems.

-- Example: Querying resource utilization using sys.dm_db_resource_stats
SELECT
    end_time,
    avg_cpu_percent,
    avg_data_io_percent,
    avg_log_write_percent
FROM
    sys.dm_db_resource_stats
WHERE
    database_name = 'YourDatabaseName' -- Replace with your database name
ORDER BY
    end_time DESC;
                

Best Practices for Monitoring

• Set up Alerts: Configure alerts in Azure Monitor for critical metrics like CPU percentage, storage full, and connection failures.
• Regularly Review Query Performance: Use Query Performance Insight and DMVs to identify and optimize slow queries.
• Establish Baselines: Understand your typical performance metrics during normal operations to easily identify anomalies.
• Monitor Storage: Keep an eye on storage usage to avoid unexpected downtime due to a full database.
• Utilize Diagnostic Logs: Enable and collect diagnostic logs for deeper troubleshooting when issues arise.
• Stay Informed with Intelligent Insights: Pay attention to recommendations from Intelligent Insights to proactively address potential issues.