Home / Databases / SQL Server

DatabasesHow-toAdvanced

Sizing SQL Server for the cloud: capture a performance baseline and map it to vCores and instance types

Size Azure SQL, RDS and cloud VMs from real data rather than server specs. Covers which counters to collect and for how long, how to read CPU, memory, IOPS, throughput and log rate, and how to map them to service tiers and instance families.

Deepak Bhardwaj23 Oct 20263 min read

Applies toSQL Server 2012+; targets Azure SQL Database / MI, Azure VMs, Amazon RDS for SQL Server, EC2

1. Collect for at least 2 weeks (include month-end)

Metric	Source
CPU % (SQL process and total), core count	Perfmon `Processor(_Total)\% Processor Time`, `Process(sqlservr)\% Processor Time`
Memory used, Page life expectancy	`SQLServer:Memory Manager\Total Server Memory`, `Buffer Manager\Page life expectancy`
Data IOPS and MB/s	`LogicalDisk(*)\Disk Reads/sec`, `Disk Writes/sec`, `Disk Read Bytes/sec`
IO latency	`LogicalDisk(*)\Avg. Disk sec/Read`, `sec/Write`
Log write rate	`SQLServer:Databases(_Total)\Log Bytes Flushed/sec`
tempdb usage	file size and growth events
Batch requests/sec	`SQLServer:SQL Statistics\Batch Requests/sec`

$counters = '\Processor(_Total)\% Processor Time','\Process(sqlservr)\% Processor Time',
  '\SQLServer:Memory Manager\Total Server Memory (KB)','\SQLServer:Buffer Manager\Page life expectancy',
  '\LogicalDisk(*)\Disk Reads/sec','\LogicalDisk(*)\Disk Writes/sec',
  '\LogicalDisk(*)\Disk Read Bytes/sec','\LogicalDisk(*)\Disk Write Bytes/sec',
  '\LogicalDisk(*)\Avg. Disk sec/Read','\LogicalDisk(*)\Avg. Disk sec/Write',
  '\SQLServer:Databases(_Total)\Log Bytes Flushed/sec','\SQLServer:SQL Statistics\Batch Requests/sec'
Get-Counter -Counter $counters -SampleInterval 30 -MaxSamples 40320 | Export-Counter -Path D:\perf\sql01.blg

(Named instances use MSSQL$NAME: instead of SQLServer:.) Azure Migrate and AWS Migration Evaluator collect similar data for you.

2. Use percentiles, not averages

Size CPU to the 95th percentile of busy hours, and storage to the peak IOPS and throughput that matter (batch windows). Averages hide the month-end load that breaks the migrated system.

3. Map to Azure SQL

Need	Choice
CPU	vCores ≈ p95 cores used × 1.2 headroom (modern cores are often faster, so validate)
Low IO latency, high log rate, readable secondary	Business Critical (local SSD)
Typical OLTP	General Purpose (next-gen GP on MI lets you set IOPS separately)
Very large DB (>4 TB) or fast scale	Hyperscale (Azure SQL Database; up to 128 TB)

Check the per-tier log rate limits and IOPS limits: the log rate cap is the most common surprise for heavy ETL.

4. Map to AWS

Need	Choice
Memory-heavy OLTP	`r6i`/`r7i`, or `x2iedn` (high memory per vCPU, good for licensing with Optimize CPUs)
General	`m6i`/`m7i`
Storage	gp3 (set IOPS and throughput independently) or io2 Block Express for high, consistent IOPS
RDS	Same instance families with the `db.` prefix; Multi-AZ doubles the instance cost

5. Validate before cutover

Restore a copy to the target and replay a real workload (Distributed Replay is deprecated; use tools such as Query Store export, WorkloadTools or app load tests).
Compare p95 query time, batch duration, and waits (sys.dm_os_wait_stats).

Tip
Start a size smaller on PaaS if the data supports it: scaling up Azure SQL or RDS takes minutes, and you stop paying for the "just in case" cores that made the on-premises server so expensive to license.