From I/O Waits to Inside the Batch Window: Tuning SQL Server on Amazon RDS

A banking system runs a nightly end-of-day batch: interest accruals, balance updates, schedules and reports. It has to finish inside a fixed window before the next business day starts. In this case the regular end-of-day run took about 4 hours, but the month-end run took about 13.5 hours against an 11-hour window.

The database was SQL Server 2019 on Amazon RDS. This post walks through how the problem was diagnosed from wait statistics and cloud metrics, what was changed, one hypothesis that turned out to be only half right, and the queries you can reuse on your own system.

TL;DR

• The run had two separate bottlenecks: an I/O-bound phase (PAGEIOLATCH_SH and WRITELOG, IOPS pinned at the provisioned limit) followed by a CPU-bound phase (repeated full scans of job queue tables).
• Fixes: provisioned IOPS 30,000 → 60,000 (the instance's maximum), MAXDOP 8 and cost threshold for parallelism 50.
• Storage pressure disappeared, and a follow-up end-of-day run finished in about 7 hours. The batch met its performance target.
• Always check two limits in the cloud: the volume and the instance. Usable IOPS is whichever is lower.

The environment

Component	Details
Database engine	SQL Server 2019 Enterprise on Amazon RDS
Instance	db.r5.16xlarge: 64 vCPU, 512 GiB memory, 2 NUMA nodes of 32 vCPU
Storage	EBS io2 (Provisioned IOPS SSD), 30,000 IOPS
Instance settings	MAXDOP, cost threshold for parallelism and max server memory all at defaults
Workload	Batch of several hundred jobs run by many parallel application agents
Batch window	11 hours

The application itself doesn't matter much here. The pattern applies to any batch that works through large tables with many concurrent workers and uses tables as work queues.

Figure 1. Where the time went: two bottlenecks in two phases of the same run, and the fix for each.

The method: line up two timelines

The single most useful step was putting the application's job log next to the database load chart (Database Insights, DB load grouped by wait). The general approach:

1. Job timeline first: which jobs ran when, and for how long.
2. Database load by wait class over the same period. Which wait dominates in each phase?
3. Map each wait to a resource (PAGEIOLATCH to data file reads, WRITELOG to log writes, CPU to processors) and check that resource's graph against its limit, including latency.
4. Top SQL for the dominant wait. Which statements cause it?
5. Compare against a baseline using per-unit numbers, such as records per second.
6. Turn each theory into a check someone can run, and confirm it before recommending a fix.

Doing this showed the run was not one problem but two. For roughly the first five hours, load was almost all PAGEIOLATCH_SH, and that period lined up exactly with the biggest job (an interest accrual job over several million records). After that job finished, the load switched to CPU.

A baseline comparison pointed the same way. The same jobs took 2–3 times longer at month end than on an earlier daily run with almost the same number of records. The work hadn't grown, so the time was going into waiting. (The earlier daily run is not a perfect baseline, as it ran before some infrastructure changes, so treat that as supporting evidence rather than proof.)

Phase 1: the I/O-bound phase

What the metrics showed

• Top waits: PAGEIOLATCH_SH (waiting for data pages to be read from disk) and WRITELOG (waiting for log writes on commit).
• CloudWatch IOPS sat flat at about 29,500 against a provisioned 30,000.
• Read latency during the same window was about 50 ms. io2 normally answers in 1–2 ms.
• Throughput peaked at only about 243 MB/s.

A resource near its limit with low latency is just busy. A resource at its limit with rising latency is saturated, because requests are waiting in a queue. That is what happened here.

IOPS-bound or throughput-bound?

Divide throughput by IOPS to get the average I/O size:

243 MB/s ÷ 29,500 IOPS ≈ 8 KB per I/O

SQL Server pages are 8 KB, so these were single-page random reads, the signature of key lookups, not large scans. (Read-ahead during scans issues much larger I/Os.) The Top SQL list confirmed it: a primary-key lookup on a large history table was running about 7,700 times per second. Small I/Os mean you need more IOPS. Large I/Os mean you need more throughput, and should look at indexing.

Two limits, not one

In the cloud, the usable IOPS is the lower of the volume's limit and the instance's EBS limit:

Layer	Max IOPS	Max throughput
io2 Block Express volume on RDS for SQL Server	256,000	n/a (scales with IOPS and I/O size)
db.r5.16xlarge instance (EBS limit)	60,000	1,700 MB/s
db.r5b.16xlarge instance (same CPU and memory)	173,333	5,000 MB/s

So the volume (30,000) was the bottleneck, and the instance could take up to 60,000. Provisioning more than 60,000 on this instance would cost money without making anything faster. If I/O is still the limit at 60,000, the next step is the instance type (for example the r5b variant), not more IOPS.

The fix

Provisioned IOPS was raised from 30,000 to 60,000 in a single change.

Plan RDS storage changes carefully. After you modify RDS storage, you can't modify it again until a cooldown has passed (typically six hours, or until storage optimization finishes, whichever is longer). Pick the target in one step rather than stepping it up gradually.

Figure 2. Before and after raising provisioned IOPS (illustrative).

Phase 2: the CPU-bound phase

What the metrics showed

Once the big accrual job finished, the load turned green (CPU). Two statements made up most of it, both of this shape:

SELECT KEY_ID FROM JOB_QUEUE_0001

The application uses queue tables (hundreds of them, one per job). When a job starts it writes its work keys into its queue table, the agents read keys from it, each processed key is deleted, and the job completes when the table is empty. The statement has no WHERE clause, so every execution reads the whole table, and many agents were running it repeatedly.

Reading Average Active Sessions correctly

The top statement showed about 110 average active sessions (AAS) at about 6.3 executions per second. Because AAS = executions per second × average active time per execution:

110 AAS ÷ 6.3 executions/s ≈ 17 seconds of active time per execution

It's tempting to read that as "each execution uses 17 parallel threads", but the number alone can't tell you that. It could be one slow 17-second scan or a parallel scan. Only the execution plan (or last_dop in sys.dm_exec_query_stats) shows which.

The parallelism defaults

The instance was still on the defaults: MAXDOP 0 (unlimited) and cost threshold for parallelism 5. On a 64-vCPU server with many concurrent sessions, that lets even modest queries spread across many cores, and those queries then compete with each other.

• MAXDOP 8. Microsoft's guidance is to keep MAXDOP within one NUMA node and no higher than 16. With 32 vCPU per node and many batch sessions running at once, 8 is a conservative middle value.
• Cost threshold for parallelism 50. Stops cheap queries from going parallel at all. This is a common starting point.

On RDS, both settings live in the DB parameter group. The default parameter group can't be edited, so if the instance uses it you have to attach a custom group, which needs a reboot. MAXDOP can also be set per database with no restart:

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

With only a few chances to rerun a month-end batch, both changes were applied together alongside the IOPS change. Normally you'd change one thing at a time to measure each effect. Here the priority was a good run.

A hypothesis tested: ghost records in queue tables

Queue tables that are filled and emptied all the time are a classic source of ghost records. When SQL Server deletes a row it first marks it as a ghost, and a background task removes it later. Until that happens, scans still read those pages. The theory was that the queue tables were full of ghosts, which made every SELECT KEY_ID scan more expensive than it needed to be.

The check, run on one queue table after a batch:

Allocation unit	Pages	Live rows	Ghost rows	Page fullness
IN_ROW_DATA	1,235	255	373	47%
LOB_DATA	135,400 (~1 GB)	0	195,900	0.28%

That looked like strong confirmation: more ghosts than live rows, and about 1 GB of large-object pages holding nothing. But a second check a few days later, across all of the several hundred queue tables, told a different story. Together they used only about 3.4 GB, the largest was about 53 MB, and the same table had dropped from about 1 GB to 16.5 MB.

So the cleanup does catch up after the batch. The queue tables aren't a storage problem, and rebuilding them before each run wasn't needed. The real cost is reading full queue tables over and over during the run, which is about how the application polls them, not about leftover space.

Lesson: one snapshot can support a theory that a second snapshot disproves. Measure again, at a different time, before you recommend a fix that changes data.

Ruling out implicit conversion

Implicit conversion (for example an NVARCHAR parameter compared with a VARCHAR column) can stop SQL Server from seeking on an index. It needs two conditions: a predicate and a type mismatch. The queue-table scans have no WHERE clause, so there's nothing to convert. The key column on the history table being looked up was already NVARCHAR, which matches how most drivers send string parameters by default, so implicit conversion was very unlikely. The execution plan is the final word: look for CONVERT_IMPLICIT. Checking each condition separately is quicker than arguing about the claim as a whole.

The result

• After the IOPS change, CloudWatch showed no more pressure on IOPS. The I/O-bound phase was gone.
• A follow-up end-of-day run finished in about 7 hours, well inside the 11-hour window, and the batch went on to meet its performance target.

One honest caveat: that 7-hour run was a regular end-of-day, not a month end, so it isn't a like-for-like comparison with the 13.5-hour month-end run. The number to quote is the next month-end run with the same changes in place.

What's left: the application side

The CPU spikes from the queue-table scans are still there, because they come from how the batch agents read their work. Two directions remain:

• Database side: if the plans show those scans running in parallel, test reducing their parallelism further, for example with a plan guide that sets MAXDOP 1 for those statements only.
• Application side: review the number of batch agents and how they read the queue tables. Changing how often the full queue is read is likely to give a bigger gain than any further database setting.

The diagnostic queries

All of these are read-only and work on Amazon RDS for SQL Server with the master user.

1. Size of every queue table (lightweight, metadata only)

SELECT t.name AS table_name,
       SUM(ps.row_count)              AS live_rows,
       SUM(ps.in_row_used_page_count) AS in_row_pages,
       SUM(ps.lob_used_page_count)    AS lob_pages,
       SUM(ps.used_page_count) * 8.0 / 1024 AS used_mb
FROM sys.dm_db_partition_stats ps
JOIN sys.tables t ON t.object_id = ps.object_id
WHERE t.name LIKE 'JOB[_]QUEUE[_]%'
  AND ps.index_id IN (0, 1)
GROUP BY t.name
ORDER BY used_mb DESC;

Few live rows with high used_mb means bloat. Note the 8.0: with 8 / 1024 SQL Server does integer division and small tables show 0 MB.

2. Ghost records and page fullness for one table

SELECT OBJECT_NAME(ips.object_id) AS table_name,
       ips.alloc_unit_type_desc,
       ips.page_count,
       ips.record_count,
       ips.ghost_record_count,
       ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.JOB_QUEUE_0001'), NULL, NULL, 'SAMPLED') ips
WHERE ips.index_level = 0;

Use SAMPLED on large tables. It fills in the record and ghost counts from a sample of pages. DETAILED reads every page and is heavy on a busy system.

3. Top statements by CPU, with parallelism

WITH top_q AS (
    SELECT TOP 50 sql_handle, plan_handle, statement_start_offset, statement_end_offset,
           execution_count, total_worker_time, total_elapsed_time,
           total_logical_reads, total_rows, last_dop, max_dop
    FROM sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC
)
SELECT qs.execution_count,
       qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000  AS avg_cpu_ms,
       qs.total_elapsed_time / NULLIF(qs.execution_count, 0) / 1000 AS avg_elapsed_ms,
       qs.total_logical_reads / NULLIF(qs.execution_count, 0)       AS avg_logical_reads,
       qs.total_rows / NULLIF(qs.execution_count, 0)                AS avg_rows,
       qs.last_dop, qs.max_dop,
       SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,
           (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
                 ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS statement_text,
       qp.query_plan
FROM top_q qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

Picking the top 50 first and then fetching text and plans only for those keeps it light on a large plan cache.

4. Column types (implicit conversion check)

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('JOB_QUEUE_0001', 'ACCOUNT_HISTORY');

5. Which files grow during the batch

SELECT DB_NAME(database_id) AS db_name, name AS file_name, type_desc,
       size * 8.0 / 1024 AS size_mb
FROM sys.master_files
WHERE database_id IN (DB_ID(), DB_ID('tempdb'))
ORDER BY db_name, type_desc;

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();

Run these before and after the batch. If free storage drops sharply during the run, this tells you whether it's data, the log or tempdb.

CloudWatch metrics to export for the same window

ReadIOPS, WriteIOPS, ReadThroughput, WriteThroughput, ReadLatency, WriteLatency, DiskQueueDepth, CPUUtilization, FreeStorageSpace. In Database Insights or Performance Insights, also export DB load by wait and the Top SQL tab.

Lessons learned

• Line up the application timeline with the database load timeline. It shows which job causes which wait, and whether you have one problem or several.
• Saturation means the limit plus latency. IOPS at the limit with latency rising means requests are queuing.
• Throughput ÷ IOPS gives the I/O size. 8 KB means random lookups, so add IOPS. Large I/Os mean scans, so look at throughput and indexing.
• Check both cloud limits. The volume and the instance each have a ceiling, and the lower one wins.
• Don't leave parallelism at defaults on large multi-NUMA servers running concurrent batch work.
• AAS ÷ executions per second gives time per call, and nothing more. Confirm parallelism from the plan.
• Test a theory twice before acting on it. The ghost-record theory looked confirmed from one snapshot and was disproved by the next.
• Know where the database's job ends. Once infrastructure and settings are right, the remaining gains are often in how the application uses the database.

All figures in this post are approximate and anonymized. The table names (JOB_QUEUE_n, ACCOUNT_HISTORY) are generic placeholders.