Optimization: Azure SQL Database sizing with elastic pools, serverless and the right service tier

#Three usage patterns

#1. Measure usage

sys.resource_stats in master keeps about 14 days of 5-minute data per database:

-- run in master of the logical server
SELECT database_name,
       MAX(avg_cpu_percent)       AS max_cpu,
       AVG(avg_cpu_percent)       AS avg_cpu,
       MAX(avg_data_io_percent)   AS max_data_io,
       MAX(avg_log_write_percent) AS max_log_write
FROM sys.resource_stats
WHERE start_time > DATEADD(day, -14, SYSUTCDATETIME())
GROUP BY database_name
ORDER BY max_cpu DESC;

For finer detail inside one database (last hour, 15-second samples): sys.dm_db_resource_stats.

Read it like this: avg < 10% and max < 40% means over-provisioned; max hitting 100% regularly means throttled (see the "Azure SQL Database is slow" post); a gap between avg and max shows how spiky the database is.

#2. Elastic pools: size by the peak of the sum

A pool needs enough capacity for the combined load at the busiest moment, not the sum of each database's peak. Build the combined curve from sys.resource_stats (CPU % × the database's vCores, summed per 5-minute slot), take the max, and add headroom (~20%).

Good pool candidates: SaaS tenant databases, departmental apps with office-hours peaks in different time zones, many small databases. Poor candidates: two big databases that peak at the same time.

az sql elastic-pool create -g rg-data -s sql-prod -n pool-apps --edition GeneralPurpose \
  --family Gen5 --capacity 8 --db-max-capacity 4 --db-min-capacity 0
az sql db update -g rg-data -s sql-prod -n appdb17 --elastic-pool pool-apps

Per-database min/max settings stop one tenant from taking the whole pool.

#3. Serverless for intermittent databases

az sql db update -g rg-data -s sql-dev -n testdb \
  --edition GeneralPurpose --compute-model Serverless --family Gen5 \
  --min-capacity 0.5 --capacity 4 --auto-pause-delay 60

• Billed per second for compute used, within min/max vCores; storage billed always.
• Auto-pause (General Purpose serverless) stops compute billing after the delay; the first connection after a pause waits for resume, so apps need connection retry logic.
• Some features keep a database from auto-pausing (e.g. geo-replication, long-term retention backups, certain sync/job features). Check the current list.

Hyperscale also offers serverless compute (with different pause behaviour). Check what your tier supports.

#4. Right-size provisioned databases

• Scale vCores to keep p95 CPU in the 40–70% range for steady workloads.
• Check memory pressure and log write before scaling down. Log rate limits are tied to tier and vCores.
• Business Critical only where you need its local SSD latency, in-memory OLTP, or built-in read replica; otherwise General Purpose.
• Scaling causes a brief disconnect at the switch; schedule it.

az sql db update -g rg-data -s sql-prod -n orders --service-objective GP_Gen5_4

#5. Other levers

#6. Review regularly

Run the sys.resource_stats query monthly; usage changes as apps change. Tag databases with owner and environment so idle ones can be challenged.

Tip

Before scaling down, look at the max over at least two weeks that include month-end or batch windows. A database idle on Tuesday can be saturated on the 1st.

#Checklist

• 14 days of resource stats per database reviewed
• Pool candidates grouped by combined peak
• Serverless for intermittent databases, apps retry on resume
• Provisioned databases right-sized, tier justified
• Hybrid Benefit / reservations reviewed
• Monthly review scheduled