次の方法で共有

  • [アーティクル]

Planning Server データベース インデックスのモニタリングと管理

更新 : 2009-04-30

この資料の内容は以下のとおりです。

  • Examine Measure Group table indexes

  • Checking index fragmentation

  • Reorganizing indexes

  • Rebuilding indexes

ほとんどのデータベース システムではシステムのパフォーマンス向上のためにインデックスが使用されますが、同様に、Planning Server システムでもインデックスが使用され最適なパフォーマンスが実現します。インデックスがクラスタ化されているかどうかに関係なく、INSERT/UPDATE を実行するとページが分割され、断片化が発生します。インデックスの断片化は時間の経過と共に深刻になり、システムのパフォーマンスが低下します。データベース管理者は、インデックスの断片化の状況を監視し、インデックスの断片化が進んだ場合の対処方法を理解しておくことが重要です。

Planning アプリケーション データベース内のすべてのテーブルでインデックスの断片化状態を監視する必要がありますが、名前がプレフィックス "MG_" で始まるメジャー グループのテーブルについては特に注意する必要があります。アプリケーション データベースの中でも特に大量の挿入、更新、削除操作が発生するのが、メジャー グループのテーブルです。このようなデータ変更を実行する操作の種類としては、割り当ての送信および計算ルールの実行があります。メジャー グループのテーブルの中には、他と比較して多くのアクティビティが発生するテーブルがあります。インデックスを定期的に再構築または整理しないと、"MG_*" テーブルのインデックスは時間と共に断片化の度合いが深刻になり、システムのパフォーマンスが大幅に低下します。

メジャー グループのテーブルのインデックスの調査

モデルの作成時に、Planning Server によって新しい "MG_*" テーブルにクラスタ化されたインデックスが 1 つ作成されます。このクラスタ化インデックスには、そのメジャー グループ テーブルのすべてのディメンション キーが含まれます。このクラスタ化インデックスでのディメンション キーの順序は、Planning Server によって自動的に設定されます。この順序は、個々の顧客のメジャー グループ テーブルの使用方法 (たとえば、書き戻し用、データ読み込み用、ルール ベースの計算用など) を的確に反映していない可能性があります。データベース管理者は、クラスタ化インデックスの列の順序を確認する必要があり、場合によっては順序の再調整が必要になります。

たとえば、MG_Strategic_Plan_MeasureGroup_default_partition テーブルに次のようなクラスタ化インデックスが作成され、次のような列順序が設定されているとします。このインデックスの列順序は、メジャー グループのテーブルの使用方法に合わせて再調整できます。また、Excel 用 PerformancePoint アドインのユーザーは会計主体に基づいて書き戻し操作を実行することが多いので、Entity_MemberID 列をこのインデックスの先頭の列に移動した方がよい場合があります。

CREATE CLUSTERED INDEX [ClusteredIndex_default_partition] ON [dbo].[MG_Strategic_Plan_MeasureGroup_default_partition] 
(
      [Scenario_MemberId] ASC,
      [Time_Month] ASC,
      [Account_MemberId] ASC,
      [BusinessProcess_MemberId] ASC,
      [Entity_MemberId] ASC,
      [TimeDataView_MemberId] ASC,
      [Currency_MemberId] ASC,
      [BusinessDriver_MemberId] ASC,
      [Product_MemberId] ASC,
      [Flow_MemberId] ASC,
      [Intercompany_MemberId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

例

次の例では、Planning アプリケーション データベースのメジャー グループ テーブルでオンライン オプションを使用してインデックスを再構築する方法を示します。

ALTER INDEX [ClusteredIndex_default_partition] 
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REBUILD WITH (ONLINE = ON);

データベース管理者は、各メジャー グループ テーブルのインデックスを最適化するためのジョブを、Microsoft SQL Server 2005 を実行しているコンピュータに作成する必要があります。断片化の状況によっては、インデックスの再編成と再構築を異なる頻度で行うようにジョブをスケジュールします。

たとえば、インデックスの再編成は毎日行い、インデックスの再構築は週に 1 回行うようにします。メジャー グループのテーブルのクラスタ化インデックスのように、さらに頻繁に再構築を行う必要があるインデックスもあります。インデックス再構築ジョブは、深夜などのピーク以外の時間帯に実行するようスケジュールを設定します。基幹業務のデータを保持するテーブルを除き、どちらの方法もオンラインで行うことができます。インデックスの再構築をオンラインではなくオフラインで行う場合は、先にアプリケーションをオフラインにする必要があります。アプリケーションをオフラインにする方法については、Planning 管理コンソールのヘルプを参照してください。

インデックスの断片化の確認

データベースの変更を行っていると、データベースでインデックスの情報が分散化つまり断片化する場合があります。インデックスの断片化の度合いが深刻な場合、クエリのパフォーマンスが低下し、アプリケーションの応答が遅くなる可能性があります。

SQL Server 2005 では、インデックスの再編成またはインデックスの再構築を行うことで、インデックスの断片化を解消できます。インデックスを分析し、断片化の程度を判断してから、使用する最適化方法を決定する必要があります。

例

次の例では、インデックスの断片化をモニタリングする方法を示します。

次のようなコード ステートメントを実行するとします。

SELECT a.index_id, name, avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('MG_Strategic_Plan_MeasureGroup_default_partition'),
     NULL, NULL, NULL) AS a
        JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

この場合、次のような結果セットが返されます。

index_id name avg_fragmentation_in_percent

1

MG_Strategic_Plan_MeasureGroup_default_partition_PK

23.076923076923077

断片化の詳細については、「SQL Server Books Online」を参照してください。この例では、avg_fragmentation_in_percent が 30% 未満なので、推奨される解決方法は MG_Strategic_Plan_MeasureGroup_default_partition_PK の再編成です。

断片化が 30% より大きい場合は、インデックスを再構築することが推奨されます。

メモメモ :

この 30% という値は、「SQL Server Books Online」での推奨に従っています。この値は、組織での必要性に合わせて最適なように変更できます。

インデックスの再編成

インデックスの断片化の度合いが深刻でない場合は、インデックスの再編成が最良の方法です。一方、インデックスの断片化の度合いが深刻な場合は、インデックスを再構築した方が望ましい結果を得られます。断片化のガイドラインについては、「Checking Index Fragmentation」を参照してください。

例

次の例では、Planning Server でメジャー グループ テーブルの主キー インデックスを再編成する方法を示します。1 つ以上のインデックスを再編成するには、ALTER INDEX ステートメントと共に REORGANIZE 句を使用します。

ALTER INDEX [ClusteredIndex_default_partition] 
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REORGANIZE ;

インデックスの再編成は、インデックスの再構築ほど時間をかけず、リーフ レベルの断片化のみをクリーンアップします。エクステントのスキャン密度が向上するようにオブジェクトを移動することはありません。インデックス再編成コマンドは、常にオンラインで実行されます。インデックスの再編成の実行中に、ロックが長時間保持されることはありません。インデックスの再編成、およびインデックスの再編成と再構築の長所と短所については、「SQL Server Books Online」を参照してください。

インデックスの再構築

インデックスは、削除してから新しく作成することで再構築できます。新しいインデックスが作成されるときに、断片化が解消されます。

次の例では、Planning Server のメジャー グループ テーブルでオンライン オプションを使用してインデックスを再構築する方法を示します。

ALTER INDEX [ClusteredIndex_default_partition] 
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REBUILD WITH (ONLINE = ON);

各メジャー グループ テーブルのインデックスを最適化する SQL Server ジョブをスケジュールします。断片化の度合を判定した後、インデックスの再編成とインデックスの再構築を異なる頻度で行うように、ジョブをスケジュールします。たとえば、インデックスの再編成は、1 日に 1 回、夜間などのピーク以外の時間帯に行うようにスケジュールを設定し、インデックスの再構築は 1 週間に 1 回だけ行うようにします。基幹業務のデータを保持するテーブルを除き、どちらの方法もオンラインで行うことができます。

一般に、インデックスを再構築する方が、インデックスを再編成するより、よい結果が得られます。再構築では、リーフ レベルと B-tree の両方からすべてのレベルの断片化が解消され、ツリーのバランスが再調整されます。統計情報が更新され、正確な統計で "完全スキャン" を行うのと同じ結果になります。

インデックスの再構築の詳細については、「SQL Server Books Online」を参照してください。

関連項目