Storage for SQL Server: gp3 vs io2, tempdb

Sign in and start the SQL lab (saat-sql starts if it’s stopped; allow 5 to 10 minutes):

cd C:\aws-lab
aws login --profile saat-admin
$env:AWS_PROFILE = 'saat-admin'
.\Start-SqlLab.ps1

You should see something like this:

Tunnel ready on 127.0.0.1,11433 for saatadmin (password is in SQLCMDPASSWORD for this window)

1Read the storage and instance limits

Paste this whole block:

aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].[DBInstanceClass,StorageType,AllocatedStorage,Iops,StorageThroughput,MaxAllocatedStorage]" --output text
aws ec2 describe-instance-types --instance-types t3.micro --query "InstanceTypes[0].EbsInfo.EbsOptimizedInfo.[BaselineBandwidthInMbps,MaximumBandwidthInMbps,BaselineIops,MaximumIops]" --output text

You should see something like this:

db.t3.micro     gp3     20      3000    125     None
87      2085    500     11800

The volume offers 3,000 IOPS and 125 MiB/s. The instance underneath (db.t3.micro uses the same hardware as t3.micro) has a baseline of only 500 IOPS and 87 Mbps, bursting to 11,800 IOPS and 2,085 Mbps while credits last. MaxAllocatedStorage None means autoscaling is off.

2Push real I/O through SQL Server

Paste this whole block. It writes about 200 MB of rows (one to two minutes on this small instance):

@'
SET NOCOUNT ON;
IF OBJECT_ID('dbo.Big') IS NULL CREATE TABLE dbo.Big (Id int IDENTITY PRIMARY KEY, Pad char(4000) NOT NULL DEFAULT 'x');
INSERT dbo.Big (Pad) SELECT TOP (50000) 'x' FROM sys.all_objects a CROSS JOIN sys.all_objects b;
CHECKPOINT;
SELECT COUNT(*) AS big_rows, CAST(SUM(DATALENGTH(Pad)) / 1048576.0 AS decimal(10,1)) AS approx_mb FROM dbo.Big;
'@ | Set-Content io-work.sql
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -d SaatShop -i io-work.sql -W

You should see something like this:

big_rows approx_mb
50000    190.7

3Measure latency inside SQL Server and in CloudWatch

Paste this whole block. Wait a minute or two first so CloudWatch has the datapoints:

@'
SET NOCOUNT ON;
SELECT DB_NAME(vfs.database_id) AS db, mf.type_desc, vfs.num_of_reads, vfs.num_of_writes,
  CAST(vfs.io_stall_read_ms * 1.0 / NULLIF(vfs.num_of_reads, 0) AS decimal(10,1)) AS avg_read_ms,
  CAST(vfs.io_stall_write_ms * 1.0 / NULLIF(vfs.num_of_writes, 0) AS decimal(10,1)) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
WHERE vfs.database_id IN (DB_ID('SaatShop'), 2) AND mf.type_desc IN ('ROWS', 'LOG')
ORDER BY db, mf.type_desc;
'@ | Set-Content io-stats.sql
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -i io-stats.sql -W
function Get-RdsMetric([string]$Name, [string]$Stat = 'Average', [int]$Minutes = 15) {
  $end = (Get-Date).ToUniversalTime(); $start = $end.AddMinutes(-$Minutes)
  $vals = aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name $Name --dimensions Name=DBInstanceIdentifier,Value=saat-sql --start-time $start.ToString('o') --end-time $end.ToString('o') --period 60 --statistics $Stat --query "Datapoints[].$Stat" --output text
  if ($Stat -eq 'Minimum') { ($vals -split '\s+' | ForEach-Object { [double]$_ } | Measure-Object -Minimum).Minimum } else { ($vals -split '\s+' | ForEach-Object { [double]$_ } | Measure-Object -Maximum).Maximum }
}
"WriteIOPS max:       $(Get-RdsMetric WriteIOPS Maximum)"
"WriteLatency max s:  $(Get-RdsMetric WriteLatency Maximum)"
"EBSIOBalance% min:   $(Get-RdsMetric EBSIOBalance% Minimum)"

You should see something like this:

db       type_desc num_of_reads num_of_writes avg_read_ms avg_write_ms
SaatShop LOG       12           51840         0.8         0.6
SaatShop ROWS      3311         26402         2.1         1.4
tempdb   LOG       9            61            0.7         0.9
tempdb   ROWS      40           88            0.9         1.2
WriteIOPS max:       2950.4
WriteLatency max s:  0.0021
EBSIOBalance% min:   71

Latency is fine because the instance was bursting: a few minutes of load used almost a third of its I/O burst balance. A real daytime workload on db.t3.micro would drain it, and then the same writes would queue. That’s the pattern behind many “it was fast this morning” tickets.

4Find tempdb and turn on autoscaling

Paste this whole block:

sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -d tempdb -W -Q "SET NOCOUNT ON; SELECT name, physical_name, size * 8 / 1024 AS size_mb FROM sys.database_files"
aws rds modify-db-instance --db-instance-identifier saat-sql --max-allocated-storage 100 --apply-immediately --query "DBInstance.PendingModifiedValues" --output json | Out-Null
Start-Sleep 30
aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].MaxAllocatedStorage" --output text

You should see something like this:

name     physical_name                  size_mb
tempdev  D:\rdsdbdata\DATA\tempdb.mdf     8
templog  D:\rdsdbdata\DATA\templog.ldf    8
temp2    D:\rdsdbdata\DATA\tempdb2.ndf    8
100

tempdb shares the D: data volume here. On a class with instance store, the same query shows tempdb on a separate fast local drive. Autoscaling now grows saat-sql up to 100 GiB if it runs low on space, instead of the database stopping with “disk full”.

✅ Checkpoint: You read the gp3 volume and the much lower instance baseline, generated about 200 MB of writes, measured per-file latency and EBSIOBalance%, found tempdb on D:, and set MaxAllocatedStorage to 100.