Skip to Content
Cloud Engineering Academy
17. Daily-Work Playbooks & Career

Playbook: slow SQL after migration to RDS

Find out why a query that was fast on-premises is slow on Amazon RDS by separating network round trips, plan and statistics changes, storage limits, parameter differences and connection-pool behaviour, using database insights, wait statistics and Query Store.

advanced⏱ 1 h 25 min🟢 FreeSOA-C03DVA-C03DEA-C01SAA-C03

Assumes: .NET 10 LTS · EF Core 10 · Amazon RDS for SQL Server and PostgreSQL · AWS CLI v2 · Region us-east-1. Verified against official sources on 2026-11-18.

Learning objectives

By the end of this playbook you can:

  • Split "the query is slow" into network round trips, plan quality, storage, CPU and memory, locking and connection handling.
  • Use RDS monitoring tools (Performance Insights or its successor, CloudWatch Database Insights; naming is changing, see the note below) to find the top wait and the top SQL.
  • Read SQL Server wait statistics, Query Store and plan differences, and the PostgreSQL equivalents.
  • Recognise migration-specific causes: stale statistics, compatibility level and cardinality estimator changes, missing indexes, implicit conversions, and a new network distance between application and database.
  • Detect and fix N+1 query patterns in EF Core, and explain why they hurt more after a migration.
  • Run a C# demo that counts database round trips and shows how latency multiplies them.

Prerequisites

Why this matters in banking

Harbourline migrated its statements database from an on-premises SQL Server to Amazon RDS for SQL Server over a weekend. On Monday the "monthly statement" screen goes from 300 ms to 6 seconds, and the 08:00 reconciliation job, which finished in 40 minutes before, is still running at 11:00. Nothing was "broken": the migration test passed and the data matches. The post-migration review also reveals that the application servers moved to ECS in a different Availability Zone from the database.

Slow queries after a migration are rarely one problem. They are typically three small ones adding up: a little extra latency per round trip, a plan that changed, and a storage setting that is lower than on the old hardware. The method below finds each one, in order of how cheaply it can be checked.

Tool naming is changing (⚠ VERIFY)

AWS has been moving the Performance Insights console experience to CloudWatch Database Insights. Sources checked on 2026-11-18 describe the Performance Insights console being retired in mid-2026 and redirecting to Database Insights, with the Performance Insights API parameters kept for automation. The exact date and which features (for example execution plans and on-demand analysis) need Database Insights Advanced mode may vary, so check the RDS console for your instance and the current documentation. The concepts here (database load, wait events, top SQL) are the same in both.

The idea in plain language

A database query's total time is the sum of five things:

  1. Network: getting the request to the database and the answer back. Each round trip costs latency.
  2. Parse and plan: choosing how to run the query. A bad plan can be 1,000 times slower.
  3. Execution: CPU work and reading data. If data is not in memory it must come from storage.
  4. Waiting: for locks, for storage, for memory grants, for a parallel worker.
  5. Transfer: sending the result rows back.

Performance tools express this as database load (average active sessions) broken down by wait event. If load is mostly CPU, you have a query or CPU problem. If it is mostly I/O waits, storage or missing indexes. If it is lock waits, blocking. If the database looks idle but the app is slow, the time is in the network or the app (often N+1 calls).

Analogy: A clerk fetches files from an archive. It is slow because the archive moved to another building (network), the clerk now uses a different filing system and walks the long way (plan), the archive has a narrower door (storage throughput), two clerks want the same drawer (locks), or the clerk makes 200 trips for 200 files instead of one trip for all (N+1). Timing the trip tells you which.

What problem the AWS services solve

RDS gives you managed monitoring with the same engine you already know, plus the things you cannot do on a managed instance:

  • Database load and top SQL (Performance Insights or Database Insights): which SQL and which wait dominate.
  • Enhanced Monitoring: operating-system level metrics (CPU per process, memory, disk) at a fine interval, because you have no OS login on RDS.
  • CloudWatch metrics: CPUUtilization, FreeableMemory, ReadLatency, WriteLatency, ReadIOPS, WriteIOPS, DiskQueueDepth, DatabaseConnections, and for burstable storage or instance classes, burst balance metrics.
  • Parameter groups: engine settings under version control, applied to the instance, because you cannot edit sp_configure-style settings by hand for all options.
  • Read replicas to offload reporting, with the usual replication-lag caveats.

What changes against on-premises: no OS access, no filesystem-level tuning, settings are limited to what the parameter group exposes, storage performance is a purchased property (type, size, provisioned IOPS and throughput), and Multi-AZ adds synchronous replication to every commit.

Microsoft / Azure comparison

You know (Microsoft/Azure)AWS equivalentWhat's different
SQL Server on a physical or VM host with local SSDRDS for SQL Server on EBS storageStorage is network-attached. IOPS and throughput are limited by the volume type, its size and the instance class
Azure SQL Query Performance InsightPerformance Insights / CloudWatch Database InsightsLoad shown as average active sessions by wait event and SQL
Query StoreQuery Store (supported on RDS SQL Server, enable per database)Same feature. Use it to compare plans before and after migration
sp_configure and trace flagsRDS parameter groups, plus rdsadmin procedures for some optionsNot every server setting is exposed, check what your engine version allows
Azure SQL Database elastic pool / DTUsRDS instance class + storage type + provisioned IOPSYou size compute and storage separately
Azure SQL zone redundancyRDS Multi-AZSynchronous standby: commits wait for it, which adds latency to writes
Linked server to on-prem from a VMThe database may still be called across a VPN or Direct ConnectLatency over hybrid links multiplies chatty queries
EF Core logging and Application Insights SQL dependency trackingEF Core logging, X-Ray / OpenTelemetry spansYou must enable the instrumentation yourself

Architecture

Where the time goes

The investigation order

Step-by-step implementation

Minutes 0 to 10: scope and baseline

  1. Which queries, which time? One screen or all? Since when? Compare with a baseline: query durations from before the migration (Query Store export, APM data, slow-query logs).
  2. Check what else changed in the same release: the app moved hosts, the .NET version changed, connection strings (pool size, MultiSubnetFailover, TLS), isolation level, a driver update.
  3. Stop the bleeding if needed: scaling the instance class up is a quick, reversible mitigation for CPU-bound slowness (with a short outage in single-AZ, or a failover in Multi-AZ), but it hides the cause. Note it as a mitigation, not a fix.

Minutes 10 to 25: the quick dashboard check

Open database load and CloudWatch metrics for the slow period:

ObservationMeaning
Load below vCPU count, top wait is nothing special, but app is slowTime is outside the database: round trips, app threads, network
Load above vCPU count with CPU as the main componentCPU-bound: plans, missing indexes, too many parallel queries
High ReadLatency or DiskQueueDepth, I/O waits on topStorage-bound
High WriteLatency with commit/log waitsLog writes or Multi-AZ sync commit
FreeableMemory collapsing and many readsBuffer cache too small for the working set
DatabaseConnections at a plateau equal to the pool maxPool exhaustion: queries queue in the app

Minutes 25 to 50: find the top SQL and read its plan

Use the Top SQL view, or the DMVs below, to identify the statements with the highest total time, not just the slowest single run. A 30 ms query run 40,000 times per minute outweighs a 5 second query run once.

For each top statement, compare its plan to the pre-migration plan (Query Store keeps plan history if it was on). Look for: table scans where there was a seek, a different join type, an enormous memory grant, parallelism where there was none, CONVERT_IMPLICIT warnings.

Minutes 50 to 70: check the usual migration suspects

  • Statistics. After a restore or migration, statistics may be stale or missing. Run UPDATE STATISTICS for the affected tables (a full scan on large tables takes time and I/O: do it in a quiet period).
  • Compatibility level and cardinality estimator. If the target compatibility level is higher than the source, the newer estimator can make different choices. Test with the legacy estimator as a diagnosis, not as a permanent answer.
  • Indexes. Were all indexes created on the target? Schema conversion tools sometimes skip or change them. Compare the index lists.
  • Data types and collation. varchar versus nvarchar parameters cause implicit conversions that defeat indexes. A changed collation does the same for string comparisons.
  • Parameter sniffing. A plan compiled for one parameter value reused for another. Query Store forced plans or OPTION (RECOMPILE) help.
  • Parameter group. Compare max degree of parallelism, cost threshold for parallelism and memory settings with the old server.
  • Storage. Compare IOPS and throughput of the old SAN with the new volume's limits. A modest gp3 volume has baseline limits (⚠ VERIFY current numbers) which a hungry reconciliation job can hit.
  • Multi-AZ commits. Many small committed transactions in a loop slow down because each waits for the standby. Batch them.

Minutes 70 to 85: fix, verify, prevent

Change one thing at a time and measure with the same workload. Then add the guardrails: Query Store enabled and retained, a dashboard of top SQL, alarms on ReadLatency, DiskQueueDepth and connection count, a regression test with a query-count budget for key endpoints (see the C# demo), and a post-migration performance test as a go-live gate.

AWS Console instructions

  1. Console → RDS → Databases → (instance) → Monitoring: the CloudWatch graphs (CPU, memory, latency, IOPS, queue depth, connections). Choose the Database Insights or Performance Insights view if enabled and set the time range to the slow period.
  2. Console → RDS → (instance) → Configuration: instance class, storage type, allocated storage, provisioned IOPS and throughput, Multi-AZ, parameter group.
  3. Console → RDS → Parameter groups → (group): compare settings against your baseline notes. Note which parameters are static (need a reboot) and which dynamic.
  4. Console → RDS → (instance) → Logs & events: error log and agent log. Slow-query logging for PostgreSQL is enabled through parameters (log_min_duration_statement).
  5. Console → CloudWatch → Metrics → RDS → Per-Database Metrics to build a side-by-side graph of ReadLatency, WriteLatency and DiskQueueDepth.

AWS CLI

# 1. Instance facts: class, storage type, IOPS, Multi-AZ, parameter group, insights status.
aws rds describe-db-instances --db-instance-identifier hl-statements-prod \
  --query 'DBInstances[].[DBInstanceClass,StorageType,AllocatedStorage,Iops,StorageThroughput,MultiAZ,DBParameterGroups[0].DBParameterGroupName,PerformanceInsightsEnabled]' \
  --output table --profile learn-sandbox

# 2. Storage latency and queue depth over the last six hours (five-minute averages).
aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name ReadLatency \
  --dimensions Name=DBInstanceIdentifier,Value=hl-statements-prod \
  --start-time $(date -u -d '6 hours ago' +%FT%TZ) --end-time $(date -u +%FT%TZ) \
  --period 300 --statistics Average --profile learn-sandbox

aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name DiskQueueDepth \
  --dimensions Name=DBInstanceIdentifier,Value=hl-statements-prod \
  --start-time $(date -u -d '6 hours ago' +%FT%TZ) --end-time $(date -u +%FT%TZ) \
  --period 300 --statistics Maximum --profile learn-sandbox

# 3. Database load by wait event from Performance Insights data (resource id looks like db-ABCDEFGHIJKL; API kept for automation, VERIFY for Database Insights).
aws pi get-resource-metrics --service-type RDS --identifier db-ABCDEFGHIJKL \
  --metric-queries '[{"Metric":"db.load.avg","GroupBy":{"Group":"db.wait_event","Limit":5}}]' \
  --start-time $(date -u -d '3 hours ago' +%FT%TZ) --end-time $(date -u +%FT%TZ) \
  --period-in-seconds 300 --profile learn-sandbox

# 4. Compare parameter groups of the old and new settings you recorded (look at one setting).
aws rds describe-db-parameters --db-parameter-group-name hl-sqlserver-se-16 \
  --query "Parameters[?ParameterName=='max degree of parallelism' || ParameterName=='cost threshold for parallelism'].[ParameterName,ParameterValue,ApplyType]" \
  --output table --profile learn-sandbox

T-SQL to run on the SQL Server instance (use a read-only investigation login; these are all read-only queries):

-- Top statements by total elapsed time since the plan cache was last cleared.
SELECT TOP 10
    qs.execution_count,
    qs.total_elapsed_time / 1000 AS total_elapsed_ms,
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END
          - qs.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_elapsed_time DESC;

-- Top waits since instance start (ignore benign background waits when you read it).
SELECT TOP 10 wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%' AND wait_type NOT IN ('LAZYWRITER_SLEEP','XE_TIMER_EVENT','REQUEST_FOR_DEADLOCK_SEARCH')
ORDER BY wait_time_ms DESC;

-- Compatibility level and Query Store state per database.
SELECT d.name, d.compatibility_level, d.is_query_store_on FROM sys.databases AS d;

-- Statistics freshness for one table.
SELECT s.name AS stat_name, sp.last_updated, sp.rows, sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID('dbo.StatementLine');

The equivalents on RDS for PostgreSQL: pg_stat_statements (top SQL), EXPLAIN (ANALYZE, BUFFERS) (actual plan with I/O), ANALYZE table_name (refresh statistics), pg_stat_activity (waits and blocking) and the parameters work_mem, random_page_cost and effective_cache_size in the parameter group.

Infrastructure as Code

Make monitoring and the performance-relevant settings explicit, so the new instance is not a mystery.

using Amazon.CDK;
using Amazon.CDK.AWS.CloudWatch;
using Amazon.CDK.AWS.RDS;
using Constructs;

public class StatementsDbTuningStack : Stack
{
    public StatementsDbTuningStack(Construct scope, string id, IStackProps props) : base(scope, id, props)
    {
        // Parameter group with the settings you decided on (names as exposed by RDS for SQL Server; ⚠ VERIFY for your version).
        var parameterGroup = new ParameterGroup(this, "SqlServerParams", new ParameterGroupProps
        {
            Engine = DatabaseInstanceEngine.SqlServerSe(new SqlServerSeInstanceEngineProps
            {
                Version = SqlServerEngineVersion.VER_16
            }),
            Description = "Statements DB tuning after migration",
            Parameters = new Dictionary<string, string>
            {
                ["max degree of parallelism"] = "4",
                ["cost threshold for parallelism"] = "50"
            }
        });

        var readLatency = new Metric(new MetricProps
        {
            Namespace = "AWS/RDS",
            MetricName = "ReadLatency",
            DimensionsMap = new Dictionary<string, string> { ["DBInstanceIdentifier"] = "hl-statements-prod" },
            Statistic = "Average",
            Period = Duration.Minutes(5)
        });

        new Alarm(this, "ReadLatencyHigh", new AlarmProps
        {
            AlarmName = "hl-statements-read-latency",
            AlarmDescription = "Average read latency above 20 ms for 15 minutes. See playbook slow-sql-after-rds-migration.",
            Metric = readLatency,
            Threshold = 0.02,                    // seconds
            EvaluationPeriods = 3,
            ComparisonOperator = ComparisonOperator.GREATER_THAN_THRESHOLD,
            TreatMissingData = TreatMissingData.NOT_BREACHING
        });

        Amazon.CDK.Tags.Of(this).Add("Project", "cea-lab");
        Amazon.CDK.Tags.Of(this).Add("Lab", "m17-playbook-slow-sql");
    }
}

Complete code

A round-trip counter that shows why latency multiplies

This demo needs no database server and no AWS account. It uses EF Core with an in-memory SQLite database and a command interceptor that counts every command sent. It runs the same report two ways, N+1 and a single projected query, and shows the time those round trips would add at a latency you choose. The latency figure is an assumption you pass in, not a measurement: the point is that cost grows with the count, so a migration that adds even a millisecond per call multiplies against every N+1 pattern.

RoundTripDemo/Program.cs
using System.Data.Common;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

var assumedLatencyMs = args.Length > 0 ? double.Parse(args[0], System.Globalization.CultureInfo.InvariantCulture) : 1.0;

using var connection = new SqliteConnection("Data Source=:memory:");
connection.Open();                                   // keep open: an in-memory SQLite database lives as long as the connection

var counter = new CommandCounter();
var options = new DbContextOptionsBuilder<BankContext>()
    .UseSqlite(connection)
    .AddInterceptors(counter)
    .Options;

using (var setup = new BankContext(options))
{
    setup.Database.EnsureCreated();
    for (var a = 1; a <= 200; a++)
    {
        var account = new Account { Number = $"ACC-{a:D4}" };
        for (var t = 1; t <= 5; t++) account.Transactions.Add(new Txn { Amount = t * 10m });
        setup.Accounts.Add(account);
    }
    setup.SaveChanges();
}

// Scenario A: the N+1 pattern. One query for accounts, then one query PER account.
counter.Reset();
using (var db = new BankContext(options))
{
    var totals = new Dictionary<string, decimal>();
    foreach (var account in db.Accounts.AsNoTracking().ToList())                       // 1 query
    {
        totals[account.Number] = db.Transactions.AsNoTracking()                         // N queries
            .Where(t => t.AccountId == account.Id).Sum(t => t.Amount);
    }
}
var nPlusOne = counter.Count;

// Scenario B: one projected, grouped query. The database does the aggregation.
counter.Reset();
using (var db = new BankContext(options))
{
    var totals = db.Accounts.AsNoTracking()
        .Select(a => new { a.Number, Total = a.Transactions.Sum(t => t.Amount) })
        .ToList();                                                                      // 1 query
}
var single = counter.Count;

Console.WriteLine($"Assumed round-trip latency: {assumedLatencyMs} ms (an input, not a measurement)");
Console.WriteLine($"{"pattern",-22} {"commands",9} {"added latency",15}");
Console.WriteLine($"{"N+1 (loop)",-22} {nPlusOne,9} {nPlusOne * assumedLatencyMs,12:F0} ms");
Console.WriteLine($"{"one grouped query",-22} {single,9} {single * assumedLatencyMs,12:F0} ms");

const int budget = 5;
if (nPlusOne > budget) { Console.WriteLine($"FAIL: N+1 report exceeds the query budget of {budget}."); return 2; }
return 0;

public class Account
{
    public int Id { get; set; }
    public string Number { get; set; } = "";
    public List<Txn> Transactions { get; set; } = new();
}

public class Txn
{
    public int Id { get; set; }
    public int AccountId { get; set; }
    public decimal Amount { get; set; }
}

public class BankContext(DbContextOptions<BankContext> options) : DbContext(options)
{
    public DbSet<Account> Accounts => Set<Account>();
    public DbSet<Txn> Transactions => Set<Txn>();
}

public class CommandCounter : DbCommandInterceptor
{
    private int _count;
    public int Count => _count;
    public void Reset() => Interlocked.Exchange(ref _count, 0);

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        Interlocked.Increment(ref _count);
        return result;
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default)
    {
        Interlocked.Increment(ref _count);
        return new ValueTask<InterceptionResult<DbDataReader>>(result);
    }
}

Create a console project targeting net10.0, run dotnet add package Microsoft.EntityFrameworkCore.Sqlite (use the 10.x version), save the code as Program.cs and run dotnet run -- 1.0. Expected: 201 commands for the N+1 version and 1 for the grouped query. Try dotnet run -- 0.2 and dotnet run -- 5 to see how the added latency changes.

Pool settings worth setting explicitly

Program.cs (excerpt)
// SQL Server connection string pieces that matter after a move to RDS (values are examples).
var cs = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder
{
    DataSource = "hl-statements-prod.xxxxxxxx.us-east-1.rds.amazonaws.com",
    InitialCatalog = "Statements",
    Encrypt = Microsoft.Data.SqlClient.SqlConnectionEncryptOption.Strict,   // TLS, verify the certificate (see the security notes)
    MinPoolSize = 10,                // avoid a burst of new connections (and TLS handshakes) on a cold start
    MaxPoolSize = 100,               // must be below what the instance can serve across ALL tasks
    ConnectTimeout = 15,
    CommandTimeout = 30,             // fail slow queries instead of waiting forever
    ApplicationName = "hl-statements-api"   // shows up in the database's session list
}.ConnectionString;

Code explained line by line

  1. SqliteConnection("Data Source=:memory:") creates a database in memory that disappears when the connection closes, so the demo is self-contained. connection.Open() keeps it alive for the whole run.
  2. CommandCounter derives from DbCommandInterceptor. EF Core calls ReaderExecuting (sync) or ReaderExecutingAsync (async) for every query it sends, so the counter sees every round trip.
  3. Interlocked.Increment keeps the counter correct even if a request runs queries on several threads.
  4. Scenario A's foreach runs a second query inside the loop, once per account. That is the N+1 pattern: one query for the list plus N queries for the details.
  5. Scenario B uses Select with Sum over the navigation property. EF Core translates it into a single GROUP BY style SQL statement, so the database aggregates and sends only 200 small rows.
  6. AsNoTracking() skips change tracking for read-only queries, saving memory and CPU in the app.
  7. The budget check turns the lesson into a regression test: a real endpoint test fails the build when it issues more queries than agreed.
  8. In the connection string, MaxPoolSize is per application instance. With 20 tasks each allowed 100, the database must handle up to 2,000 connections, so size it from the total, not per task.
  9. CommandTimeout = 30 makes a slow query fail visibly rather than silently hold a connection and pool slot, which is what turns one slow query into an outage.

Security considerations

⚠ Learning shortcut — not for production

Investigation often tempts people to run ad-hoc queries as the master user, to turn on detailed query capture that records parameter values, or to restore a production backup into a laptop-accessible database to "see what is happening". Parameter values in banking data are personal data. Use a read-only investigation role, keep sensitive parameter logging off, and test on masked or synthetic data in a private subnet.

  • Do not enable EnableSensitiveDataLogging in production. It writes parameter values (names, account numbers) into logs.
  • Query text and plans can contain literals. Treat Query Store exports, Performance Insights top-SQL and plan XML as sensitive, and keep access limited.
  • Keep TLS on and verify the server certificate. Setting TrustServerCertificate=true to "make the connection work" removes the protection; install the RDS certificate bundle in the trust store instead (⚠ VERIFY the current certificate authority for your instance).
  • No credentials in connection strings in source. Retrieve from Secrets Manager, see Secrets Manager and Parameter Store.
  • Changes need approval. Updating statistics, changing parameter groups, forcing plans and scaling the instance are production changes, with a change ticket and a rollback plan. Parameter-group changes to static parameters need a reboot.
  • Read replicas hold the same data. Apply the same encryption, network and access rules.

Banking use case

For Harbourline's statements database, the Monday incident decomposed into three findings. First, the application tasks sat in Availability Zone b while the primary was in a; the monthly statement screen issued 38 queries (an N+1 over statement lines), so the added cross-AZ latency of a fraction of a millisecond per call became noticeable. Fixing the query to one projection cut it to 3 queries. Second, Query Store showed the reconciliation query had changed from an index seek to a scan after compatibility level 160 was applied, and a missing nonclustered index on StatementLine(AccountId, PostedDate) had not been recreated by the migration tooling. Recreating it restored the plan. Third, the volume's provisioned throughput was below what the old SAN delivered, so the nightly job was throttled; raising throughput for the batch window (and scheduling it outside the business day) fixed it. Each finding is recorded with the metric, the plan before and after, the change ticket and the rollback.

Common mistakes

  • Scaling the instance first. It hides N+1 and plan problems and costs money every hour.
  • Judging by one slow run. Use total time across executions, and compare against a baseline.
  • Forgetting statistics after a restore.
  • Raising the compatibility level and the application in the same change with no way to separate effects.
  • Ignoring Multi-AZ write cost in code that commits row by row.
  • Setting MaxPoolSize per task without adding up all tasks.
  • Adding indexes blindly from the missing-index DMV. They cost writes and storage, and the DMV ignores existing similar indexes.
  • Testing on a tiny dataset or a warm cache and declaring victory.

Troubleshooting

SymptomLikely causeFix
All endpoints slightly slower, database load lowAdded network latency (cross-AZ or hybrid) multiplied by query countReduce round trips (batching, projection), move the app to the primary's AZ where possible
One query much slower, same SQL as beforePlan changed (statistics, compat level, parameter sniffing)Compare plans in Query Store, update statistics, force the good plan if needed
High read I/O wait, DiskQueueDepth above 1 sustainedStorage throughput or IOPS limit, or scans from a missing indexCheck volume limits and indexes, raise provisioned IOPS or throughput if justified
Slow only in the first minutes after a restart or failoverCold cacheWarm up with a scheduled workload, size memory to the working set
Slow writes, commit waitsMulti-AZ synchronous commit, many tiny transactionsBatch writes, keep transactions short
App times out waiting for connectionPool exhausted by slow queries or too smallFix the slow queries first, then size pool, set CommandTimeout
CONVERT_IMPLICIT warnings in plansParameter type mismatch (nvarchar parameter against varchar column)Map the property as varchar in EF Core, or align column types
CPU at 100% in burstsParallelism settings or an expensive query in a loopReview max degree of parallelism and cost threshold for parallelism, find top CPU SQL
Reporting queries slow the OLTP workloadSame instance for bothUse a read replica (with replication-lag awareness)

Cost and cleanup

Cost label: free. The demo runs locally with an in-memory database. If you reproduce the problem on a sandbox RDS instance, that is paid: a small SQL Server instance has licence-included hourly pricing and storage cost (⚠ VERIFY current price), so prefer RDS for PostgreSQL on a small burstable class, deploy and destroy within an hour, or skip the AWS part entirely.

# Only if you created a sandbox instance. Skip the final snapshot only for disposable sandbox data.
aws rds delete-db-instance --db-instance-identifier <sandbox-instance> \
  --skip-final-snapshot --delete-automated-backups --profile learn-sandbox

aws cloudwatch delete-alarms --alarm-names hl-statements-read-latency --profile learn-sandbox

# Confirm nothing tagged for this lesson remains
aws resourcegroupstaggingapi get-resources --tag-filters Key=Lab,Values=m17-playbook-slow-sql --profile learn-sandbox

Exercises

  • 🟢 Guided: Run the demo with latencies 0.2, 1 and 5 ms. Write down the added latency for each pattern. Then change the budget check to 3 and see the exit code.
  • 🟡 Challenge: Add a third scenario using Include(a => a.Transactions) and run it. How many commands does EF Core send? Then add .AsSplitQuery() and compare. Explain the trade-off between one large joined result and several queries.
  • 🔴 Live problem: "The overnight reconciliation job went from 40 minutes to 3 hours after the move to RDS. Database CPU is at 20% and ReadLatency looks normal. The job runs 60,000 small SELECTs and commits after each insert. The app runs in a different AZ." Rank your hypotheses and the evidence for each, and say what you would change first.

Assignment

Create a post-migration performance checklist for your portfolio: a baseline you capture before a migration (top SQL, plans, wait profile, query counts per endpoint), the comparison you run after, the parameter and storage settings to confirm, and the go-live gate. Commit as runbooks/post-migration-sql-performance.md with fictional data.

Solutions

Knowledge check

Interview questions

  • A query is slow after moving to RDS. How do you investigate? Establish the baseline, look at database load and waits, find top SQL by total time, compare plans, check statistics, compatibility level, indexes, storage limits, parameter group and round-trip count.
  • What is an N+1 query and how do you fix it in EF Core? One query for a list plus one per item. Fix with a projection, Include or a split query, or a set-based query, and guard with a query budget in tests.
  • What does Multi-AZ cost you in performance? Synchronous commit latency on writes. You gain availability, and mitigate with batching.
  • How is RDS tuning different from on-premises? No OS access, settings through parameter groups, storage performance bought through volume type and IOPS, and monitoring through managed tools.
  • When would you use a read replica? Offloading reads and reporting, accepting replication lag, and not for read-your-own-write flows.

Certification practice

Completion checklist

  • I can split query time into network, plan, execution, waiting and transfer
  • I can say how Performance Insights or Database Insights shows load by wait event
  • I ran the round-trip demo at three latencies and explained the result
  • I know the migration suspects: statistics, compatibility level, indexes, types, storage, Multi-AZ
  • I can explain how to size a connection pool across many tasks
  • I ran the cleanup steps and confirmed nothing tagged m17-playbook-slow-sql remains

Key takeaways

  • Start with database load and the top wait, and compare to a baseline.
  • Cost grows with round trips: N+1 patterns hurt more after any added latency.
  • After a migration check statistics, compatibility level, indexes, data types and storage limits.
  • Multi-AZ makes commits slower: batch writes.
  • Change one thing at a time, and keep Query Store and alarms so the next regression is visible.

Further reading

Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.

On this page