SQL Server 2008

データ ウェアハウスのクエリ パフォーマンス

Sunil Agarwal and Torsten Grabs and Dr. Joachim Hammer

概要:

• スター結合クエリの最適化
• パーティション テーブルの並列処理
• 行の圧縮とページの圧縮
• パーティションで固定されたインデックス付きビュー

SQL Server 2008 では、前のバージョンよりも強力なリレーショナル データ ウェアハウス機能が提供されますが、この新しいテクノロジを使用して、パフォーマンスの高いデータ ウェアハウスをどのように構築し、何十億ものデータ行に基づいた意思決定を支援できるか、疑問に思われるかもしれません。また、どの機能が意思決定支援クエリとレポートのパフォーマンスを最適化するのに役立つか、およびこの新しいバージョンの SQL Server® を利用することで、実際にどのようなパフォーマンスの向上を期待できるかということについても気になるのではないでしょうか。

実際のリリースが近づくにつれて、実にさまざまな疑問が浮かんできます。この記事では、SQL Server 2008 で提供されるパフォーマンス関連のデータ ウェアハウス機能の中で、重要度の高いいくつかの機能について詳しく説明します。製品の導入に先立ち、これらの説明を参考にしていただければと思います。

データベースの論理設計 : ディメンション モデリング

通常、基幹業務トランザクション アプリケーションでは、正規化されたデータベース スキーマが使用されます。リレーショナル データ ウェアハウスの論理的なデータベース スキーマを設計するとき、正規化にはあまり重点が置かれません。現在のリレーショナル データ ウェアハウスの多くは、ディメンション モデリングを使用して設計されています。ディメンション モデリングは、Ralph Kimball 氏と Margy Ross 氏の著書『データ ウェアハウス・ツールキット - 多次元データ ウェアハウス構築の実践手法』によって普及した手法です。

データ ウェアハウスを扱う機会が多い皆さんは、おそらく既にリレーショナル データ ウェアハウスでよく使用されているスキーマのパターン (スター スキーマやスノーフレーク スキーマなど) をよくご存知だと思います。ディメンション モデリングでは、ディメンション テーブルとファクト テーブルが区別されます。ディメンション テーブルはマスタ データ (製品、顧客、店舗、国など) を格納するテーブルで、ファクト テーブルはトランザクション データ (売り上げ、注文数、購入数、返品数など) を格納するテーブルです。

ディメンション テーブルとファクト テーブルは、主キー (PK) と外部キー (FK) のリレーションシップによって関連付けられます。多くのデータ ウェアハウスは、FK 制約を適用しないことで、必要なストレージのサイズを抑えています。これにより、基盤となるインデックスのストレージに関するオーバーヘッドが軽減されるので、ファクト テーブルのメンテナンスにかかるコストを削減することができます。通常、データ ウェアハウスのディメンション テーブルのサイズはかなり小さく、格納される行の数は数千または多くても数百万個です。一方、ファクト テーブルのサイズはかなり大きく、数億または数十億個の行が格納される場合もあります。したがって、論理設計では、ファクト テーブルのストレージ要件に十分な注意を払う必要があります。

このサイズという要素は、ディメンション テーブル内のどの列をキーとして選択し、ファクト テーブルとディメンション テーブルのリレーションシップを維持するかを判断するときにも影響を与えます。ディメンションのビジネス キー (ディメンションによって表現されるエンティティの実世界における ID) に基づく複合キーは、一般に複数の列で構成されます。このことは、ファクト テーブル内の対応する外部キーにとって問題となります。その理由は、ファクト テーブルの行ごとに複数行の複合キーが存在することになるからです。

この問題に対処するために、通常はサイズの小さい代理キーを使用してファクト テーブルとそのディメンションのリレーションシップを実装します。代理キーは整数型の ID 列で、ディメンション テーブルの疑似的な主キーとして機能します。ファクト テーブルがサイズの小さい代理キーを参照することで、サイズの大きいファクト テーブルのストレージ要件を大幅に縮小することができます。図 1 は、ディメンション モデリング、ディメンション テーブルとファクト テーブル、および代理キーを使用してモデル化されたデータ ウェアハウス スキーマを示しています。

Figure 1** 1 つのファクト テーブルと 2 つのディメンション テーブルから構成されたスター スキーマの例 **(画像を拡大するには、ここをクリックします)

スノーフレーク スキーマの設計では、1 つまたは数個のディメンションが複数のレベル (顧客ディメンションの場合は顧客、国、地域など) にまたがるようにすることで、データ内に過度の冗長性が見られる、サイズの大きいディメンションを正規化します。各レベルは別々のテーブルで表現されるので、スキーマはスノーフレーク (雪の結晶) の形になります。一方、スター スキーマの設計では、ディメンションは複数のテーブルにまたがりません。ファクト テーブルを中心にディメンション テーブルのグループが形成されるので、スキーマはスター (星) の形になります。

通常、ディメンション モデリングを使用してスター スキーマまたはスノーフレーク スキーマをモデル化した場合、一定のパターンに従って意思決定支援クエリを記述します。まずファクト テーブルからいくつかのメジャーを選択し、代理キーを使用してファクト行を 1 つまたは数個のディメンションに結合します。次に、ディメンション テーブルのビジネス列にフィルタ述語を適用し、1 つまたは数個のビジネス列に基づいてグループ化した後、ファクト テーブルから取得したメジャーを一定の期間にわたって集計します。以下のクエリは、このパターンを示しています。これは、スター結合クエリと呼ばれることもあります。

select ProductAlternateKey,
CalendarYear,sum(SalesAmount)
from FactInternetSales Fact
     join DimTime 
on Fact.OrderDateKey = TimeKey
     join DimProduct 
on DimProduct.ProductKey =
   Fact.ProductKey
where CalendarYear between 2003 and 2004
      and ProductAlternateKey like 'BK%'
group by ProductAlternateKey,CalendarYear

物理設計

リレーショナル データ ウェアハウスで使用される SQL クエリの多くは、スター結合クエリの構造に従って記述されます。ただし通常、意思決定者は常に新しい方法を使用して基本的なビジネス データを理解しようとするので、意思決定支援クエリの内容も時間の経過と共に変化します。したがって、データ ウェアハウスのワークロードには、高い割合でアドホック クエリが使用されます。このことが原因で、意思決定支援クエリと、ディメンション モデリングを使用してモデル化されたデータ ウェアハウス スキーマの物理設計は、困難な作業になります。

通常、SQL Server データ ウェアハウスを設計する場合は、青写真と物理設計を作成し、ワークロードの変化に合わせてこれらを調整および改良します。この青写真は、独自のデータ ウェアハウス環境に合わせて自由に変更することができます。ただし、その場合はもちろん、変更に伴って実施されるインデックスのメンテナンス作業がパフォーマンスに与える影響や、インデックスのストレージ要件など、データベースの物理設計に関するベスト プラクティスに従って作業を行う必要があります。

ファクト テーブル

青写真の設計は、通常のスター クエリの形を前提としており、ファクト テーブルにインデックスを構築します。ファクト テーブルのクラスタ化インデックスでは、複数のディメンション代理キー列 (外部キー列) をインデックス キーとして使用します。インデックス キーには、最も頻繁に使用される列が含まれるようにします。また、実際にこの設計を使用したときに、ワークロードで最も頻繁に実行されるクエリに対して、適切なアクセス パスが提供されるかどうかを確認することをお勧めします。

さらに、この青写真では、ファクト テーブル内のディメンション代理キー (外部キー) 列ごとに、単一列の非クラスタ化インデックスを作成します。これにより、いずれかのディメンションの選択度が非常に高いクエリに対して、効果的なアクセス パスが提供されます。

クラスタ化インデックスの目的は、ワークロード内の大部分のクエリのパフォーマンスを向上させることです。これに対し、非クラスタ化インデックスの目的は、特定の顧客または製品に関するファクト テーブルのメジャーを取得するクエリのパフォーマンスを向上させることです。たとえば、これらの非クラスタ化インデックスによって、ファクト テーブルをスキャンしなくても、特定の顧客の売り上げデータを取得できるようになります。

ディメンション テーブル

青写真の設計をディメンション テーブルに適用する場合、ディメンション テーブルごとにインデックスを作成する必要があります。たとえば、ディメンションの代理キー列に主キー制約を適用して非クラスタ化インデックスを作成したり、ディメンション エンティティのビジネス キー列にクラスタ化インデックスを作成したりします。ディメンション テーブルのサイズが大きい場合は、選択度の高い述語で頻繁に使用される列に非クラスタ化インデックスを追加することも検討してください。

クラスタ化インデックスを使用すると、データ ウェアハウスのメンテナンス中に実行される抽出、変換、および読み込み (ETL) 処理が効率化されます。多くの場合、これらの処理では速度が重視されます。たとえば、ディメンションの更新速度が遅いと、ディメンション内にまだ存在しない行がディメンション テーブルに追加されている間に、既存の行が更新されてしまいます。このアクセス パターンで適切に処理が実行されるようにするには、ETL 処理時に実行されるディメンション テーブルの参照および更新処理のパフォーマンスを向上させる必要があります。

ここで説明した青写真の設計は、SQL Server を使用してリレーショナル データ ウェアハウスを構築するときに、その物理設計の基盤として役立ちます。この一般的なリレーショナル データ ウェアハウスの構成を基に、SQL Server 2008 の主要な新機能について説明します。

スター結合クエリの最適化

通常、ディメンション モデリングを使用してモデル化されたリレーショナル データ ウェアハウスのスター結合クエリで実行される最も実行コストの高い処理は、ファクト テーブルの処理です。選択度が非常に高いクエリでも、ファクト テーブルから取得する行の数は、ディメンション テーブルから取得する行の数よりも桁違いに多くなります。このため、クエリのパフォーマンスを向上させるには、ファクト テーブルへの最適なアクセス パスを使用することが不可欠です。

SQL Server では、クエリ オプティマイザによって、一連のパスの中から最も推定コストの低いアクセス パスが自動的に選択されます。データ ウェアハウスの場合、最大の目標は、クエリ オプティマイザがスター結合クエリの実行プラン用に効率的なアクセス パスを選択できるようにすることです。SQL Server のクエリ オプティマイザには、パフォーマンスの高いスター結合クエリの実行プランを自動的に提供するためのさまざまな機能が含まれています。

スター結合クエリは、3 つのカテゴリに分類することができます (図 2 参照)。このように大まかなカテゴリに分類することで、SQL Server エンジンは、それぞれのカテゴリのクエリに適したプランを特定することができます。SQL Server が依存する最も重要な概念は、ファクト テーブルに対するクエリの選択度です。ファクト テーブルから取得する行の数が少ないほど、そのクエリは選択度が高いと見なされます。ファクト テーブルから取得する行の割合によって、直感的にこれらのカテゴリを理解することができます。この割合は、一般的な顧客の展開事例から割り出した値ですが、アクセス パスの定義を決定するために使用できるほど厳密な境界値ではありません。

Figure 2** スター結合クエリの選択度の範囲 **(画像を拡大するには、ここをクリックします)

1 つ目のカテゴリに分類されるのは、ファクト テーブルに格納されている行の最大 10% を処理する、選択度が非常に高いクエリです。2 つ目のカテゴリに分類されるのは、ファクト テーブルに格納されている行の 11 ～ 75% を処理する、中間の選択度を持つクエリです。そして、3 つ目のカテゴリに分類されるのは、ファクト テーブルに格納されている行の 76% 以上を処理する、選択度の低いクエリです。図の四角形の中には、各カテゴリで基本的に使用されるクエリ実行プランも記載されています。

選択度に基づいたプランの選択

通常、選択度の高いスター クエリでは、取得されるファクト テーブルの行が 10% 以下であるため、ファクト テーブルへのランダム アクセスが可能です。したがって、このカテゴリのクエリ プランは、入れ子になったループ結合、(非クラスタ化) インデックスのシーク、およびファクト テーブルのブックマーク参照に大きく依存しています。これらのクエリでは、ランダムにファクト テーブルへの I/O が実行されるので、より多くのファクト テーブルのデータを取得する場合は、順次 I/O を実行するクエリを使用するとパフォーマンスが向上します。このため、ファクト テーブルから取得される行の数が増加してきたら、別のクエリ プランの使用を検討する必要があります。

中間の選択度を持つスター クエリは、ファクト テーブルに格納されている大量の行を処理するので、通常、ファクト テーブルへのアクセス パスとして優先されるのは、ファクト テーブルのスキャンまたは範囲スキャンを使用するハッシュ結合です。SQL Server では、ビットマップ フィルタの使用によって、このようなハッシュ結合のパフォーマンスを向上させます。

図 3 は、SQL Server でのスター結合クエリの実行中に、ビットマップ フィルタによって結合のパフォーマンスを向上させるしくみを示しています。この図は、Product と Time という 2 つのディメンション テーブルに対して実行されるクエリのプランです。これらのテーブルは、代理キーによってファクト テーブルと結合されます。このクエリでは、WHERE 句などのフィルタ述語を両方のディメンション テーブルに使用することで、1 つのディメンションにつき 1 つの行のみが取得されるようにしています。2 つの結合演算子の横にある赤いテーブルが、このことを示しています。

Figure 3** 結合引き下げ処理を行うスター結合クエリ プラン **(画像を拡大するには、ここをクリックします)

それぞれの結合にはハッシュ結合を実装します。この目的は、SQL Server で、ディメンション テーブルから取得された条件に一致する行の情報を使用して、各ディメンション テーブルの結合引き下げ情報と呼ばれるものを作成できるようにすることです。緑の四角形は、結合引き下げ情報のデータ構造を表しています。SQL Server では、基盤となるディメンション テーブルから取得したデータによって結合引き下げ情報が作成されると、クエリの実行中に、ファクト テーブルを処理する演算子 (テーブル スキャンなど) にこれらのデータ構造が自動的に渡されます。この演算子は、ディメンション テーブルの行に関する情報を使用して、ディメンションの結合条件に一致しないファクト テーブルの行を除外します。

SQL Server では、クエリ処理の非常に早い段階 (ファクト テーブルから行を取得した後) で、これらのファクト テーブルの行が除外されます。これにより、除外された行がクエリ プランのそれ以降の演算子によって処理されなくなるので、CPU 使用率が低下し、場合によってはディスク I/O の回数も削減することができます。SQL Server では、ビットマップ表現によって、クエリの実行時に結合引き下げ情報のデータ構造が効率的に実装されます。

スター結合の最適化手順

最適化処理は、結合クエリの最適化に使用される標準的なヒューリスティックを使用して、クエリ実行プランの初期セットを生成します。その後、特定の用途に使用される拡張機能を呼び出して、追加のクエリ プランを生成します。

この拡張機能をデータ ウェアハウスに使用した場合、スター スキーマ、スノーフレーク スキーマ、およびスター クエリのパターンを検出し、ファクト テーブルに対するクエリの選択度を推定します。スキーマとクエリの形式がパターンに一致する場合、SQL Server ではクエリ プランが自動的にプラン領域に追加されます。その後、この領域に対してコスト ベースの最適化を実行し、最適であると判断されたクエリ プランを実行プランとして選択します。

SQL Server では、クエリの実行時に、結合引き下げの実際の選択度も監視されます。選択度が変化した場合、結合引き下げ情報のデータ構造を動的に配置し直して、最も選択度の高いプランが最初に適用されるようにします。

スター結合のヒューリスティック

多くの場合、データ ウェアハウスの物理設計ではスター スキーマが使用されますが、ファクト テーブルとディメンション テーブルのリレーションシップ (たとえば、前述の外部キー制約など) が明示的に指定されるわけではありません。外部キー制約が明示的に指定されていない場合、SQL Server では、ヒューリスティックに基づいてスター スキーマ クエリのパターンを検出する必要があります。スター結合クエリのパターンを検出するときに適用されるヒューリスティックを次に示します。

1. N 項結合されているテーブルの中で最もサイズの大きいものが、ファクト テーブルであると見なされます。他にもファクト テーブルの最小サイズに関する制約があります。たとえば、最も大きいテーブルが特定のサイズを超えていなければ、N 項結合はスター結合とは見なされません。
2. スター結合クエリに含まれるバイナリ結合では、結合条件として単一列の等価述語を使用する必要があります。また、それらの結合は内部結合である必要があります。制限が厳しいと思うかもしれませんが、通常のスター スキーマでは、ほとんどのファクト テーブルとディメンション テーブルの結合がこの条件を満たしています。上記のパターンに当てはまらない、より複雑な結合条件が使用されている場合、その結合はスター結合から除外されます。たとえば、5 ウェイ スター結合の 2 つの結合で複雑な結合述語が使用されている場合、その結合は 3 ウェイ スター結合 (と後続の 2 つの結合) として扱われる可能性があります。

以上は、あくまでもヒューリスティックに基づいた規則です。実際は、これらのヒューリスティックを基に、ディメンション テーブルがファクト テーブルとして選択されることはほとんどありません。このことは、どのプランが選択されるかということには影響を与えますが、選択されるプランの正確性には影響を与えません。この後、スター結合に含まれるバイナリ結合が、選択度の高い順に並べ替えられます。この結合の選択度は、ファクト テーブルの入力基数と結合の結果基数の割合、つまり特定のディメンションによってファクト テーブルの基数がどれぐらい減少したかを示しています。一般的には、選択度の高い結合の使用を検討します。

SQL Server のクエリ プロセッサでは、選択されたクエリ プランの推定コストが理想的な数値である場合、スター結合のパターンと前述の条件に従って、クエリが自動的に最適化されます。したがって、アプリケーションに変更を加える必要なく、パフォーマンスを大幅に向上させることができます。ただし、結合引き下げなど、スター結合の一部の最適化機能は、SQL Server Enterprise Edition のみで提供されることに注意してください。

スター結合のパフォーマンスに関する調査結果

SQL Server 2008 で提供されるスター結合の最適化機能を開発する作業の一環として、ベンチマークと実際の顧客のワークロードを基に、さまざまなパフォーマンス調査を実施しました。これら 3 つのワークロードの結果は参考になると思います。

マイクロソフトの販売部門のデータ ウェアハウス このワークロードは、マイクロソフトの販売部門で意思決定の支援を目的として内部的に使用されているデータ ウェアハウスのパフォーマンスを追跡したものです。サンプルとして、約 750 GB (インデックスを含む) のデータベースのスナップショットを作成しました。このワークロードの大部分のクエリでは、10 個を超える結合が使用されているので、クエリ処理には時間がかかります。

小売業界の顧客 この調査は、データ ウェアハウスを使用している小売業界の顧客 (通常の店舗とオンライン ショップを運営する企業) の環境を基に実施されました。この顧客の特徴は、ディメンション モデリングを使用してモデル化されたスノーフレーク スキーマと、標準的なスター結合クエリを使用していることです。この調査では、約 100 GB の未加工のデータを使用して、データ ウェアハウスのスナップショットを作成しました。

意思決定支援ワークロード この調査では、ディメンション モデリングを使用してモデル化された 100 GB のデータベースに対して実行された意思決定支援ワークロードのパフォーマンスを追跡しました。図 4 は、これら 3 種類のワークロードの結果を示しています。このグラフの値は、ワークロード内で実行されたすべてのクエリの応答時間の幾何平均を正規化したものです。これは、ワークロード内のいずれかのクエリを実行するとき、予想されるそのクエリのパフォーマンスを知るための目安として役立ちます。図の棒グラフは、スター結合の最適化機能を使用しなかった場合のベースライン パフォーマンス (1.0) と、スター結合の最適化機能を使用した場合のパフォーマンスを比較しています。これらの調査は、すべて SQL Server 2008 を使用して実施されました。

Figure 4** スター結合の最適化によるパフォーマンスの向上 **(画像を拡大するには、ここをクリックします)

図のデータからわかるように、すべてのワークロードのパフォーマンスが、12 ～ 30% と大幅に向上しています。実際の結果は環境によって異なりますが、SQL Server エンジンに対して実行される意思決定支援ワークロードのパフォーマンスは、SQL Server 2008 の新機能であるスター結合の最適化によって、約 15 ～ 20% 向上することが予想されます。

パーティション テーブルの並列処理

大規模なデータ ウェアハウスで実行されるクエリの処理速度を向上させるために、サイズの大きいファクト テーブルを日付でパーティション分割することがよくあります。これにより、データが複数のファイル グループに分割されるので、特定のデータ範囲内に含まれる行を処理するときに検索するデータの量が減少します。また、ファイル グループを多数の物理ディスクにまたがって展開すると、基盤となるディスク システムの同時実行機能によって、パフォーマンスが向上します。

SQL Server 2005 では、サイズの大きいテーブルを管理しやすくするために、サイズの大きいリレーションをサイズの小さい論理的なまとまりに分割する機能が導入されました。特に大規模な意思決定支援アプリケーションの場合は、この機能を使用して、クエリ処理のパフォーマンスを向上させることができます。

残念ながら、SQL Server 2005 を使用している一部の顧客の環境では、このようなパーティション テーブルにアクセスするクエリを実行した場合 (特に並列処理を行う共有メモリ型マルチプロセッサ コンピュータ上で実行した場合)、パフォーマンスに関する問題が発生します。これは、SQL Server 2005 では、パーティション テーブルにアクセスする並列クエリを処理するときに、そのクエリに割り当てられるスレッドが、利用可能なスレッドのサブセットに限定される場合があるためです。

たとえば、最大 64 個のスレッドを使用してクエリの並列処理を実行できる、64 個のコアが搭載されたコンピュータがあり、あるクエリからこのコンピュータの 2 つのパーティションにアクセスするとします。SQL Server 2005 は、64 個のスレッドのうち 2 個のみを取得するので、使用される CPU の処理能力は 64 分の 2 (3.1%) です。同じコンピュータの同じファクト テーブルにアクセスするクエリを実行した場合、テーブルをパーティション分割したときの方が、パーティション分割していないときよりも 10 倍以上パフォーマンスが低下したという報告も寄せられています。

SQL Server 2005 は単一のパーティションにアクセスするクエリ用に最適化されていることに注意する必要があります。このバージョンのクエリ プロセッサは、すべての利用可能なスレッドをスキャンの実行用に割り当てます。マルチコア コンピュータの単一のパーティションにアクセスするクエリを実行した場合、この特殊な最適化機能によってパフォーマンスは大幅に向上するので、複数のパーティションにアクセスするクエリでも、顧客は当然このような動作を期待したのです。

SQL Server 2008 で新しく提供されるパーティション テーブルの並列処理 (PTP) 機能を使用すると、クエリがアクセスするパーティションの数や、個々のパーティションの相対的なサイズに関係なく、既存のハードウェアの処理性能を活用して、パーティション テーブルにアクセスするクエリのパフォーマンスを向上させることができます。パーティション分割されたファクト テーブルを使用する一般的なデータ ウェアハウスのシナリオでは、特にクエリがアクセスするパーティションの数が利用可能なプロセッサ コアの数よりも少ない場合、並列プランで実行されるクエリのパフォーマンスは大幅に向上する可能性があります。この新機能は、調整や構成を行う必要なく、すぐに使用することができます。

たとえば、売り上げデータを表すファクト テーブルがあり、このデータが売り上げの発生日に基づいて 4 つのパーティションに分割されているとします。図 5 を参照していただくと、この例をイメージしやすいと思います。通常は、データをパーティション分割しない場合のように、データ範囲全体用のクラスタ化インデックスが 1 つ作成されるのではなく、ファクト テーブルの各パーティションの日付列にクラスタ化インデックスが作成されます。クエリ Q が、過去 7 日間の売り上げを集計するとします。新しい売り上げデータは、最後のパーティション (P4) からファクト テーブルに追加され続けるので、クエリを実行するタイミングによっては、そのクエリが他のパーティションにアクセスすることになります。図 5 の一番上の行は、この状況を示しています。Q1 クエリは 1 つのパーティションにのみアクセスしますが、Q2 クエリは実行時に関連データが P3 および P4 パーティションに分散しているので、これら 2 つのパーティションにアクセスします。

Figure 5** 新しい PTP 機能の動作 **(画像を拡大するには、ここをクリックします)

今度は、利用可能なスレッドが 8 つあるとします。Q1 と Q2 を SQL Server 2005 で実行すると、予期しない動作が発生する可能性があります。SQL Server 2005 の最適化機能では、クエリからアクセスするパーティションが 1 つのみであることをオプティマイザがコンパイル時に認識した場合、そのパーティションは分割されずに 1 つのテーブルとして扱われるので、生成されるプランではすべての利用可能なスレッドがこのテーブルへのアクセスに使用されます。

この結果、1 つのパーティション (P3) にアクセスする Q1 用に生成されるプランでは、クエリが 8 つのスレッドによって処理されます (図には示されていません)。2 つのパーティションにアクセスする Q2 の場合、基盤となるハードウェアが他のスレッドを使用できる場合でも、実行エンジンは 1 つのスレッドのみを各パーティションに割り当てます。したがって、Q2 は CPU の能力をほとんど利用できないので、おそらく Q1 よりも格段に時間がかかります。

Q1 と Q2 を SQL Server 2008 で実行すると、ハードウェアがより効率的に利用されるので、パフォーマンスが向上し、動作が予測しやすくなります。この場合も、Q1 を実行するときは、利用可能な 8 つのスレッドがすべて P2 のデータ処理に割り当てられます (図には示されていません)。一方 Q2 の場合は、実行エンジンが利用可能なすべてのスレッドを P3 と P4 にラウンドロビン方式で割り当てる並列プランが作成されます。その結果、この 2 つのパーティションにそれぞれ 4 つのスレッドが割り当てられます (図の一番下の行を参照)。CPU の能力がすべて利用されるので、Q1 と Q2 で同程度のパフォーマンスが得られます。

クエリがアクセスするパーティションの数よりもプロセッサ コアの数が多い場合、このラウンド ロビン方式のスレッド割り当てによって、クエリのパフォーマンスが大幅に向上します。ただし残念ながら、この例ほど簡単にパーティションへのスレッド割り当てが行われない場合もあります。

図 6 は、SQL Server 2008 がインストールされたマルチコア プロセッサ コンピュータでパーティション テーブルを使用したときに、SQL Server 2005 と比較してどの程度パフォーマンスが向上するかを示しています。このテスト結果に基づいたグラフは、パーティション テーブルをスキャンしたときのパフォーマンスを表しています。このテストでは、64 個のコアと 256 GB の RAM が搭載されたシステムを使用し、121 GB のテーブル 1 個を 11 GB のパーティション 11 個に分割しました。この図に記載されている一連のテストでは、コールド バッファ スタートとウォーム バッファ スタートの両方でヒープ ファイルの構成が使用されています。また、すべてのクエリで単純なデータ スキャンが実行されます。

Figure 6** SQL Server で新しい PTP 機能を有効にした場合のスキャン パフォーマンス **(画像を拡大するには、ここをクリックします)

Y 軸は応答時間 (秒)、X 軸は並列処理の程度 (DOP) をそれぞれ表しています。DOP は、クエリに割り当てられるスレッドの数に相当します。図からわかるように、コールド スタートでもウォーム スタートでも、応答時間は DOP が 22 になるまで短縮され続けます。DOP が 22 になった時点で、コールド スタートの場合は I/O システムが飽和 (これ以上パフォーマンスが向上しない) 状態になります。これは、この例で使用したクエリが I/O を頻繁に実行するからです。CPU を集中的に使用するワークロードでは、この制限が発生しないか、DOP が高くなった場合のみ制限が発生します。

ただし、ウォーム スタートを表す曲線では、DOP レベルが高くなるにつれて、応答時間が短縮され続けています。SQL Server 2005 では、複数のパーティションを処理するときに 1 つのパーティションに割り当てられるスレッドが 1 つのみであるため、DOP が 11 に達したあたりから、どちらの曲線も徐々に平坦になります。

実際には、DOP が増加しても、それに比例して応答時間が短縮されるわけではないことに注意してください。予期される動作は、ステップ関数の動作に近くなります。これは、基本的にクエリの処理には時間のかかる部分が存在するという事実を反映しています。したがって、たとえば、あるスキャンに 1 つまたは複数のスレッドを追加するだけでなく、残りのすべてのスキャンにもスレッドを追加して、より短時間で処理が完了するようにしない限り、最終的なクエリの処理速度は向上しません。

他にもテストを実施し、さまざまなハードウェアとファイル構成を使用して PTP の動作を確認しました。これらのテストでも、1 つのパーティションに割り当てられるスレッドの数が 2 つ以上に増加すると、スループットの向上という点で同様の現象が見られました。

大事なことを言い忘れていましたが、SQL Server 2008 で提供される新しい PTP 機能によって、クエリ プランの信頼性が向上し、特定のワークロードの実行に関するより正確な洞察を得ることができます。たとえば、PTP 機能の一環として、並列プランと順次プランを XML プラン表示で表現する処理が強化されているほか、コンパイル時のプランと実行時のプランで提供されるパーティション分割情報の質が向上しています。

データ圧縮

ビジネス インテリジェンスの普及と共に、分析用のデータが続々とデータ ウェアハウスに追加されています。この結果、管理するデータのサイズが急激に増加しています。1995 年に Winter Corporation によって初めて実施されたデータベースのサイズに関する調査によると、世界最大のシステムに格納されたデータの量は 1 TB でした。10 年後、データベースの最大サイズは 100 倍近くまで増加しました。さらに驚くべきことに、データ ウェアハウスのサイズは 2 年おきに 3 倍増加しています。このため、このような大量のデータを管理し、データ ウェアハウスのクエリで許容レベルのパフォーマンスを実現するという新たな課題が生まれました。通常、このようなクエリは複雑であり、多くの結合と集計を使用して大量のデータにアクセスします。また、ワークロードに含まれるクエリの多くが I/O を頻繁に実行することも珍しくありません。

ネイティブのデータ圧縮機能は、この問題の解決を目的としています。SQL Server 2005 SP2 では、10 進数と数値データ用に新しい可変長ストレージ形式である VarDecimal ストレージ形式が導入されました。この新しいストレージ形式を使用すると、データベースのサイズを大幅に縮小することができます。領域の節約は 2 つのメリットをもたらし、これによって I/O を頻繁に実行するクエリのパフォーマンスが向上します。1 つ目は、読み取るページ数が減少することです。そして 2 つ目は、データが圧縮された状態でバッファ プール内に格納されることによって、ページの推定寿命が長くなることです (つまり、要求されたページがバッファ内に存在する確率が高くなります)。データ圧縮によって領域は節約されますが、データを圧縮および圧縮解除する処理には当然 CPU コストがかかります。

VarDecimal ストレージ形式に基づいて構築された SQL Server 2008 では、2 種類の圧縮を利用できます。これらは、行の圧縮とページの圧縮です。行の圧縮は、VarDecimal ストレージ形式を拡張して、すべての固定長データ型を可変長ストレージ形式で格納します。

固定長データ型には、integer、char、float などがあります。SQL Server ではこれらのデータ型を可変長形式で格納しても、データ型のセマンティクスは変わりません (アプリケーションでは固定長のデータ型として認識されます)。したがって、アプリケーションに変更を加えることなく、データ圧縮のメリットを得ることができます。

ページの圧縮では、特定のページに格納された 1 つまたは複数の行に含まれている列のデータの冗長性が最小限に抑えられます。この圧縮機能では、LZ78 (Lempel-Ziv) アルゴリズムのマイクロソフトによる実装が使用されるため、冗長なデータが 1 回のみページ内に格納され、複数の列からこのデータを参照できます。ページ圧縮を使用すると、実際には行の圧縮も行われることに注意してください。

行の圧縮とページの圧縮は、1 つのテーブルとインデックス、またはパーティション テーブルとパーティション分割されたインデックスの 1 つまたは複数のパーティションで有効にすることができます。これにより、圧縮する範囲をテーブル、インデックス、およびパーティションの中から自由に選択できるので、領域の節約と CPU への影響のバランスを適切にとることができます。図 7 は、固定インデックスを使用して異なる方法でパーティション分割された売り上げテーブルを示しています。

Figure 7** 異なる圧縮設定が適用されたパーティション テーブル **(画像を拡大するには、ここをクリックします)

各パーティションは四半期を示しており、10 ～ 12 月が最新の四半期です。最初の 2 つのパーティションへのアクセスはあまり頻繁に発生せず、3 つ目のパーティションへのアクセスはある程度発生し、最後のパーティションへのアクセスは頻繁に発生しています。この場合、適切な構成を挙げるとすれば、まず最初の 2 つのパーティションでページ圧縮を有効にして、ワークロードのパフォーマンスに与える影響を最小限にとどめながら、領域をできるだけ節約します。また、3 つ目のパーティションで行圧縮を有効にし、最後のパーティションでは圧縮を実行しないようにします。

圧縮は、オンラインであるかオフラインであるかを問わず、Alter Table または Alter Index データ定義言語 (DDL) ステートメントを使用して有効にすることができます。SQL Server では、節約できる領域を見積もるためのストアド プロシージャも提供されます。どの程度の領域を節約できるかは、データの分散状態と、圧縮対象のオブジェクトのスキーマによって異なります。

さまざまな顧客のデータベースでテストを行った結果から判断すると、ほとんどのデータベースでサイズが 50 ～ 65% 減少すること、および I/O を頻繁に実行するクエリのパフォーマンスが大幅に向上することが予想されます。ただし、CPU を集中的に使用するクエリのパフォーマンスへの影響については、多少見積もりが難しく、クエリの複雑さに左右されると言えます。SQL Server では、インデックスまたはテーブルにアクセスする場合のみ、圧縮解除のコストが発生します。一般的なデータ ウェアハウスのシナリオでは、スキャン演算子の相対的な CPU コストがクエリの全体的な CPU コストよりも低い場合、CPU 使用率への影響は 20 ～ 30% 未満です。

パーティションで固定されたインデックス付きビュー

SQL Server 2008 のパーティションで固定されたインデックス付きビューを使用すると、リレーショナル データ ウェアハウス内でサマリ集計をより効率的に作成および管理し、これまで集計を有効に使用できなかった状況でも、集計を使用できるようになります。そしてこれに伴い、クエリのパフォーマンスも向上します。一般的なシナリオとしては、日付でパーティション分割されたファクト テーブルが挙げられます。このテーブルにはインデックス付きビュー (サマリ集計) が定義され、これによってクエリの処理速度が向上します。テーブル内のパーティションを切り替えると、そのパーティション テーブルに定義されている、パーティションで固定されたインデックス付きビュー内の対応するパーティションも自動的に切り替わります。

これは、SQL Server 2005 からの大きな機能強化です。SQL Server 2005 では、ALTER TABLE SWITCH 操作を使用してパーティションを切り替える前に、パーティション テーブルに定義されているインデックス付きビューを削除する必要がありました。SQL Server 2008 では、パーティションで固定されたインデックス付きビュー機能が提供されるので、インデックス付きビューによってサイズの大きいパーティション テーブルにもたらされるメリットを得ることができるだけでなく、パーティション テーブル全体の集計を再構築するコストの発生を回避することができます。このメリットとしては、集計のメンテナンスが自動的に実行されることや、インデックス付きビューの対応付けが行われることなどが挙げられます。

パーティション レベルのロックのエスカレート

SQL Server では、範囲パーティション分割がサポートされています。範囲パーティション分割を使用すると、データを管理しやすいように分割したり、使用パターンに従ってデータをグループ化したりすることができます。たとえば、売り上げデータを月または四半期単位で分割することができます。パーティションを自身のファイル グループにマップし、さらにそのファイル グループを一連のファイルにマップすることができます。これには 2 つの大きなメリットがあります。まず、パーティションを独立した 1 つの単位としてバックアップおよび復元することができます。また、使用パターンやクエリの負荷に応じて、ファイル グループを低速または高速な I/O サブシステムにマップすることができます。

ここで興味深いのは、データのアクセス パターンです。クエリと DML 操作では、パーティションのサブセットにアクセスしたり、そのサブセットを操作したりするだけで処理が完了する場合があります。したがって、たとえば 2004 年の売り上げデータを分析する場合は、関連するパーティションにアクセスするだけで済みます。他のパーティションに格納されているデータに同時にアクセスしているクエリの影響を受けないことが理想です (システム リソースへの影響は除きます)。SQL Server 2005 では、他のパーティションに格納されているデータへのアクセスが同時に発生した場合、テーブルがロックされ、他のパーティションへのアクセスに影響が出ることがあります。

SQL Server 2008 では、このような影響を最小限に抑えるために、パーティション レベルまたはテーブル レベルでロックのエスカレートを制御できるテーブル レベルのオプションが導入されています。既定では、ロックのエスカレートは SQL Server 2005 と同様にテーブル レベルで有効になります。ただし、テーブルのロックのエスカレートに関するポリシーは上書きすることができます。したがって、たとえば、次のようにロックのエスカレートを設定することができます。

Alter table <mytable> set (LOCK_ESCALATION = AUTO)

このコマンドは、テーブルのスキーマに適したロックのエスカレートの粒度を選択するよう SQL Server に指示しています。テーブルがパーティション分割されていない場合、ロックのエスカレートはテーブル レベルで有効になります。テーブルがパーティション分割されている場合、ロックのエスカレートはパーティション レベルで有効になります。また、SQL Server では、テーブル レベルでのロックを無効にするためのヒントとして、このオプションが使用されます。

まとめ

この記事では、リレーショナル データ ウェアハウスで使用する意思決定支援クエリのパフォーマンスを向上させるのに役立つ、SQL Server 2008 の機能強化についてごく簡単に説明しました。意思決定支援クエリの応答時間を短縮することは重要ですが、他にもこの記事では説明しなかった重要な要件があることに留意してください。

リレーショナル データ ウェアハウスに関連するその他の機能としては、次のものがあります。

• T-SQL における MERGE 構文のサポート : 1 つのステートメントとラウンドトリップを使用して、データベースの (ディメンション) データを更新、削除、または挿入することができます。
• SQL Server エンジンがログ記録を行うときのパフォーマンスの最適化 : より効率的な ETL が実現されます。
• グループ化セット : T-SQL で意思決定に役立つ集計クエリを容易に記述できるようになります。
• バックアップの圧縮 : 完全バックアップと増分バックアップの I/O 回数が減少します。
• リソース管理 : さまざまなワークロードへのシステム リソースの割り当てを制御することができます。

これらの魅力的な機能の詳細については、SQL Server Web サイト (microsoft.com/sql) を参照してください。

技術的知識を提供してくれた Boris Baryshnikov、Prem Mehra、Peter Zabback、および Shin Zhang に感謝します。

Sunil Agarwal は、マイクロソフトの SQL Server ストレージ エンジン グループでシニア プログラム マネージャを務めています。担当分野は、同時実行、インデックス、tempdb、LOBS、サポータビリティ、一括インポート、および一括エクスポートです。

Torsten Grabs は、マイクロソフトの SQL Server チームで、コア ストレージ エンジンのリード シニア プログラム マネージャを務めています。データベース システムの博士号を持ち、SQL Server には 10 年間携わっています。

Dr. Joachim Hammer は、マイクロソフトのクエリ処理グループでプログラム マネージャを務めています。専門分野は、大規模なデータ ウェアハウス アプリケーションにおけるクエリの最適化、分散クエリ、ETL、および情報の統合です。

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; 許可なしに一部または全体を複製することは禁止されています.