Performance Monitoring for Azure SQL

Why Monitor?

Effective monitoring helps you identify bottlenecks, maintain SLA compliance, and optimize cost.

Detect high‑CPU or memory usage
Track query latency and throughput
Set proactive alerts before issues impact users

Key Metrics

DTU/CPU

Queries

DTU or vCore usage gives a quick view of overall resource consumption.

SELECT
    avg_cpu_percent,
    avg_data_io_percent,
    avg_log_write_percent,
    max_worker_percent,
    max_session_percent
FROM sys.resource_stats
WHERE database_name = DB_NAME();

IO metrics help you understand storage performance.

SELECT
    read_percent,
    write_percent,
    io_stall,
    io_stall_read_ms,
    io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL);

Identify long‑running queries.

SELECT TOP 10
    total_elapsed_time/1000.0 AS TotalSec,
    execution_count,
    avg_elapsed_time/1000.0 AS AvgSec,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2)+1) AS QueryText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY total_elapsed_time DESC;

Setup Monitoring in Azure Portal

Use the built‑in metrics explorer and create alerts.

Navigate to your Azure SQL database resource.
Click Metrics in the left pane.
Select DTU consumption, CPU percentage, or any other metric.
Click New alert rule to define thresholds.

Best Practices

Enable Query Store for historical query performance.
Set alerts on CPU > 80% for 5 minutes.
Periodically review index usage and missing indexes.
Use Azure Advisor recommendations.