Amazon RDS for SQL Server インスタンス内のインデックスを再構築するメンテナンスタスクを作成する方法を教えてください。

簡単な説明

インデックスの断片化は重大な問題であり、SQL Server データベースをすぐに再構築しないと、パフォーマンスの問題が発生する可能性があります。断片化のレベルを監視し、インデックスを定期的に再構築および再編成することをおすすめします。インデックスの断片化は、データページのギャップや論理的な断片化として発生します。

RDS for SQL Server には、インデックスの再構築または再編成を行うプランやジョブを自動的に作成するメンテナンスプランはありません。

ただし、次のいずれかの方法でインデックスを再構築できます。

• SQL Server エージェントジョブを作成してインデックスを再構築し、統計情報を更新します。
• スクリプトとスケジュールを使用して手動で再構築ジョブを作成します。

注: インデックスを再構築すると、データベースエンジンはインデックスを削除して再作成します。インデックスの種類とデータベースエンジンのバージョンに応じて、再構築操作はオフラインまたはオンラインで作成できます。インデックスを再構成する際、データベースエンジンはインデックスを削除したり再作成したりすることはありません。代わりに、データベースエンジンはページ上の情報を再構築します。

解決策

SQL Server エージェントジョブを作成してインデックスを再構築し、統計情報を更新する

次の手順を実行します。

1. Microsoft SQL Server Management Studio (SSMS) クライアントを起動し、ログインします。

2. 右側のウィンドウで [SQL Server Agent] を右クリックし、[新規]、[ジョブ] を選択します。

3. [名前] にエージェントジョブの名前を入力し、[説明] に説明を入力します。

4. [OK] を選択します。

5. [ステップ] を選択し、[新規] を選択します。

6. [ステップ名] にステップの名前を入力します。

7. [データベース] を選択し、定期的に実行するコマンドを追加します。
   インデックス再構築用の SQL コマンド例を次に示します。

   Use [DBNAME]SET NOCOUNT ON  
   DECLARE @Objectid INT, @Indexid INT,@schemaname VARCHAR(100),@tablename VARCHAR(300),@ixname VARCHAR(500),@avg_fragment float,@command VARCHAR(4000)  
   DECLARE AWS_Cusrsor CURSOR FOR  
   SELECT A.object_id,A.index_id,QUOTENAME(SS.NAME) AS schemaname,QUOTENAME(OBJECT_NAME(B.object_id,B.database_id))as tablename ,QUOTENAME(A.name) AS ixname,B.avg_fragmentation_in_percent AS avg_fragment FROM sys.indexes A inner join sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') AS B  
   ON A.object_id=B.object_id and A.index_id=B.index_id  
   INNER JOIN SYS.OBJECTS OS ON A.object_id=OS.object_id  
   INNER JOIN sys.schemas SS ON OS.schema_id=SS.schema_id  
   WHERE B.avg_fragmentation_in_percent>30  AND A.index_id>0 AND A.IS_DISABLED<>1  
   ORDER BY tablename,ixname  
   OPEN AWS_Cusrsor  
   FETCH NEXT FROM AWS_Cusrsor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fragment  
   WHILE @@FETCH_STATUS=0  
   BEGIN  
   IF @avg_fragment>=30.0  
   BEGIN  
   SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD '+N' WITH (ONLINE = ON)';  
   --Can add following line for index reorganization. Else remove following line.  
   SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REORGANIZE';  
   END  
   --PRINT @command  
   EXEC(@command)  
   FETCH NEXT FROM AWS_Cusrsor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fragment  
   END  
   CLOSE AWS_Cusrsor  
   DEALLOCATE AWS_Cusrsor

   注: DBNAME を実際のデータベース名に置き換えます。上記のコマンド例を使用すると、指定したデータベース内にある、断片化率が 30% を超えたすべてのテーブルの断片化されたインデックスを再構築できます。すべてのデータベースに対して同じ SQL コマンドを実行する場合は、適宜コマンドを変更するか、データベースごとに個別のジョブを作成します。

8. [OK] を選択します。

9. [スケジュール] を選択して [新規] を選択し、インデックス再構築ジョブを実行するスケジュールを追加します。

10. スケジュールの詳細を入力し、[OK] を選択します。

11. ジョブを右クリックして [Start Job at Step] を選択し、ジョブが実行できることを確認します。

**注:**インデックスを本番データベースにデプロイする前に、開発用 RDS データベースで上記例のスクリプトをテストしてください。インデックスの再構築にかかる時間は、インデックスのサイズと数によって異なります。

最適な実行計画を生成するには、オプティマイザーにはテーブル列のキー値 (統計情報) の分布に関する最新情報が必要です。すべてのテーブルの統計情報を定期的に更新することをおすすめします。インデックスを再構築する日には、統計情報を更新することは避けてください。

統計情報の更新は、一度に 1 つのテーブルで機能します。データベースレベルのコマンドである sp_updatestats は、Amazon RDS では使用できません。詳細については、Microsoft のウェブサイトで sp_updatestats を参照してください。

統計情報の更新を使用してカーソルを書き込み、データベース内のすべてのオブジェクトで統計情報を更新します。または、sp_updatestats にラッパーを作成してスケジュールを設定します。

sp_updatestats を囲むラッパーを使用するには、次の手順を実行します。

1. 次のコマンドを実行してストアドプロシージャを作成します。

   create procedure myRDS_updatestatswith execute as ‘dbo’  
   as  
   exec sp_updatestats  
   go

2. 新しいプロシージャの実行アクセス許可をユーザーに付与します。

   grant execute on myRDS_updatestats to user

3. 統計情報の更新ジョブをスケジュールします。

スクリプトとスケジュールを使用して手動で再構築ジョブを作成する

断片化したインデックスを確認し、スケジュールに従ってインデックスの再構築を行うには、スクリプトまたはプロシージャを手動で作成します。スクリプトを使用して独自のコードを作成したり、手動メンテナンスジョブを設定したりすることができます。

GitHub のウェブサイトにある aws-rds-indexmaintenance-job-example SQL スクリプトを使用してもかまいません。これらのスクリプトは、断片化レベルに応じてインデックスを毎週再構築および再編成します。スクリプトは、データベース (IndexStats) とオブジェクト (テーブル) を作成し、データベースのテーブル、インデックス、断片化率など、インスタンス上のすべてのデータベースに関する情報を格納します。

aws-rds-indexmaintenance-job-example には、CreateDatabaseAndObjects.sql と CreateWeeklyMaintenanceJob.sql というスクリプトが含まれています。

CreateDatabaseAndObjects.sql スクリプトは次のテーブルを作成します。

• Messages テーブル。インデックスで実行される REBUILD または REORGANIZE アクションに関するメッセージが含まれます。
  注: 必要に応じて、メッセージをコピーして手動で実行できます。
• ServerDatabases テーブル。現在のインスタンス上のデータベースを記録します。
  注: このテーブルでは、システムデータベース (Master、Model、TempDB、msdb) は除外されます。このテーブルでは、SQL Server コンポーネントが作成する SSIS や SSRS (rdsadmin_ReportServer および rdsadmin_ReportServerTempDB) などのデータベースも除外されます。
• ServerTables テーブル。ServerDatabases テーブル内のすべてのデータベースに関するテーブルを収集します。

CreateWeeklyMaintenanceJob.sql スクリプトは次のストアドプロシージャを作成します。

• sp_PopulateDatabases プロシージャは、インスタンス上のすべてのデータベースを ServerDatabases テーブルに記録します。
  注: このテーブルでは、システムデータベースや SQL Server コンポーネントが作成するデータベース (SSAS、SSRS など) のデータベースは除外されます。このテーブルには、SSIS の SSIDB は含まれます。
• sp_PopulateTables プロシージャは、各データベースのテーブルを ServerTables に記録します。
  注: プロシージャはテーブルを記録した後、テーブルが属するスキーマをチェックし、スキーマに含まれるインデックスを検索します。次に、プロシージャはインデックス内で最も断片化されたインデックス情報を検索し、記録します。
• sp_ReindexTables プロシージャは ServerTables から情報を読み取り、次のルールを使用して再構築またはデフラグプロセスを開始します。
  断片化が 0～9%: 何も行わない
  断片化が 10～30%: 再編成
  断片化が 31～100%: 再構築

GitHub で入手できる aws-rds-indexmaintenance-job-example スクリプトを使用するには、次の手順を実行します。

1. CreateDatabaseAndObjects.sql スクリプトをコピーして実行します。
2. CreateWeeklyMaintenanceJob.sql スクリプトを実行します。

注: マネージドサービスの制限により、SQL Server Agent ジョブは、SSMS クライアントにログインしている AWS アカウントでのみ作成できます。