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

この文書の適用条件

そのまま実行できるコマンド

-- 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);

結果の読み方

こういう状況で使います

• 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

考えられる原因（可能性の高い順）

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.

確認手順

1. 1

   Estimate compression space reduction for existing tables

   参照のみ

   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

   参照のみ

   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

   参照のみ

   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

   参照のみ

   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

   中

   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

   高

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

対応方法

これで解決しない場合に確認すること

• 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.

この文書の根拠と限界

よくある質問

この文書がカバーする質問

• 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

リスク表示の意味

• 参照のみデータと設定を変更しません。
• 低影響は限定的ですが、権限と負荷の確認が必要です。
• 中性能・ロック・コストに影響する可能性があります。
• 高障害・データ損失・復旧作業が発生する可能性があります。
• 専門家レビュー必須本番適用前に別途レビューが必須です。

GIIPの対応範囲

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.

関連サービス

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