• [アーティクル]

パーティション テーブルとパーティション インデックスに対するクエリ処理の機能強化

SQL Server 2008 では、多くの並列プランでのパーティション テーブルに対するクエリ処理のパフォーマンスが向上しています。また、並列プランと直列プランを表す方法が変更され、コンパイル時と実行時の両方の実行プランで示されるパーティション分割情報が強化されています。このトピックでは、これらの機能強化について説明します。また、パーティション テーブルとパーティション インデックスのクエリ実行プランを解釈する方法、およびパーティション分割されたオブジェクトに対するクエリのパフォーマンス向上に関するベスト プラクティスについて説明します。

注意注

パーティション テーブルとパーティション インデックスは、SQL Server Enterprise Edition、Developer Edition、および Evaluation Edition でのみサポートされます。

新しいパーティション対応のシーク操作

SQL Server 2008 では、パーティション テーブルの内部表現が変更されており、クエリ プロセッサに対してテーブルは、先頭列に PartitionID を持つ複数列インデックスとして表されます。PartitionID は、特定の行を含むパーティションの ID を表すために内部的に使用される非表示の計算列です。たとえば、T(a, b, c) として定義されているテーブル T が列 a でパーティション分割され、そのテーブルの列 b にクラスター化インデックスがあるとします。SQL Server 2008 では、このパーティション テーブルは内部的に、スキーマが T(PartitionID, a, b, c) で複合キー (PartitionID, b) にクラスター化インデックスがある非パーティション テーブルとして扱われます。これにより、クエリ オプティマイザーは、パーティション テーブルまたはパーティション インデックスの PartitionID に基づいてシーク操作を実行できます。

パーティションの解消は、このシーク操作で行われるようになりました。

また、クエリ オプティマイザーが拡張され、ある条件を指定したシーク操作またはスキャン操作を PartitionID (論理的な先頭列) およびその他のインデックス キー列に基づいて実行し、続いて 2 番目のレベルのシークを実行できます。このシークは最初のレベルのシーク操作の条件を満たす値ごとに、別の条件を指定した 1 つ以上の追加の列に基づいて実行できます。つまり、スキップ スキャンと呼ばれるこの操作によって、クエリ オプティマイザーは、ある条件に基づいてシーク操作またはスキャン操作を実行してアクセス対象のパーティションを特定し、その操作内で 2 番目のレベルのインデックスのシーク操作を実行し、特定済みのパーティションのうち別の条件を満たすパーティションから行を返すことができます。たとえば、次のクエリについて考えてみます。

SELECT * FROM T WHERE a < 10 and b = 2;

この例では、T(a, b, c) として定義されているテーブル T が列 a でパーティション分割され、そのテーブルの列 b にクラスター化インデックスがあるとします。テーブル T のパーティション境界は、次のパーティション関数によって定義されます。

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

クエリを解決するために、クエリ プロセッサによって最初のレベルのシーク操作が実行され、条件 T.a < 10 を満たす行を含むすべてのパーティションが検索されます。これにより、アクセス対象のパーティションが特定されます。特定された各パーティション内で、プロセッサによって列 b のクラスター化インデックスに対する 2 番目のレベルの Seek が実行され、条件 T.b = 2 および T.a < 10 を満たす行が検索されます。

次の図は、スキップ スキャン操作を論理的に表したものです。列 a および b にデータが格納されているテーブル T を示しています。パーティションには 1 ～ 4 の番号が割り当てられており、パーティション境界は破線の縦線で表示されています。パーティションに対する最初のレベルのシーク操作 (図には示されていません) で、パーティション 1、2、および 3 が、テーブルに対して定義されたパーティション分割および列 a の述語で暗黙的に指定された Seek 条件を満たすことが特定されています。つまり、条件 T.a < 10 を満たします。スキップ スキャン操作の 2 番目のレベルの Seek 部分でスキャンされるパスは、曲線で表示されています。基本的に、スキップ スキャン操作によって、前の各パーティションで条件 b = 2 を満たす行がシークされます。スキップ スキャン操作の総コストは、3 つの個別のインデックス シークの総コストと同じです。

スキップ スキャン操作

クエリ実行プランのパーティション分割情報の表示

パーティション テーブルとパーティション インデックスに対するクエリの実行プランは、Transact-SQL SET ステートメントの SET SHOWPLAN_XML または SET STATISTICS XML を使用するか、SQL Server Management Studio のグラフィカル実行プラン出力を使用して調べることができます。たとえば、コンパイル時の実行プランを表示するにはクエリ エディターのツール バーの [推定実行プランの表示] をクリックし、実行時のプランを表示するには [実際の実行プランを含める] をクリックします。

これらのツールを使用すると、次の情報を確認できます。

  • パーティション テーブルまたはパーティション インデックスにアクセスする Scan、Seek、Insert、Update、Merge、Delete などの操作。

  • クエリによってアクセスされるパーティション。たとえば、実行時の実行プランでは、アクセスされるパーティションの総数やアクセスされる連続したパーティションの範囲を確認できます。

  • シーク操作またはスキャン操作でスキップ スキャン操作が使用され、1 つ以上のパーティションからデータが取得されるタイミング。

実行プランの表示に関する詳細については、「実行プランの操作方法に関するトピック」を参照してください。

パーティション情報に関する機能強化

SQL Server 2008 では、コンパイル時と実行時の両方の実行プランのパーティション分割情報が強化されています。実行プランで次の情報が示されるようになりました。

  • Seek、Scan、Insert、Update、Merge、Delete などの操作がパーティション テーブルに対して実行されることを示す省略可能な Partitioned 属性。

  • 先頭のインデックス キー列として PartitionID を含み、PartitionID に対して範囲シークを指定するフィルター条件を含む SeekKeys サブ要素を持つ新しい SeekPredicateNew 要素。2 つの SeekKeys サブ要素が存在する場合は、PartitionID に対するスキップ スキャン操作が使用されることを示しています。

  • アクセスされるパーティションの総数を示す概要情報。この情報は、実行時のプランでのみ確認できます。

この情報をグラフィカル実行プラン出力と XML プラン表示出力の両方で表示する方法を示すために、パーティション テーブル fact_sales に対する次のクエリについて考えてみます。このクエリでは、2 つのパーティションのデータが更新されます。

UPDATE fact_sales

SET quantity = quantity * 2

WHERE date_id BETWEEN 20080802 AND 20080902;

次の図は、このクエリのコンパイル時の実行プランにおける Clustered Index Seek 操作のプロパティを示しています。fact_sales テーブルの定義およびパーティション定義を確認するには、このトピックの「例」を参照してください。

プラン表示出力のパーティション情報。

Partitioned 属性

Index Seek などの操作がパーティション テーブルまたはパーティション インデックスに対して実行される場合、コンパイル時のプランと実行時のプランに Partitioned 属性が表示され、True (1) に設定されます。False (0) に設定された場合、この属性は表示されません。

Partitioned 属性は、次の物理操作と論理操作で表示できます。

  • Table Scan

  • Index Scan

  • Index Seek

  • Insert

  • Update

  • Delete

  • Merge

前の図に示したように、この属性は、属性が定義されている操作のプロパティに表示されます。XML プラン表示出力では、この属性は、属性が定義されている操作の RelOp ノードに Partitioned="1" として表示されます。

新しい Seek 述語

XML プラン表示出力では、SeekPredicateNew 要素がその要素を定義している操作に表示されます。この要素には、SeekKeys サブ要素を 2 つまで含めることができます。最初の SeekKeys アイテムは、論理インデックスのパーティション ID レベルで最初のレベルのシーク操作を指定します。つまり、この Seek によって、クエリの条件を満たすためにアクセスする必要があるパーティションが特定されます。2 番目の SeekKeys アイテムは、最初のレベルの Seek で特定された各パーティション内で行われるスキップ スキャン操作の 2 番目のレベルの Seek 部分を指定します。

パーティションの概要情報

実行時の実行プランでは、パーティションの概要情報に、アクセスしたパーティションの数やアクセスした実際のパーティションの ID が示されます。この情報を使用して、クエリで正しいパーティションにアクセスしているかどうか、および他のすべてのパーティションが考慮の対象から除外されているかどうかを確認できます。

Actual Partition Count および Partitions Accessed の情報が示されます。

Actual Partition Count は、クエリでアクセスされるパーティションの総数です。

XML プラン表示出力の Partitions Accessed は、新しい RuntimePartitionSummary 要素に表示されるパーティションの概要情報です。この要素は、要素が定義されている操作の RelOp ノードに表示されます。次の例に、合計 2 つのパーティション (パーティション 2 および 3) にアクセスすることを示す RuntimePartitionSummary 要素の内容を示します。

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2">

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

プラン表示のその他のメソッドを使用したパーティション情報の表示

プラン表示のメソッド SHOWPLAN_ALL、SHOWPLAN_TEXT、および STATISTICS PROFILE では、このトピックで説明したパーティション情報はレポートされません。ただし、次のような例外があります。SEEK 述語の一環として、アクセスされるパーティションが、パーティション ID を表す計算列の範囲述語によって特定されます。次の例は、Clustered Index Seek 操作の SEEK 述語を示しています。パーティション 2 および 3 にアクセスし、シーク オペレーターによって条件 date_id BETWEEN 20080802 AND 20080902 を満たす行がフィルター選択されます。

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),

        SEEK:([PtnId1000] >= (2) AND [PtnId1000] <= (3)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902))

                ORDERED FORWARD)

パーティション分割されたヒープの実行プランの解釈

SQL Server 2008 では、パーティション分割されたヒープがパーティション ID の論理インデックスとして扱われます。パーティション分割されたヒープのパーティションの解消は、実行プランでは、SEEK 述語がパーティション ID に対して指定されている Table Scan 操作として表されます。次の例は、プラン表示情報を示しています。

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

併置結合の実行プランの解釈

結合の併置は、同じパーティション関数または同等のパーティション関数を使用して 2 つのテーブルをパーティション分割し、結合の両側のパーティション列をクエリの結合条件に指定すると発生します。クエリ オプティマイザーでは、同じパーティション ID を持つ各テーブルのパーティションが個別に結合されるプランを生成できます。併置結合では必要なメモリが少なくなり、処理時間が短縮されることがあるので、この結合は非併置結合よりも処理が高速になる場合があります。オプティマイザーでは、コストの推定に基づいて、非併置プランまたは併置プランが選択されます。

併置プランでは、Nested Loops 結合は内側の 1 つ以上の結合テーブルまたはインデックス パーティションを読み取ります。Constant Scan 操作内の数値は、パーティション番号を表します。

パーティション テーブルまたはパーティション インデックスに対して併置結合の並行プランが生成された場合、結合操作 Constant Scan と Nested Loops の間に Parallelism 操作が現れます。その場合、結合外部にある複数のスレッドがそれぞれ異なるパーティションに対して読み取りや操作を行います。

次の図は、併置結合の並列クエリ プランを示しています。

併置結合実行プラン

パーティション分割されたオブジェクトの並列クエリの実行方法

クエリ プロセッサは、パーティション分割されたオブジェクトから選択するクエリに対して並列実行を使用します。クエリ プロセッサでは、実行方法の一環として、クエリに必要なテーブル パーティションと各パーティションに割り当てるスレッドの数を決定します。ほとんどの場合、クエリ プロセッサは、各パーティションに同数またはほぼ同数のスレッドを割り当て、パーティション全体でクエリを並列実行します。以降、スレッドの割り当てについてさらに詳しく説明します。

スレッドの数がパーティションの数よりも少ない場合、クエリ プロセッサが各スレッドを別々のパーティションに割り当てると、最初に、スレッドが割り当てられていないパーティションが 1 つ以上残ります。パーティションでスレッドの実行が完了すると、クエリ プロセッサは、各パーティションに 1 つのスレッドが割り当てられるまで、そのスレッドを次のパーティションに割り当てます。これが当てはまるのは、クエリ プロセッサがスレッドを他のパーティションに再割り当てする場合に限ります。

終了後に再割り当てされたスレッド

スレッドの数とパーティションの数が同じ場合、クエリ プロセッサは各パーティションに 1 つのスレッドを割り当てます。スレッドの終了時に、そのスレッドが別のパーティションに再割り当てされることはありません。

各パーティションに割り当てられている 1 つのスレッド

スレッドの数がパーティションの数よりも多い場合、クエリ プロセッサは各パーティションに同じ数のスレッドを割り当てます。スレッドの数がパーティションの数で割り切れないと、クエリ プロセッサは、使用できるすべてのスレッドを使用するために、一部のパーティションにスレッドを 1 つ多く割り当てます。パーティションが 1 つしかない場合は、すべてのスレッドがそのパーティションに割り当てられることに注意してください。次の図では、4 個のパーティションと 14 個のスレッドがあります。各パーティションには 3 つのスレッドが割り当てられ、さらに 2 つのパーティションには追加のスレッドが割り当てられて、合計で 14 スレッドが割り当てられています。スレッドは、終了時に別のパーティションに再割り当てされることはありません。

複数のパーティションに割り当てられている複数のスレッド

上の例では簡単にスレッドを割り当てる方法を示していますが、実際の方法はより複雑で、クエリの実行中に発生する他の変数を使用します。たとえば、テーブルがパーティション分割され、その列 A にクラスター化インデックスが設定されている場合、クエリで述語句 WHERE A IN (13, 17, 25) を使用すると、クエリ プロセッサは、1 つ以上のスレッドを、各テーブル パーティションではなくスレッドの 3 つの各シーク値 (A=13、A=17、および A=25) に割り当てます。これらの値を含むパーティションでクエリを実行すれば十分です。これらのシーク述語がすべて同じテーブル パーティションに含まれる場合は、すべてのスレッドが同じテーブル パーティションに割り当てられます。

別の例を挙げると、テーブルの列 A に境界点 (10、20、30) が設定された 4 つのパーティション、列 B のインデックス、クエリに述語句 WHERE B IN (50, 100, 150) が含まれるとします。テーブル パーティションは A の値に基づいているため、B の値はどのテーブル パーティションにも存在する可能性があります。したがって、クエリ プロセッサは、4 つの各テーブル パーティションで、B の 3 つの値 (50、100、150) を検索します。クエリ プロセッサは、これらの 12 個の各クエリ スキャンを並列実行できるように、スレッドを均等に割り当てます。

列 A に基づいたテーブル パーティション

各テーブル パーティションにおける列 B の検索

テーブル パーティション 1: A < 10

B=50、B=100、B=150

テーブル パーティション 2: A >= 10 AND A < 20

B=50、B=100、B=150

テーブル パーティション 3: A >= 20 AND A < 30

B=50、B=100、B=150

テーブル パーティション 4: A >= 30

B=50、B=100、B=150

ベスト プラクティス

大きなパーティション テーブルおよびパーティション インデックスの大量のデータにアクセスするクエリのパフォーマンスを向上するために推奨するベスト プラクティスを次に示します。

  • 各パーティションを多くのディスクにわたってストライピングします。

  • 可能な場合は、アクセス頻度の高いパーティションまたはすべてのパーティションを保持できる十分なメイン メモリがあるサーバーを使用して、I/O コストを軽減します。

  • クエリ対象のデータがメモリ内に収まらない場合は、テーブルおよびインデックスを圧縮します。これにより、I/O コストを軽減します。

  • 高速なプロセッサおよびできるだけ多くのプロセッサ コアを搭載したサーバーを使用して、並列クエリ処理機能を活用します。

  • サーバーに十分な I/O コントローラーの帯域幅があることを確認します。

  • すべての大きなパーティション テーブルにクラスター化インデックスを作成して、B ツリーのスキャンの最適化を活用します。

  • パーティション テーブルへのデータの一括読み込みを行う場合は、ホワイト ペーパー「パーティション テーブルに一括データを読み込む」に示す推奨事項に従います。

例

次の例では、7 つのパーティションを持つ 1 つのテーブルを含むテスト データベースを作成します。この例でクエリを実行し、コンパイル時と実行時の両方のプランのパーティション分割情報を表示する場合は、前に説明したツールを使用します。

注意注

この例では、100 万以上の行をテーブルに挿入します。この例を実行すると、ハードウェアによっては数分かかる場合があります。この例を実行する前に、1.5 GB を超えるディスク領域が確保されていることを確認してください。

 
USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO