Azure SQL Performance Management

Overview

Optimizing performance of Azure SQL Database ensures that your workloads run efficiently, cost‑effectively, and with predictable latency. This tutorial walks through monitoring tools, query tuning, index design, and scaling strategies.

Monitoring Performance

Azure provides a rich set of metrics and logs. Use the following queries in sys.dm_db_resource_stats to get real‑time insights:

SELECT
    end_time,
    avg_cpu_percent,
    avg_data_io_percent,
    avg_log_write_percent,
    dtu_limit,
    dtu_used
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

For historical analysis, query sys.dm_db_resource_stats across a time window:

DECLARE @StartTime datetime2 = DATEADD(HOUR, -24, SYSDATETIME());

SELECT *
FROM sys.dm_db_resource_stats
WHERE end_time >= @StartTime
ORDER BY end_time;

Tuning Queries

Identify high‑cost queries with sys.dm_db_query_stats and sys.dm_exec_sql_text:

SELECT TOP 10
    qs.total_elapsed_time/1000 AS total_ms,
    qs.execution_count,
    qs.total_worker_time/1000 AS cpu_ms,
    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 query_text
FROM sys.dm_db_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_elapsed_time DESC;

Apply the suggested index or query rewrite based on the execution plan.

Index Strategies

Use the built‑in index recommendation service:

SELECT *
FROM sys.dm_db_missing_index_details
WHERE database_id = DB_ID();

Example of creating a covering index:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON dbo.Orders (CustomerId, OrderDate)
INCLUDE (TotalAmount);

Scaling & Elastic Pools

Adjust service tier or compute size via PowerShell:

$resourceGroup = "MyResourceGroup"
$serverName    = "myserver"
$dbName        = "mydb"
$sku = "GP_S_Gen5_2"   # General Purpose, Gen5, 2 vCores

Set-AzSqlDatabase -ResourceGroupName $resourceGroup `
    -ServerName $serverName `
    -DatabaseName $dbName `
    -Edition "GeneralPurpose" `
    -VCore $sku

Elastic pools can be managed from the portal or using Azure CLI:

az sql elastic-pool create \
  --resource-group $resourceGroup \
  --server $serverName \
  --name mypool \
  --edition GeneralPurpose \
  --capacity 50

Best Practices

Enable Query Performance Insight in the Azure portal.
Regularly review Automatic Tuning recommendations.
Use Read‑scale or Hyperscale tiers for large‑volume workloads.
Implement Connection Pooling in your application layer.
Archive old data to Azure Blob Storage or Azure Data Lake.