azure-sql-optimization

What this skill does

# Azure SQL Database Optimization

Platform-specific optimization for Azure SQL Database.

## Quick Reference

### Service Tier Comparison

| Tier | Best For | Max Size | Key Features |
|------|----------|----------|--------------|
| Basic | Dev/test, light workloads | 2 GB | Low cost |
| Standard | General workloads | 1 TB | S0-S12 DTUs |
| Premium | High I/O, low latency | 4 TB | P1-P15 DTUs |
| General Purpose (vCore) | Most workloads | 16 TB | Serverless option |
| Business Critical | High availability | 4 TB | In-memory, read replicas |
| Hyperscale | Large databases | 100 TB | Auto-scaling storage |

### DTU vs vCore

| Aspect | DTU | vCore |
|--------|-----|-------|
| Pricing | Bundled resources | Separate compute/storage |
| Control | Limited | Fine-grained |
| Reserved capacity | No | Yes (up to 72% savings) |
| Serverless | No | Yes (General Purpose) |
| Best for | Simple workloads | Predictable, migrated workloads |

## Performance Monitoring

### Resource Consumption
```sql
-- Last 15 minutes (avg 15-second intervals)
SELECT
    end_time,
    avg_cpu_percent,
    avg_data_io_percent,
    avg_log_write_percent,
    avg_memory_usage_percent,
    max_worker_percent,
    max_session_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

-- Historical (last 14 days, hourly)
SELECT
    start_time,
    end_time,
    avg_cpu_percent,
    avg_data_io_percent,
    avg_log_write_percent
FROM sys.resource_stats
WHERE database_name = DB_NAME()
ORDER BY start_time DESC;
```

### Query Performance Insight
```sql
-- Top CPU consumers last hour
SELECT TOP 20
    qt.query_sql_text,
    rs.avg_cpu_time / 1000 AS avg_cpu_ms,
    rs.count_executions,
    rs.avg_cpu_time * rs.count_executions / 1000 AS total_cpu_ms
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -1, GETUTCDATE())
ORDER BY rs.avg_cpu_time * rs.count_executions DESC;
```

## Automatic Tuning

### Enable Automatic Tuning
```sql
-- Enable all auto-tuning options
ALTER DATABASE current
SET AUTOMATIC_TUNING (
    FORCE_LAST_GOOD_PLAN = ON,
    CREATE_INDEX = ON,
    DROP_INDEX = ON
);

-- Check current settings
SELECT * FROM sys.database_automatic_tuning_options;
```

### View Tuning Recommendations
```sql
-- Current recommendations
SELECT
    name,
    reason,
    score,
    state_desc,
    is_revertable_action,
    is_executable_action,
    details
FROM sys.dm_db_tuning_recommendations;
```

### Apply Recommendations
```sql
-- Force a specific query plan
EXEC sp_query_store_force_plan @query_id = 12345, @plan_id = 67890;

-- Unforce plan
EXEC sp_query_store_unforce_plan @query_id = 12345, @plan_id = 67890;
```

## Hyperscale Features

### Storage Auto-Scaling
- Automatically grows up to 128 TB
- No need to pre-provision storage
- Pay only for storage used

### Read Scale-Out
```sql
-- Connection string option
ApplicationIntent=ReadOnly

-- In application code
"Server=myserver.database.windows.net;Database=mydb;ApplicationIntent=ReadOnly;..."
```

### Named Replicas
```sql
-- Create named replica
ALTER DATABASE MyDatabase
ADD SECONDARY ON SERVER MySecondaryServer
WITH (SERVICE_OBJECTIVE = 'HS_Gen5_2', SECONDARY_TYPE = Named, NAME = N'MyReadReplica');
```

## Serverless Configuration

### Configure Auto-Pause
```sql
-- Via Azure Portal, CLI, or PowerShell
-- Set auto-pause delay (minutes), min/max vCores

-- Check current usage
SELECT
    cpu_percent,
    auto_pause_delay_in_minutes_configured
FROM sys.dm_db_resource_stats_serverless;
```

### Serverless Best Practices
1. **Use for intermittent workloads** - Saves cost during idle periods
2. **Set appropriate min vCores** - Prevents cold starts for time-sensitive apps
3. **Monitor auto-pause** - Auto-resume adds latency
4. **Consider always-on for consistent workloads** - Provisioned may be cheaper

## Connection Optimization

### Connection Pooling
```csharp
// .NET connection string
"Server=tcp:myserver.database.windows.net,1433;Database=mydb;
 Min Pool Size=10;Max Pool Size=100;Connection Timeout=30;"
```

### Retry Logic
```csharp
// Azure SQL requires retry logic for transient faults
var options = new SqlRetryLogicOption()
{
    NumberOfTries = 5,
    DeltaTime = TimeSpan.FromSeconds(1),
    MaxTimeInterval = TimeSpan.FromSeconds(30)
};
```

### Connection Best Practices
1. **Use connection pooling** - Reduce connection overhead
2. **Implement retry logic** - Handle transient faults (error 40613, 40197)
3. **Use redirect connection mode** - Better performance after initial connection
4. **Close connections promptly** - Don't hold connections unnecessarily

## Azure-Specific Limitations

### Not Supported
- SQL Agent (use Azure Functions, Logic Apps)
- BULK INSERT from files (use Blob Storage)
- Linked servers (use Elastic Query)
- FILESTREAM
- Cross-database queries in same server (use Elastic Query)

### Workarounds

#### Bulk Insert from Blob Storage
```sql
-- Create credential
CREATE DATABASE SCOPED CREDENTIAL BlobCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'your_sas_token';

-- Create external data source
CREATE EXTERNAL DATA SOURCE BlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://youraccount.blob.core.windows.net/container',
    CREDENTIAL = BlobCredential
);

-- Bulk insert
BULK INSERT MyTable
FROM 'data.csv'
WITH (DATA_SOURCE = 'BlobStorage', FORMAT = 'CSV', FIRSTROW = 2);
```

#### Elastic Query for Cross-Database
```sql
-- On target database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL ElasticCredential
WITH IDENTITY = 'username', SECRET = 'password';

CREATE EXTERNAL DATA SOURCE RemoteDB
WITH (
    TYPE = RDBMS,
    LOCATION = 'remote-server.database.windows.net',
    DATABASE_NAME = 'RemoteDatabase',
    CREDENTIAL = ElasticCredential
);

CREATE EXTERNAL TABLE dbo.RemoteTable (...)
WITH (DATA_SOURCE = RemoteDB);
```

## Cost Optimization

### Reserved Capacity
- Up to 72% savings vs pay-as-you-go
- 1-year or 3-year terms
- Exchange/refund flexibility

### Right-Sizing
```sql
-- Check if over-provisioned
SELECT
    AVG(avg_cpu_percent) AS avg_cpu,
    MAX(avg_cpu_percent) AS max_cpu,
    AVG(avg_data_io_percent) AS avg_io,
    MAX(avg_data_io_percent) AS max_io
FROM sys.dm_db_resource_stats
WHERE end_time >= DATEADD(day, -7, GETUTCDATE());

-- If avg < 40% consistently, consider downsizing
```

### Hyperscale Cost Considerations
- Compute: Per-second billing
- Storage: Per-hour billing for used space
- Read replicas: Additional compute cost
- Memory not automatically released (monitor and scale appropriately)