CloudWatch Database Insights: Find What Slows SQL Server

Sign in and start the SQL lab:

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

1Turn on Database Insights and add data

Paste this whole block:

aws rds modify-db-instance --db-instance-identifier saat-sql --enable-performance-insights --performance-insights-retention-period 7 --database-insights-mode standard --apply-immediately --query "DBInstance.DBInstanceStatus" --output text
Start-Sleep 30
aws rds wait db-instance-available --db-instance-identifier saat-sql
$rid = aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].DbiResourceId" --output text
aws rds describe-db-instances --db-instance-identifier saat-sql --query "DBInstances[0].[PerformanceInsightsEnabled,DatabaseInsightsMode,PerformanceInsightsRetentionPeriod]" --output text
sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -d SaatShop -Q "SET NOCOUNT ON; INSERT dbo.Orders (Customer, Amount) SELECT TOP (100000) CONCAT(N'Customer ', ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), CAST(ABS(CHECKSUM(NEWID())) % 500000 / 100.0 AS decimal(10,2)) FROM sys.all_objects a CROSS JOIN sys.all_objects b; SELECT COUNT(*) AS orders FROM dbo.Orders;"
$rid

You should see something like this:

modifying
True    standard        7
orders
-----------
     100003
db-ABCDEFGHIJKLMNOPQRSTUVWXY1

Database Insights is on in Standard mode, with no restart. Note the db-… value: that’s the DbiResourceId the API needs, and it never changes even if you rename the instance.

2Put CPU load on saat-sql

Paste this whole block. Two background jobs run an expensive query again and again for 10 minutes:

$load = {
  param($pw)
  $env:SQLCMDPASSWORD = $pw
  $end = (Get-Date).AddMinutes(10)
  while ((Get-Date) -lt $end) { sqlcmd -S "127.0.0.1,11433" -U saatadmin -C -d SaatShop -Q "SELECT COUNT_BIG(*) FROM dbo.Orders a CROSS JOIN (SELECT TOP (300) OrderId FROM dbo.Orders) b WHERE a.Amount > b.OrderId" | Out-Null }
}
1..2 | ForEach-Object { Start-Job -ScriptBlock $load -ArgumentList $env:SQLCMDPASSWORD } | Out-Null
Get-Job | Select-Object Id, State

You should see something like this:

Id State
-- -----
 1 Running
 2 Running

Open CloudWatch > Insights > Database Insights, choose saat-sql, and watch DB load climb over the next few minutes. The dashed line is the vCPU count (2).

3Read top waits and top SQL from the CLI

Wait about 8 minutes, then paste this whole block:

function Get-TopWaits([int]$Minutes = 15) {
  $e = (Get-Date).ToUniversalTime(); $s = $e.AddMinutes(-$Minutes).ToString('o'); $e = $e.ToString('o')
  '[{"Metric":"db.load.avg","GroupBy":{"Group":"db.wait_event","Limit":5}}]' | Set-Content pi-query.json
  $r = aws pi get-resource-metrics --service-type RDS --identifier $rid --metric-queries file://pi-query.json --start-time $s --end-time $e --period-in-seconds 60 | ConvertFrom-Json
  $r.MetricList | Where-Object { $_.Key.Dimensions } | ForEach-Object { '{0,-25} {1:N2}' -f $_.Key.Dimensions.'db.wait_event.name', (($_.DataPoints.Value | Where-Object { $null -ne $_ } | Measure-Object -Average).Average) }
}
function Get-TopSql([int]$Minutes = 15) {
  $e = (Get-Date).ToUniversalTime(); $s = $e.AddMinutes(-$Minutes).ToString('o'); $e = $e.ToString('o')
  $k = aws pi describe-dimension-keys --service-type RDS --identifier $rid --start-time $s --end-time $e --metric db.load.avg --group-by Group=db.sql_tokenized,Limit=3 | ConvertFrom-Json
  $k.Keys | ForEach-Object { $t = $_.Dimensions.'db.sql_tokenized.statement'; '{0:N2}  {1}' -f $_.Total, $t.Substring(0, [Math]::Min(70, $t.Length)) }
}
Get-TopWaits
Get-TopSql

You should see something like this:

CPU                       1.64
SOS_SCHEDULER_YIELD       0.21
1.79  SELECT COUNT_BIG(*) FROM dbo.Orders a CROSS JOIN (SELECT TOP (?) Orde

Load is almost all CPU, close to the 2 vCPU line, and one statement causes nearly all of it. SOS_SCHEDULER_YIELD shows queries taking turns on busy CPUs. In real life your next step is that query’s plan (Query Store, or Advanced mode’s execution plans) and an index or a rewrite.

4Alarm when load passes the vCPU count

Paste this whole block:

$topic = aws sns list-topics --query "Topics[?ends_with(TopicArn, ':saat-alerts')].TopicArn | [0]" --output text
aws cloudwatch put-metric-alarm --alarm-name saat-sql-dbload-high --namespace AWS/RDS --metric-name DBLoad --dimensions Name=DBInstanceIdentifier,Value=saat-sql --statistic Average --period 300 --evaluation-periods 1 --threshold 2 --comparison-operator GreaterThanThreshold --treat-missing-data notBreaching --alarm-actions $topic
Start-Sleep 300
aws cloudwatch describe-alarms --alarm-names saat-sql-dbload-high --query "MetricAlarms[0].[StateValue,StateReason]" --output text

You should see something like this:

ALARM   Threshold Crossed: 1 datapoint [2.06 (20/10/26 16:25:00)] was greater than the threshold (2.0).

You get an email from saat-alerts, as in Lesson 23. A DBLoad alarm catches slowdowns that a CPU alarm misses, such as blocking, where CPU stays low but sessions pile up.

✅ Checkpoint: Database Insights is on in Standard mode, Get-TopWaits shows mostly CPU, Get-TopSql shows the cross-join query, and saat-sql-dbload-high went to ALARM.