ApsaraDB RDS:RDS for SQL Server の容量不足の問題

原因の分析

合計データ容量は、すべてのデータファイルサイズの合計であり、割り当て済み領域と未割り当て領域の 2 つの部分で構成されます。

• 割り当て済み領域には、使用済み領域と未使用領域が含まれます。未使用領域は、同じテーブルまたはインデックス内の新しいレコードにのみ割り当てることができ、他のデータベースオブジェクトが直接使用することはできません。

• 未割り当て領域は、完全に未割り当てのエクステントで構成されます。各エクステントは 64 KB の隣接した領域です。未割り当て領域は、どのデータベースオブジェクトにも関連付けられていません。ファイルを圧縮することで、この領域を解放できます。

データ量が継続的に増加する場合、通常、未割り当て領域は小さくなります。このシナリオでは、ファイルを直接圧縮しても効果はありません。ファイルを圧縮する前に、まず割り当て済み領域を最適化して再利用する必要があります。

データ容量の再利用

データ容量を再利用するために一般的に使用される方法は次のとおりです。

• データのアーカイブ

  古い既存データなど、使用頻度の低いデータをデータベースから削除します。データを他のデータベースインスタンスに移行したり、他のフォーマットでアーカイブしたりすることもできます。この方法は、データ量と使用済みデータ容量を直接削減します。

  この方法はデータ容量の増加を制御するのに効果的ですが、データベースオブジェクトとアプリケーションロジックの特定の設計が必要です。また、アプリケーション設計者と開発者の協力も必要です。

• データの圧縮

  SQL Server 2016 以降を実行するインスタンス、および 2016 より前のバージョンを実行する Enterprise インスタンスには、組み込みのデータ圧縮機能があります。単一のテーブル、インデックス、またはパーティションで行またはページの圧縮を有効にできます。詳細については、「データ圧縮」をご参照ください。

  データ圧縮率は大きく異なります。これは、テーブルスキーマ、列のデータの型、および値の分布に依存します。比率は 10% から 90% の範囲になります。SQL Server は、専用のストアドプロシージャ sp_estimate_data_compression_savings を提供しており、特定のテーブルまたはインデックスで圧縮を有効にすることによって得られるストレージの節約量を迅速に見積もるのに役立ちます。

  説明

  • テーブルまたはインデックスの圧縮設定の変更は、データ定義言語 (DDL) 操作です。大規模なテーブルの場合、この操作は長期間のテーブルロックを引き起こし、ビジネスに影響を与える可能性があります。この操作はオフピーク時に実行する必要があります。

  • RDS for SQL Server Enterprise インスタンスの場合、ONLINE パラメーターを ON に設定できます。その後、変更を実行できます。これにより、ビジネスへの影響は最小限に抑えられます。

  • データ圧縮は CPU のオーバーヘッドを増加させます。必要に応じて影響を評価する必要があります。大規模なテーブルでのみデータ圧縮を有効にしてください。

• インデックスのデフラグ

  高いインデックスの断片化率は、クエリを遅くし、データストレージの使用量を増加させる可能性があります。インデックスをデフラグすることで、クエリ速度を向上させ、不要なストレージ容量を解放できます。

  インデックスの断片化率の表示: RDS コンソールで、自律型サービス > 性能を最適化する ページに移動します。[インデックスの使用状況] タブをクリックして、各テーブルのインデックスの断片化率を表示します。自律サービスは、インデックスの再構築または再編成の提案を提供します。

  説明

  • インデックスの断片化率は、論理的に隣接しているが物理的に隣接していないインデックスページの割合です。これは、インデックスページ内の空き領域の割合とは異なります。ただし、断片化率の高いインデックスは、再利用可能な領域の割合も高い可能性があります。

  • インデックスのページ内の平均空き領域の割合を分析するには、sys.dm_db_index_physical_stats システムビューを SAMPLED または DETAILED モードでクエリします。次に、結果セットの avg_page_space_used_in_percent 列の値を確認します。詳細については、「sys.dm_db_index_physical_stats (Transact-SQL)」をご参照ください。このクエリは多くのインデックスページを読み取るため、データベースのパフォーマンスに影響を与える可能性があります。この操作はオフピーク時に実行する必要があります。

  • インデックスの再構築操作

    この操作は、高い断片化率を持つインデックスに対してより良い最適化を提供し、より効率的です。デフォルトでは、再構築操作は対応するテーブルをロックします。Enterprise インスタンスの場合、ONLINE パラメーターを ON に設定して、長期間のテーブルロックを回避できます。

    重要

    大規模なインデックスの場合、再構築操作はデータベースのストレージ容量とログサイズを短期的に大幅に増加させる可能性があります。インデックスを再構築する前に、RDS インスタンスに再構築されるインデックスのサイズの少なくとも 2 倍の空きストレージ容量があることを確認してください。

    • RDS インスタンスのストレージ容量の表示: RDS インスタンスの [基本情報] ページに移動します。[インスタンスリソース] セクションで、インスタンスの合計および使用済みストレージ容量を表示できます。

    • 空き領域が不足している場合は、ストレージのスケールアウトを実行できます。スケールアウトが成功した後、インスタンスを再起動する必要はありません。システムは自動的に新しい容量を適用し、変更はすぐに有効になります。

    ALTER INDEX <IX_YourIndexName> ON <YourTableName> REBUILD WITH (ONLINE = ON);

    コマンドが実行された後、システムはバックグラウンドで非同期にインデックス統計を再度収集します。このプロセスには時間がかかります。したがって、コンソールページに表示される断片化率はすぐには更新されない場合があります。[再収集] ボタンをクリックして、最新のデータを手動で収集できます。データ収集が完了したら、[スクリプトのエクスポート] をクリックしてスクリプトをローカルマシンにダウンロードし、インデックスの断片化率が低下したかどうかを確認できます。

    

  • インデックスの再編成操作

    この操作は、低い断片化率を持つインデックスに対してより効率的ですが、その最適化効果は再構築ほど大きくありません。

データファイルの圧縮

前のセクションで説明したデータ容量の再利用方法は、通常、データ容量の使用量を効果的に削減できます。インスタンスの容量がまだ圧迫されている場合は、次の 2 つの方法のいずれかを使用できます。

• 方法 1: ミニバッチのループでデータファイルを圧縮します。各反復でファイルを 5 GB ずつ圧縮することをお勧めします。以下に例を示します:

  -- このスクリプトは SQL Server 2012 以降のバージョンにのみ適用されます。使用前に以下のパラメーターを指定してください。
  DECLARE @dbName NVARCHAR(128) = 'YourDBName'  -- データベース名
  DECLARE @fileName NVARCHAR(128)  -- データファイル名
  DECLARE @targetSize INT = 2000   -- ターゲットサイズ (MB)
  DECLARE @shrinkSize INT = 5120   -- 各ループ反復で圧縮するサイズ (MB)。5 GB を推奨します。
  DECLARE @currentSize INT         -- 現在のサイズ
  DECLARE @freeSize INT            -- 空き容量サイズ
  DECLARE @usedSize INT            -- 使用済み容量サイズ
  
  DECLARE @sql NVARCHAR(500)       
  DECLARE @waitTime INT = 10      -- 各圧縮操作後の待機時間 (秒)
  
  -- データファイル名を取得
  SELECT @fileName = name
  FROM sys.master_files
  WHERE database_id = DB_ID(@dbName)
  AND type_desc = 'ROWS'
  
  
  -- ループで圧縮
  WHILE 1 = 1
  BEGIN
      -- 現在のサイズ、空き容量サイズ、使用済み容量サイズを取得
      DECLARE @sql0 NVARCHAR(MAX) = N'
      USE [' + @dbName + '];
      SELECT
        @currentSize = (SUM(total_page_count) * 1.0 / 128),
        @freesize = (SUM(unallocated_extent_page_count) * 1.0 / 128)
      FROM sys.dm_db_file_space_usage
      WHERE database_id = DB_ID();'
  
      EXEC sp_executesql 
        @sql0,
        N'@currentSize INT OUTPUT, @freesize INT OUTPUT',
        @currentSize OUTPUT,
        @freesize OUTPUT
      
      PRINT 'Current size:' + CAST(@currentSize AS VARCHAR(10)) + 'MB'
      PRINT 'Free size:' + CAST(@freeSize AS VARCHAR(10)) + 'MB'
  
      set @usedSize=@currentSize - @freeSize
      PRINT 'Used size:' + CAST(@usedSize AS VARCHAR(10)) + 'MB'
  
      -- ターゲットサイズが使用済み容量サイズより小さいかどうかを確認
      IF @targetSize <= @usedSize
      BEGIN
          PRINT 'The target size is too small. Specify a new size. The target size cannot be smaller than: ' + CAST(@usedSize AS VARCHAR(20)) + 'MB'
          BREAK
      END
  
      
      -- ターゲットサイズに達したら終了
      IF @currentSize <= @targetSize
      BEGIN
          PRINT 'Shrink completed. Current size: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
          BREAK
      END
      
      -- この圧縮操作後の新しいサイズを計算
      DECLARE @newSize INT = @currentSize - @shrinkSize
      IF @newSize < @targetSize
          SET @newSize = @targetSize
          
      -- 圧縮操作を実行
      SET @sql = 'USE [' + @dbName + '];DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
      PRINT 'Executing shrink: ' + @sql
      EXEC (@sql)
      
      -- 一定期間待機してから続行
      PRINT 'Waiting ' + CAST(@waitTime AS VARCHAR(10)) + ' seconds to continue...'
      WAITFOR DELAY '00:00:10'
  END

• 方法 2: DBCC SHRINKFILE コマンドを実行して、単一のデータファイルを直接圧縮します。このコマンドは、データファイル内の未割り当て領域をオペレーティングシステムに解放します。詳細については、「データベースの圧縮」および「DBCC SHRINKFILE (Transact-SQL)」をご参照ください。

  DBCC SHRINKFILE(<File ID>, <Target size in MB>)

  クリックして例を表示

  上の図では、エクステントのサイズは 64 KB です。データファイルの合計サイズは (1673344 × 64) / 1024 = 104584 MB です。割り当て済み領域は (1313432 × 64) / 1024 = 82089.5 MB です。これは、圧縮後のデータファイルの合計サイズが 82089.5 MB 未満にはならないことを意味します。データファイルの合計サイズを 90000 MB に圧縮するには、次のコマンドを実行します:

  DBCC SHRINKFILE(1, 90000)

よくある質問

使用済みログ容量の表示

ログ容量の再利用は比較的簡単です。DBCC SQLPERF(LOGSPACE) コマンドまたは DAS を使用して、データベースログファイルの使用済み容量の割合を表示できます。使用済み容量の割合が高い場合、ログファイルを圧縮してもほとんど効果はありません。sys.databases システムビューをクエリできます。log_reuse_wait および log_reuse_wait_desc 列の出力を確認して、容量を再利用できない理由を判断します。

トランザクションログの圧縮

圧縮操作が完了したら、RDS インスタンスの [モニタリングとアラート] ページに移動して、最新のログ容量の状態を表示できます。

原因分析

ソリューション

RDS for SQL Server データベースには、データファイルとログファイルが含まれています。それぞれの容量を再利用する方法は次のとおりです:

原因の分析

その他のファイル容量とは、sqlserver.other_size、mastersize、modelsize、msdbsize などのファイルが占有する容量を指します。これらのファイルは通常小さいですが、場合によっては非常に大きくなることがあります。例:

• 多くのエラーログ (errorlog) があり、エラーログファイルのサイズが数ギガバイト、あるいはそれ以上に増加する。

• 重大な例外が発生した際に、メモリダンプファイルが自動的に生成される。

ソリューション

1. RDS インスタンスの [モニタリングとアラート] ページの [標準モニタリング] タブに移動して、これらのファイルが占有する容量を表示します。メトリックの詳細については、「標準モニタリングの表示」をご参照ください。

   

2. errorlog が大量の容量を占有している場合は、[ログ管理] ページに移動してエラーログをクリアできます。詳細については、「ログの管理」をご参照ください。

   説明

   sqlserver.other_size などの他のファイルが過剰な容量を占有している場合は、テクニカルサポートにお問い合わせください。原因の特定と問題の解決を支援します。