Causes and Countermeasures for Disk I/O Performance Degradation After Migrating an On-Premises SQL Server to AWS

Applicability of This Document

Ready-to-Run Commands

-- Target: SQL Server 2008 or later / Amazon RDS for SQL Server
-- Permission: reference permission on the target table (execute permission for sp_estimate_data_compression_savings)
-- Change: none (estimate only, does not perform actual compression)
-- Production execution: allowed
USE [SampleDB];
GO
EXEC sys.sp_estimate_data_compression_savings
    @schema_name = 'dbo',
    @object_name = 'Orders',
    @index_id = NULL,
    @partition_number = NULL,
    @data_compression = 'PAGE';

-- Target: SQL Server 2008 or later / Amazon RDS for SQL Server
-- Permission: metadata visibility on sys.databases
-- Change: none (read-only)
-- Production execution: allowed
SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'SampleDB';
-- If the result is 'REPLICATION', CDC or transactional replication is holding unread logs

-- Target: SQL Server 2008 or later (CDC-supported editions) / Amazon RDS for SQL Server
-- Permission: db_owner (execute permission for cdc.sp_cdc_cleanup_job)
-- Change: yes (deletes data from CDC change tables beyond the retention period)
-- Production execution: allowed, but verify that downstream integrations are not still consuming the data to be deleted
USE [SampleDB];
GO
EXEC sys.sp_cdc_cleanup_job;

-- Target: SQL Server 2008 or later / Amazon RDS for SQL Server
-- Permission: sysadmin or db_owner
-- Change: yes (truncates the end of the log file to reduce its size)
-- Production execution: generally allowed, but only after confirming log_reuse_wait_desc is NOTHING
USE [SampleDB];
GO
-- specify target size in MB to shrink to
DBCC SHRINKFILE (N'SampleDB_log', 1024);

How to Read the Results

When to Use This

• Nightly batches and index rebuilds take two or more times longer, even with an AWS instance of equivalent or higher spec than the on-premises server before migration
• The entire database becomes temporarily unresponsive during large-scale data loads or index rebuilds
• Batches never complete despite CPU having available capacity
• Storage capacity sharply depletes shortly after migration, triggering frequent RDS auto-storage scaling
• Increasing Provisioned IOPS (io2, etc.) only drives up storage costs without achieving fundamental improvement

Possible Causes (Most Likely First)

1. 01

   Latency inherent to network-attached storage (EBS)

   On-premises physical NVMe/SSD is directly attached via the PCIe bus and operates with microsecond-level latency, but Amazon EBS, the standard for AWS RDS/EC2, is a virtual block storage connected to the instance via a dedicated internal network link. Physical latency (in the order of milliseconds) is structurally unavoidable.

2. 02

   IOPS and throughput throttling (bandwidth limiting)

   IOPS and throughput (MB/s) limits per second are strictly defined according to instance size, volume type, and allocated capacity. General-purpose volumes such as gp2/gp3 consume burst credits when exceeding the baseline performance, and reaching limits during large-scale data loads or index rebuilds causes severe throttling that can make the entire DB unresponsive. Even raising volume-side IOPS with io2 or similar may be bottlenecked by the instance-side EBS dedicated bandwidth (EBS-Optimized).

3. 03

   Cost inflation from oversizing Provisioned IOPS

   When attempting to easily resolve insufficient I/O performance by heavily provisioning high-cost IOPS types such as io2/io2 Block Express, storage costs alone can balloon to hundreds of thousands to millions of yen per month, eliminating the cost-reduction benefits of cloud migration.

4. 04

   Unreleased logs due to CDC cleanup job delay

   When large batch updates occur and the CDC cleanup job cannot keep up, the transaction log is marked as "not yet read by CDC," preventing log space from being released even after CHECKPOINT or log backups. The log file continues auto-extending, depleting storage capacity and forcing RDS auto-storage scaling to trigger.

Diagnostic Steps

1. 1

   Estimate compression space reduction for existing tables

   Read-only

   Use sys.sp_estimate_data_compression_savings to determine how much disk I/O reduction and storage cost savings potential exists.

2. 2

   Identify the cause of unreleased logs with log_reuse_wait_desc

   Read-only

   Check whether sys.databases.log_reuse_wait_desc is fixed at REPLICATION. If so, CDC or transactional replication delay is suspected.

3. 3

   Check whether CDC is enabled and if capture is delayed

   Read-only

   Verify CDC status with sys.databases.is_cdc_enabled. If enabled, check capture delay with sys.dm_cdc_log_scan_sessions (see the related article for details).

4. 4

   Verify EBS volume and instance IOPS/throughput contracted values

   Read-only

   Check the provisioned IOPS and throughput for the volume type (gp3/io2, etc.) and the EBS bandwidth limit on the instance side via the AWS console or CLI.

5. 5

   Manually execute CDC cleanup

   Medium

   After confirming that downstream integrations have consumed all data within the retention period, execute sys.sp_cdc_cleanup_job to restore the log to a releasable state.

6. 6

   Shrink the transaction log

   High

   After confirming log_reuse_wait_desc is NOTHING, shrink the log file to the target size using DBCC SHRINKFILE.

Solutions

If This Doesn't Resolve It

• Have you checked whether EBS volume IOPS/throughput burst credits are exhausted?

  Verify that gp2/gp3 burst credit balance has not been depleted using CloudWatch metrics.

• Have you checked whether the instance-side EBS bandwidth (EBS-Optimized) is the bottleneck?

  Even raising volume-side IOPS will not produce expected performance if the instance EBS dedicated bandwidth limit has been reached.

• Have you checked the CDC cleanup job execution history and errors?

  Verify that the cleanup job regularly succeeds and that there are no consecutive failures or skips in the job history.

• Have you measured network latency including whether AWS Direct Connect is needed?

  Verify that communication latency between on-premises remaining applications and the DB on AWS is not another bottleneck.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

• I want to know the cause of slowdown after migrating on-premises SQL Server to AWS
• I want to know why storage costs surged after AWS migration
• I want to know how to handle log files that keep growing due to CDC

What the Risk Labels Mean

• Read-onlyDoes not change data or settings.
• LowImpact is limited, but permissions and load should be checked.
• MediumMay affect performance, locking, or cost.
• HighMay cause an outage, data loss, or require recovery work.
• Expert Review RequiredRequires a separate review before applying to production.

GIIP's Scope of Support

Alerts based solely on resource threshold values such as "Disk I/O exceeds 90%" cannot reveal which queries or processes are causing the I/O pressure or how much unnecessary cost is actually being incurred per month. GIIP retains storage I/O, IOPS consumption, CDC cleanup job execution status, and log file changes in the same time series, enabling root causes to be identified on a cost ($) basis. Decisions that are irreversible and high-cost, such as Provisioned IOPS increases, are operated on the basis of having a human confirm the SQL and rollback procedures to be executed beforehand.

Related Services

• データベース性能チューニング
• 障害対応・火消し支援
• クラウドコスト最適化