寄稿者 : Stuart Ozer、Prem Mehra、Kevin Cox

テクニカル レビュー担当者 : Lubor Kollar、Thomas Kejser、Denny Lee、Jimmy May、Michael Redman、Sanjay Mishra

大規模リレーショナル データ ウェアハウスの構築は複雑な作業です。この記事では、SQL Server での効率的な
大規模リレーショナル データ ウェアハウスの設計に役立つデザイン手法について説明します。ほとんどの大規模データ ウェアハウスでは、テーブルとインデックスのパーティション分割を使用するので、ここで示す推奨事項の多くにもパーティション分割が含まれています。これらのヒントの大部分は、SQL Server 2005 での大規模データ ウェアハウスの
構築経験に基づいています。

大きなファクト テーブルのパーティション分割を検討する

• 50 ～ 100 GB 以上のファクト テーブルのパーティション分割を検討します。
• パーティション分割すると、管理が容易になり、多くの場合はパフォーマンスも向上します。
  • 高速で、詳細なインデックス管理。
  • 柔軟なバックアップと復元のオプション。
  • データの高速な読み込みや削除。
• 1 つのパーティションに限定した場合、クエリの処理が高速になります。
• 一般に、日付キーに基づいてファクト テーブルをパーティション分割します。
  • スライディング ウィンドウが可能になります。
• (クエリプランによる) Partition Elimination が可能になります。

ファクト テーブルの日付キーにクラスタ化インデックスを作成する

• 効率的なクエリによって、キューブにデータを格納したり、履歴データ スライスを取得したりできます。
• 特定のバッチ ウィンドウのデータを読み込む場合は、ファクト テーブルのクラスタ化インデックスに対して、オプションの ALLOW_ROW_LOCKS = OFF と ALLOW_PAGE_LOCKS = OFF を使用します。これにより、クエリ時のテーブル スキャン操作が高速になり、大量更新時の過度なロック動作を回避できます。
• 各外部キーに非クラスタ化インデックスを作成します。これにより、選択度が高いディメンションの述語に基づいて行をピンポイントで抽出するクエリを実行できます。バックアップと復元、データベースの部分的な可用性などの管理要件に対応してファイル グループを使用します。

パーティションの粒度を慎重に選択する

• ほとんどのユーザーは、月、四半期、または年を使用しています。
• 削除を効率的に行うには、1 つのパーティション全体を一度に削除する必要があります。
• 1 つのパーティション全体を一度にロードすると、速度が向上します。
  • 毎日のデータ ロードに対して日単位のパーティションを使用することが有効である場合もあります。
  • ただし、1 つのテーブルのパーティション数は最大 1,000 個である点に注意してください。
• パーティションの粒度はクエリの並列処理に影響します。
  • 1 つのパーティションにアクセスするクエリは、MAXDOP (並列処理の最大限度) まで並列化できます。
  • 複数のパーティションにアクセスするクエリでは、MAXDOP まで、パーティションごとに 1 つのスレッドを使用します。
• MAXDOP の並列処理 (MAXDOP = 4 以上と仮定) が必要な場合は、パーティションの設計において、使用頻度の高いクエリが 2、3 個のパーティションのみにアクセスすることがないようにしてください。

ディメンション テーブルを適切にデザインする

• 日付ディメンション以外のすべてのディメンションに整数の代理キーを使用します。ディメンションの代理キーで使用できる最小の整数を使用します。これにより、ファクト テーブルの列数を少なくすることができます。
• DATETIME データ型から派生できる整数型のわかりやすい日付キーを使用します (例 : 20060215)。
  • 日付ディメンションには代理キーを使用しないでください。
  • この列に WHERE 句を適用するクエリを簡単に作成できます。これにより、ファクト テーブルの範囲外のパーティションをクエリ対象から除去できるようになります。
• 各ディメンション テーブルのビジネス キー (代理キーではなく) にクラスタ化インデックスを作成します。
  • ファクト テーブルの読み込み中の高速参照がサポートされます。
  • タイプ 2 の変化するディメンションを管理する既存のディメンション行の高速参照がサポートされます。
• 各ディメンション テーブルのディメンション キー (代理キー) に非クラスタ化主キー インデックスを作成します。
• 他の頻繁に検索されるディメンション列に非クラスタ化インデックスを作成します。
• ディメンション テーブルのパーティション分割は行わないでください。
• 高速なデータ ロードを行うために、ファクト テーブルとディメンション テーブルの間で外部キー参照制約を結ばないようにしてください。NOCHECK オプションを用いて外部キー制約を作成することができますが、整合性の関係は結ばないでください。参照変換によって整合性を確保するか、データ ソースに対して整合性チェックを実行します。

パーティションを除去するための効率的なクエリを作成する

• できる限り、ファクト テーブルのパーティション分割キー (日付ディメンション キー) にクエリ述語 (WHERE
  条件) を直接指定します。

スライディング ウィンドウの手法を使用してデータを管理する

• ファクト テーブルへのオンライン アクセスのローリング タイム ウィンドウを管理します。最新のデータをロードし、最も古いデータをアンロードします。
• パーティション範囲の両端に空のパーティションを常に保持して、パーティションの分割 (新しいデータのロード前) とパーティションのマージ (古いデータのアンロード後) でデータの移動が発生しないようにします。
• データが格納されているパーティションの分割またはマージは行わないでください。データが格納されているパーティションを分割またはマージすると、生成されるログが 4 倍以上になり、過剰なロックが発生することもあるため、非常に効率が悪くなる可能性があります。
• データをロードするパーティションと同じファイル グループに、いったんデータをロードするための作業テーブル (ステージング テーブル) を作成します。
• 削除するパーティションと同じファイル グループに、アンロード 用の作業テーブルを作成します。
• 最新のパーティション全体を一度にロードするのが最速ですが、これはパーティション粒度がデータをロードする頻度と同じである場合にのみ可能となります (日単位のパーティションを使用し、1 日 1 回データを読み込む場合など)。
• パーティション サイズがデータの読み込み頻度と異なる場合は、最新パーティションの増分読み込みを行います。
• パーティション テーブルに一括データ ロード (バルク ロード) するためのさまざまなオプションが、ホワイトペーパー (http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/loading bulk
  data partitioned table.mspx) で説明されています。
• 必ず、一度に 1 つのパーティションをアンロードします。

初期データを効率的に読み込む

• 初期データの読み込み時に、SIMPLE または BULK LOGGED 復旧モデルを使用します。
• クラスタ化インデックスを使用してパーティション ファクト テーブルを作成します。
• インデックス付けされていない作業テーブル (ステージング テーブル) をパーティションごとに作成し、各パーティションにデータを格納するためのソース データ ファイルを分割します。

• 作業テーブル (ステージング テーブル) にデータを並列に格納します。
  • 複数の BULK INSERT、BCP、または SSIS タスクを使用します。
    • I/O のボトルネックがなければ、CPU 数と同じ数だけ、並列に実行するデータ ロード スクリプトを作成します。I/O 帯域幅が制限されている場合は、並列に使用するスクリプトを少なくします。
    • データ ロードでは 0 のバッチ サイズを使用します。
    • データ ロードでは 0 のコミット サイズを使用します。
    • TABLOCK を使用します。
    • ソースが同じサーバー上のフラット ファイルである場合、BULK INSERT を使用します。データがリモート コンピュータからロードされる場合は、BCP または SSIS を使用します。
• 各作業テーブル (ステージング テーブル) にクラスタ化インデックスを作成し、適切な CHECK 制約を作成します。SORT_IN_TEMPDB オプションは使用しないでください。
• すべてのパーティションをパーティション テーブルに切り替えます。
• パーティション テーブルに非クラスタ化インデックスを作成します。
• スループットが 14 GB/秒 (インデックス付けされていないテーブル) の SAN に対応した 64 CPU サーバーでは、1 TB のデータを 1 時間未満で読み込むことができます。詳細については、SQLCAT のブログ記事 (http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx) を参照してください。

古いデータを効率的に削除する

• できる限りパーティション切り替えを使用します。
• 数百万行を非パーティションのインデックス付きテーブルから削除する場合、次の点に注意します。
  • DELETE FROM ...WHERE ... は使用しないでください。次の問題が発生する可能性があります。
    • 大規模なロックとログの問題。
    • 削除をキャンセルした場合の長時間のロールバック。
  • 通常は以下の方法で高速化します。
    • 保持するレコードをインデックス付けされていないテーブルに挿入します。
    • テーブルにインデックスを作成します。
    • 新しいテーブルの名前を変更して元のテーブルと置き換えます。
• 次の処理をループで繰り返して少しずつ削除する方法もあります。
  DELETE TOP (1000) ...;
  COMMIT

• さらに、行を削除済みとしてマークするように更新して、後で、負荷の少ない時間帯に削除する方法もあります。

統計を手動で管理する

• パーティション テーブルの統計は、テーブル全体として管理します。
• 新しいデータを読み込んだ後、大きなファクト テーブルの統計を手動で更新します。
• パーティションのインデックスを再構築した後、統計を手動で更新します。
• 定期的なデータ ロード後に統計を定期的に更新する場合は、そのテーブルの統計保守機能をオフにしてもかまいません。
• これは、最新データのみを読み取る必要があるクエリを最適化するために重要です。
• 増分データ ロード後に小さなディメンション テーブルの統計を更新すると、パフォーマンスが向上する場合もあります。ディメンション テーブルの統計の更新で FULLSCAN オプションを使用すると、クエリ プランがより正確になります。

効率的なバックアップの方針を検討する

• 非常に大規模なデータベースの場合、データベース全体のバックアップにはかなりの時間を要します。
  • たとえば、SAN 上で 2 TB のデータベースを 10 スピンドルの RAID 5 ディスクにバックアップすると、2 時間かかる場合があります (275 MB/秒の転送レート)。
• SAN テクノロジを使用したスナップショット バックアップは有力なオプションです。
• 定期的にバックアップするデータの量を減らします。
  • 履歴パーティションのファイル グループは、READ ONLY としてマークできます。
  • ファイル グループが読み取り専用になったときに、ファイル グループのバックアップを一度だけ実行します。
  • 読み取り/書き込みファイル グループについてのみ定期的なバックアップを実行します。
• 読み取り専用ファイル グループの復元は並列で実行できない点に注意してください。