RDS for SQL Server インスタンスでインデックスを再構築するメンテナンスタスクを作成するにはどうすればよいですか?

簡単な説明

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

注: インデックスを再構築すると、インデックスが削除され、その後再作成されます。インデックスの種類とデータベースエンジンのバージョンに応じて、再構築操作はオフラインでもオンラインでも実行できます。再編成では、インデックスが削除されてから再作成されるのではなく、ページ上の情報が再構築されます。

RDS for SQL Server には、インデックスを再構築または再編成するためのプランやジョブを自動的に作成するメンテナンスプランはありません。ただし、次の方法を使用してインデックスを再構築できます。

• 方法 1: SQL Server エージェントジョブを作成して、インデックスを再構築し、統計を更新します。
• 方法 2: スクリプトを使用して再構築ジョブを手動で作成し、必要に応じてスケジュールを設定します。

解決策

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

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

2.    右側のパネルで、[SQL Server Agent] を右クリックします。次に、[新規]、[ジョブ] の順に選択して SQL エージェントジョブを作成します。

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

例:

• 名前: Indexrebuild_job
• 説明: インデックス再構築のための RDS for SQL Server エージェントジョブ。

4.    [ステップ] を選択し、[新規] を選択して実行ステップを追加します。新しいウィンドウが表示されます。

5.    ステップ名 を入力します。

6.    [データベース] を選択し、定期的に実行するコマンドを追加します。

以下に示すのは、インデックス再構築 SQL コマンドの例です。このコマンド例を使用すると、指定した DB 内の断片化率が 30% を超えるすべてのテーブルの断片化されたインデックスを再構築できます。1 行目の [DBNAME] の値を、正しいデータベース名に変更します。すべてのデータベースに対して同じ 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

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

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

9.    スケジュールの [名前]、[スケジュールタイプ] などのフィールドを入力し、[OK] を選択します。

10.    作成したジョブを表示して右クリックします。次に、[ステップでジョブを開始] を選択してジョブを手動で実行し、ジョブが正しく実行できることを確認します。

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

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

更新統計は一度に 1 つのテーブルで機能することに注意してください。データベースレベルのコマンド sp_updatestats (Microsoft のウェブサイト) は Amazon RDS では使用できません。更新統計を使用してカーソルを記述し、データベース内のすべてのオブジェクトの統計を更新します。または、sp_updatestats のラッパーを作成して、スケジュールを設定してください。

sp_updatestats のラッパーを使用するには、以下を実行してください。

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

create procedure myRDS_updatestats
with execute as ‘dbo’
as
exec sp_updatestats
go

2.    新しいプロシージャの実行権限をユーザーに付与します。

grant execute on myRDS_updatestats to user

3.    前述の方法 1 の手順に従って、統計更新ジョブをスケジュールします。

方法 2: スクリプトを使用して再構築ジョブを手動で作成し、必要に応じてスケジュールを設定する

断片化されたインデックスをチェックし、それらに対してスケジュールに従ってインデックスの再構築を実行するスクリプトまたはプロシージャを手動で作成できます。スクリプトを使用して、独自のコードを作成し、手動メンテナンスジョブを設定できます。

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

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

CreateDatabaseAndObjects.sql スクリプトは次の処理を行います。

• ServerDatabases という名前のテーブルを作成します。このテーブルには、現在のインスタンス上のデータベースが記録されます。システムデータベース (マスター、モデル、TempDB、msdb) は除外されます。SSIS や SSRS (rdsadmin_ReportServer、rdsadmin_ReportServerTempDB) などの SQL Server コンポーネントによって作成されたデータベースも除外されます。
• ServerTables という名前のテーブルを作成します。このテーブルには、ServerDatabases テーブル内のすべてのデータベースのテーブルがまとめられます。
• Messages という名前のテーブルを作成します。このテーブルには、インデックス上で処理されたメッセージ (REBUILD または REORGANIZE) が含まれます。必要に応じて、メッセージをコピーして手動で実行できます。

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

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

GitHub の aws-rds-indexmaintenance-job-example スクリプトを使用するには、まず CreateDatabaseAndObjects.sql をコピーして実行してください。次に、CreateWeeklyMaintenanceJob.sql スクリプトを実行します。

注: マネージドサービスの制限により、SQL Server エージェントジョブは、現在ログインしているアカウントでのみ作成できます。他のアカウントはジョブオーナーとして許可されていません。