LLI Wiki — My Digital Knowledge Base

Reducing the Azure SQL bill as a DBA

Elastic pools vs single databases, vCore vs DTU, serverless auto-pause, right-sizing from metrics, reservations and Hybrid Benefit, backups, idle databases, Log Analytics ingestion and a monthly routine (no prices).

Cutting the Azure SQL bill is not strictly a DBA's job, but the DBA is the one who knows the real workload. Here is the approach I follow: principles and checks, no price figures (prices change; the official pricing page and calculator are the reference).

1. Elastic pool or single database

A pool makes sense when many databases have low average utilization with rare peaks that happen at different times. Databases with steady medium or high load do not belong together. There is no per-database charge in a pool: you pay for the pool size, hourly. In the vCore model, a pool's vCore unit price is the same as a single database's.

The documentation's sizing estimate for a vCore pool: the larger of (number of databases x average utilization per database) and (number of concurrently peaking databases x peak per database), then compare with the cost of single databases. Also watch total storage, and density if the pool approaches the maximum number of databases.

2. vCore or DTU

DTU vCore
Principle Fixed bundle of compute + storage + I/O Compute and storage chosen separately
Serverless No Yes
Azure Hybrid Benefit No Yes (provisioned compute)
Reservation No Yes
Hyperscale No Yes

For optimization, vCore gives more levers. DTU stays simple for small, stable databases.

3. Serverless and auto-pause for dev/test

In serverless, when a database is paused, only storage is billed. What I tune:

Set-AzSqlDatabase -ResourceGroupName rg-dba-dev -ServerName sql-contoso-dev -DatabaseName AppDb `
    -MinVcore 0.5 -MaxVcore 2 -AutoPauseDelayInMinutes 60

Pitfalls: geo-replication, failover groups, long-term retention (LTR), a DNS alias and any open session prevent pausing. The first connection to a paused database returns error 40613, so applications must retry. And reservations and Azure Hybrid Benefit do not apply to serverless: it is a trade-off, not a bonus.

4. Right-sizing from metrics

Three sources, from finest to longest:

-- Run in master: lightly used databases over ~14 days
SELECT database_name,
       MAX(sku)             AS sku,
       AVG(avg_cpu_percent) AS cpu_avg,
       MAX(avg_cpu_percent) AS cpu_peak,
       MAX(storage_in_megabytes) AS storage_mb
FROM sys.resource_stats
GROUP BY database_name
HAVING MAX(avg_cpu_percent) < 10
ORDER BY cpu_peak;

Careful: inside a pool, percentages are relative to the per-database maximum limit configured in the pool, not to the pool size. Also look at sql_instance_cpu_percent next to cpu_percent, because internal load counts. I never downsize on an average: I look at the peak, including month-end processing.

5. Reservations and Azure Hybrid Benefit (principles)

6. Storage and backups

7. Idle databases

Forgotten databases cost as much as active ones. I cross the query above with connection and session metrics, then decide: delete (after an export), move to serverless with auto-pause, or move into a pool.

8. Log Analytics ingestion cost

Diagnostic settings can send a lot of data. I look at billable volume per table:

Usage
| where TimeGenerated > ago(32d)
| where StartTime >= startofday(ago(31d)) and EndTime < startofday(now())
| where IsBillable == true
| summarize BillableDataGB = sum(Quantity) / 1000. by DataType
| sort by BillableDataGB desc

Then I turn off useless categories, pick the right table plan (analytics, basic) and compare with the commitment tiers shown on the workspace's Usage and Estimated Costs page.

Monthly routine

Step What
1 Cost analysis by resource type: what moved?
2 Top databases and pools by CPU and storage: oversized?
3 Idle databases, and dev/test databases that never pause
4 Pools: databases to move in or out
5 Backups: retention, deleted databases, redundancy
6 Log Analytics ingestion volume by table
7 Reservations: coverage and utilization

Pitfalls

Lessons learned

The quickest wins are rarely in production compute: they are the forgotten databases, the dev/test that never sleeps and the logs that ingest everything. I start there, then right-size, and commit last.

Mastodon LLIMastodon SocialFosstodon