Azure SQL Database – Performance Tuning

Table of Contents

1. Introduction Overview

Performance tuning in Azure SQL Database is a continuous process that involves analysing workload patterns, identifying bottlenecks, and applying a set of proven techniques to improve latency, throughput, and cost efficiency.

2. Indexing Strategies Indexes

Proper indexing reduces logical reads and speeds up query execution. Use the following guidelines:

  • Prefer covering indexes for high‑frequency queries.
  • Leverage filtered indexes to limit index size.
  • Regularly monitor sys.dm_db_index_usage_stats for unused indexes.

Creating a filtered index

CREATE NONCLUSTERED INDEX IX_Orders_Status
ON dbo.Orders (OrderDate)
WHERE OrderStatus = 'Completed';

3. Query Optimisation SQL

Use the Query Performance Insight tool and EXPLAIN plans to spot expensive operators.

Example: Rewriting a subquery

-- Original
SELECT CustomerId, (SELECT COUNT(*) FROM Orders o WHERE o.CustomerId = c.Id) AS OrderCount
FROM Customers c;

-- Optimised using JOIN & GROUP BY
SELECT c.CustomerId, COUNT(o.OrderId) AS OrderCount
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id
GROUP BY c.CustomerId;

4. Resource Scaling Compute

Adjust DTU/vCore tiers based on workload spikes. Azure's auto‑scale feature can automatically provision resources.

PowerShell example

Import-Module Az.Sql
$resourceGroup = "MyRG"
$serverName   = "myserver"
$dbName       = "mydb"

# Scale to 8 vCores, 32 GB memory
Set-AzSqlDatabase -ResourceGroupName $resourceGroup `
    -ServerName $serverName -DatabaseName $dbName `
    -Edition "GeneralPurpose" -VCore 8 -ComputeGeneration "Gen5"

5. Monitoring & Diagnostics Metrics

Integrate Azure Monitor, Query Store, and Extended Events for real‑time insights.

Enable Query Store

ALTER DATABASE [mydb] SET QUERY_STORE = ON;
ALTER DATABASE [mydb] SET QUERY_STORE (OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 500,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

6. Best Practices Checklist

  1. Use appropriate service tier (vCore vs DTU).
  2. Apply index maintenance windows.
  3. Regularly review Query Store reports.
  4. Implement connection pooling.
  5. Leverage elastic pools for multi‑tenant scenarios.

7. Sample Code Demo

The following C# snippet demonstrates async execution with retry policy for transient errors.

using System;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

public async Task<int> GetOrderCountAsync(int customerId)
{
    var connectionString = Environment.GetEnvironmentVariable("SQL_CONNECTION");
    using var conn = new SqlConnection(connectionString);
    await conn.OpenAsync();

    var cmd = new SqlCommand(
        @"SELECT COUNT(*) FROM Orders WHERE CustomerId = @cid;", conn);
    cmd.Parameters.Add("@cid", System.Data.SqlDbType.Int).Value = customerId;

    // Retry on transient failures
    var retry = new Microsoft.Data.SqlClient.SqlRetryLogicBaseProvider()
        .CreateRetryLogic(2, TimeSpan.FromSeconds(2));

    return await retry.ExecuteAsync(async () =>
        (int)await cmd.ExecuteScalarAsync());
}